From: | Doug Reynolds <mav(at)wastegate(dot)net> |
---|---|
To: | Teja Jakkidi <teja(dot)jakkidi05(at)gmail(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Performance issue after creating partitions |
Date: | 2022-08-25 00:52:53 |
Message-ID: | 01000182d27cd99e-5a38a3c8-7060-4969-a24b-cf879b9f5bf7-000000@email.amazonses.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
You need to change your query to not use the trunc. The TRUNC statement forces it to trunc every row in the table.
If you do order_date >= to_date(..) and order_date < to_date(..) + interval '1' day, it will use the partition range to narrow down to the correct partition first.
Sent from my iPhone
> On Aug 24, 2022, at 6:41 PM, Teja Jakkidi <teja(dot)jakkidi05(at)gmail(dot)com> wrote:
>
> Hello Admin team,
>
> We have a Postgres 13 server set up on Google Cloud. Recently we created partitions as below for the tables:
> Range on ‘order_date’ column
> List on ‘country_code’ column
>
> Quarterly partitions for range.
>
> Order date column is a time stamp column and when we are using this column as a filter in where condition : trunc(order_date) = to_date(’2022-04-01’,’YYYY-MM-DD’), the query scans all the partitions dated back from the year 2000. So, instead of going to the 2022 Q2 partition directly, the query is scanning all the partitions.
> But when we remove the trunc() function and just specify order_date =‘2022-04-01 07:02:30’, this works as expected. It goes to the correct partition directly and gets the data.
> Can Anyone help me on what’s happening in the first case and why all the partitions are being scanned?
> For the first case, when there is a non partition table, it is behaving way better.
>
> Thanks,
> J. Teja.
>
From | Date | Subject | |
---|---|---|---|
Next Message | rh | 2022-08-25 02:25:47 | patroni rhel7.x86_64 RPM package spec issue |
Previous Message | Teja Jakkidi | 2022-08-24 22:41:08 | Performance issue after creating partitions |