Re: need explanation about an explain plan

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