need explanation about an explain plan

Sorry, gmail sent uncompleted message

Hi,

Postgres 14.2 (for one more month)
The explain plan and request is here:
https://explain.depesz.com/s/Opk0

The big table (10 billions raws) is split in around 130 partitions, one by
month.
the ladate column is the partition key, and it does have 1 value for each
partition.
there is an index on the numfic column. the distribution of values for that
column may differ before and after 2019 january.

The request is executed in a loop for all ten years of data, year 1,2,3,
then year2,3,4 etc
that request is also executed for columns other than cod, one column at a
time (there are 107 columns...)

I dont understand why the planner use a filter on the date, as its already
within the partition structure.

Thanks,

Marc MILLAS

On Wed, Jun 28, 2023 at 5:22 PM Marc Millas <marc.millas@mokadb.com> wrote:

On Wed, 2023-06-28 at 17:29 +0200, Marc Millas wrote:

https://explain.depesz.com/s/Opk0

The big table (10 billions raws) is split in around 130 partitions, one by month.
the ladate column is the partition key, and it does have 1 value for each partition.
there is an index on the numfic column. the distribution of values for that column may differ before and after 2019 january.

The request is executed in a loop for all ten years of data, year 1,2,3, then year2,3,4 etc
that request is also executed for columns other than cod, one column at a time (there are 107 columns...)

I dont understand why the planner use a filter on the date, as its already within the partition structure.

If the grouping column is the partitioning key, try to set enable_partitionwise_join = on.

Otherwise, your best bet is to create an index that covers both WHERE conditions,
or a covering index, which will get you the best result:

CREATE INDEX ON table1 (numfic, ladate) INCLUDE (cod, nb_obs);

Yours,
Laurenz Albe

On Wed, Jun 28, 2023 at 6:48 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Wed, 2023-06-28 at 17:29 +0200, Marc Millas wrote:

https://explain.depesz.com/s/Opk0

The big table (10 billions raws) is split in around 130 partitions, one

by month.

the ladate column is the partition key, and it does have 1 value for

each partition.

there is an index on the numfic column. the distribution of values for

that column may differ before and after 2019 january.

The request is executed in a loop for all ten years of data, year 1,2,3,

then year2,3,4 etc

that request is also executed for columns other than cod, one column at

a time (there are 107 columns...)

I dont understand why the planner use a filter on the date, as its

already within the partition structure.

If the grouping column is the partitioning key, try to set
enable_partitionwise_join = on.

Otherwise, your best bet is to create an index that covers both WHERE
conditions,
or a covering index, which will get you the best result:

CREATE INDEX ON table1 (numfic, ladate) INCLUDE (cod, nb_obs);

Hi Laurenz, as said, in each partition there is only one value for ladate.
I don't understand the point of creating an index for the tens of millions
rows of each partition, index wich will contain the very same unique value
within all of the index.
I did set enable_partitionwise_join = 'on' and retry,
but it doesn't seem to change the plan.

On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote:

Hi Laurenz, as said, in each partition there is only one value for ladate.

The planner doesn't seem to take that into account.

Yours,
Laurenz Albe

Le mer. 28 juin 2023 à 22:46, Laurenz Albe <laurenz.albe@cybertec.at> a
écrit :

On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote:

Hi Laurenz, as said, in each partition there is only one value for

ladate.

The planner doesn't seem to take that into account.

Indeed. I did check values in pg_statistic. And rerun analyze. No change...

Hi,

@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.

The following link might help to understand how it does reading and
skipping. In your case, it does not do any harm, but maybe it would be
better change how it looks on the execution plan to prevent confusion.

https://www.postgresql.org/docs/current/indexes-multicolumn.html#:~:text=For%20example%2C%20given,be%20scanned%20through
.

Thanks!

On Thu, Jun 29, 2023 at 12:08 PM Marc Millas <marc.millas@mokadb.com> wrote:

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

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 <dgrowleyml@gmail.com> wrote: