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