Re: Performance issue after creating partitions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.jakkidi05@xxxxxxxxx> 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.
> 







[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux