Re: need explanation about an explain plan

On Wed, Jun 28, 2023 at 6:48 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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.

> Yours,
> Laurenz Albe
>