Re: need explanation about an explain plan

On Fri, 30 Jun 2023 at 00:42, Umut TEKİN <umuttechin(at)gmail(dot)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