>You could likely get an idea of how the surplus filter is slowing down >execution if you widen the filter to ensure it includes all possible >"ladate" values, run the query, then run the query again without the >date range filter. I would guess it'll only save you a few percent, >but I'm open to being proven wrong.
Thanks for the explanation and https://dbfiddle.uk/e0kpJYdd simply proves that you are right. >The planner could likely work a bit harder to prove which filters are >not required for the partition, but it currently just simply does not >do that. If we could find a cheap enough way to remove those during >planning, then we probably should. Then, we have to wait for that feature. Thanks! On Mon, Jul 3, 2023 at 12:06 AM David Rowley <dgrowle...@gmail.com> wrote: > On Fri, 30 Jun 2023 at 00:42, Umut TEKİN <umuttec...@gmail.com> wrote: > > @Marc, I think there is no problem.Even though it says it is filtered by > ladate, it is not. Because of the partition. > > As you can see for each index scan it uses a different partition and > those partition boundaries are already specified logically. > > For example; "Parallel Index Scan using table1_p_201802_numfic_idx on > table1_p_201802 t_3". > > If the names correctly matches the partition concept, the partition > table1_p_201802 only contains values for between 2018.02.01 and 2018.03.01. > > So, even though there is a filter, there is not. Thus, filtering only > occurs for your numfic column. > > I just wanted to clear up any confusion here. The above simply is not > true. If you see the filter in EXPLAIN, then the executor *is* > applying that filter. > > The planner could likely work a bit harder to prove which filters are > not required for the partition, but it currently just simply does not > do that. If we could find a cheap enough way to remove those during > planning, then we probably should. > > You could likely get an idea of how the surplus filter is slowing down > execution if you widen the filter to ensure it includes all possible > "ladate" values, run the query, then run the query again without the > date range filter. I would guess it'll only save you a few percent, > but I'm open to being proven wrong. > > David >
