On Wed, Jun 28, 2023 at 6:48 PM Laurenz Albe <laurenz.a...@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. > Yours, > Laurenz Albe >