pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Marc Millas <marc.millas@mokadb.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: need explanation about an explain plan
Date: Wed, 28 Jun 2023 18:48:20 +0200
Message-ID: <02665fc681ba77695ecf44bf1156e22ac09ae91e.camel@cybertec.at> (raw)
In-Reply-To: <CADX_1aYVmbrbJi+62b06XWuLzn6CJs6P0K=wEDinJjcGvkuC+g@mail.gmail.com>
References: <CADX_1aZHUsDwXAutJ5Eh2d9dNnTjRoWV4f=tS0yBhQhLg5t+Hg@mail.gmail.com>
	<CADX_1aYVmbrbJi+62b06XWuLzn6CJs6P0K=wEDinJjcGvkuC+g@mail.gmail.com>

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





view thread (9+ messages)  latest in thread

Message-ID: <02665fc681ba77695ecf44bf1156e22ac09ae91e.camel@cybertec.at>
Permalink:  ../02665fc681ba77695ecf44bf1156e22ac09ae91e.camel@cybertec.at/
Also on:    postgresql.org/message-id/02665fc681ba77695ecf44bf1156e22ac09ae91e.camel@cybertec.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: laurenz.albe@cybertec.at, marc.millas@mokadb.com
  Subject: Re: need explanation about an explain plan
  In-Reply-To: <02665fc681ba77695ecf44bf1156e22ac09ae91e.camel@cybertec.at>
  References: <CADX_1aZHUsDwXAutJ5Eh2d9dNnTjRoWV4f=tS0yBhQhLg5t+Hg@mail.gmail.com> <CADX_1aYVmbrbJi+62b06XWuLzn6CJs6P0K=wEDinJjcGvkuC+g@mail.gmail.com> <02665fc681ba77695ecf44bf1156e22ac09ae91e.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox