Обсуждение: need explanation about an explain plan
Hi,
Postgres 14.2 (for one more month)
The explain plan and request is here:
The big table 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 a
Sorry, gmail sent uncompleted message
Hi,
Postgres 14.2 (for one more month)
The explain plan and request is here:
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.
Thanks,
Marc MILLAS
On Wed, Jun 28, 2023 at 5:22 PM Marc Millas <marc.millas@mokadb.com> wrote:
Hi,Postgres 14.2 (for one more month)The explain plan and request is here:The big table 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 a
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
On Wed, Jun 28, 2023 at 6:48 PM Laurenz Albe <laurenz.albe@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
On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote: > Hi Laurenz, as said, in each partition there is only one value for ladate. The planner doesn't seem to take that into account. Yours, Laurenz Albe
Le mer. 28 juin 2023 à 22:46, Laurenz Albe <laurenz.albe@cybertec.at> a écrit :
On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote:
> Hi Laurenz, as said, in each partition there is only one value for ladate.
The planner doesn't seem to take that into account.
Indeed. I did check values in pg_statistic. And rerun analyze. No change...
Yours,
Laurenz Albe
Hi,
@Marc, I think there is no problem.Even though it says it is filtered by ladate, it is not. Because of the partition.
As you can see for each index scan it uses a different partition and those partition boundaries are already specified logically.
For example; "Parallel Index Scan using table1_p_201802_numfic_idx on table1_p_201802 t_3".
If the names correctly matches the partition concept, the partition table1_p_201802 only contains values for between 2018.02.01 and 2018.03.01.
So, even though there is a filter, there is not. Thus, filtering only occurs for your numfic column.
The following link might help to understand how it does reading and skipping. In your case, it does not do any harm, but maybe it would be better change how it looks on the execution plan to prevent confusion.
Thanks!
On Thu, Jun 29, 2023 at 12:08 PM Marc Millas <marc.millas@mokadb.com> wrote:
Le mer. 28 juin 2023 à 22:46, Laurenz Albe <laurenz.albe@cybertec.at> a écrit :On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote:
> Hi Laurenz, as said, in each partition there is only one value for ladate.
The planner doesn't seem to take that into account.Indeed. I did check values in pg_statistic. And rerun analyze. No change...
Yours,
Laurenz Albe
On Fri, 30 Jun 2023 at 00:42, Umut TEKİN <umuttechin@gmail.com> wrote: > @Marc, I think there is no problem.Even though it says it is filtered by ladate, it is not. Because of the partition. > As you can see for each index scan it uses a different partition and those partition boundaries are already specified logically. > For example; "Parallel Index Scan using table1_p_201802_numfic_idx on table1_p_201802 t_3". > If the names correctly matches the partition concept, the partition table1_p_201802 only contains values for between 2018.02.01and 2018.03.01. > So, even though there is a filter, there is not. Thus, filtering only occurs for your numfic column. I just wanted to clear up any confusion here. The above simply is not true. If you see the filter in EXPLAIN, then the executor *is* applying that filter. The planner could likely work a bit harder to prove which filters are not required for the partition, but it currently just simply does not do that. If we could find a cheap enough way to remove those during planning, then we probably should. You could likely get an idea of how the surplus filter is slowing down execution if you widen the filter to ensure it includes all possible "ladate" values, run the query, then run the query again without the date range filter. I would guess it'll only save you a few percent, but I'm open to being proven wrong. David
>You could likely get an idea of how the surplus filter is slowing down
>execution if you widen the filter to ensure it includes all possible
>"ladate" values, run the query, then run the query again without the
>date range filter. I would guess it'll only save you a few percent,
>but I'm open to being proven wrong.
>execution if you widen the filter to ensure it includes all possible
>"ladate" values, run the query, then run the query again without the
>date range filter. I would guess it'll only save you a few percent,
>but I'm open to being proven wrong.
Thanks for the explanation and https://dbfiddle.uk/e0kpJYdd simply proves that you are right.
>The planner could likely work a bit harder to prove which filters are
>not required for the partition, but it currently just simply does not
>do that. If we could find a cheap enough way to remove those during
>planning, then we probably should.
>not required for the partition, but it currently just simply does not
>do that. If we could find a cheap enough way to remove those during
>planning, then we probably should.
Then, we have to wait for that feature.
Thanks!
On Mon, Jul 3, 2023 at 12:06 AM David Rowley <dgrowleyml@gmail.com> wrote:
On Fri, 30 Jun 2023 at 00:42, Umut TEKİN <umuttechin@gmail.com> wrote:
> @Marc, I think there is no problem.Even though it says it is filtered by ladate, it is not. Because of the partition.
> As you can see for each index scan it uses a different partition and those partition boundaries are already specified logically.
> For example; "Parallel Index Scan using table1_p_201802_numfic_idx on table1_p_201802 t_3".
> If the names correctly matches the partition concept, the partition table1_p_201802 only contains values for between 2018.02.01 and 2018.03.01.
> So, even though there is a filter, there is not. Thus, filtering only occurs for your numfic column.
I just wanted to clear up any confusion here. The above simply is not
true. If you see the filter in EXPLAIN, then the executor *is*
applying that filter.
The planner could likely work a bit harder to prove which filters are
not required for the partition, but it currently just simply does not
do that. If we could find a cheap enough way to remove those during
planning, then we probably should.
You could likely get an idea of how the surplus filter is slowing down
execution if you widen the filter to ensure it includes all possible
"ladate" values, run the query, then run the query again without the
date range filter. I would guess it'll only save you a few percent,
but I'm open to being proven wrong.
David