Search Postgresql Archives

Re: Question on overall design

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

 





On Tue, Dec 12, 2023 at 2:11 AM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:
On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne <ddevienne@xxxxxxxxx> wrote:
On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:
* We departitioned because SELECT statements were slow.  All partitions were scanned, even when the partition key was specified in the WHERE clause.

Surely that's no the case on newer PostgreSQL, is it? Otherwise what's the point of partitioning?
Also, I remember reading something about recent improvements with a large number of partitions, no?

As someone who's interested on partitioning, I'd appreciate details. Thanks, --DD

This was on 12.5.  v13 was just released, and we weren't confident about running a mission-critical system on a .1 version.

Something's wrong if all partitions are scanned even when the partition clause is explicit in the where clause.

There are however some things which can cause problems here, such as type casts of the partition key, or when the partition key is being brought in from a join. 

All "transaction" tables were partitioned by month on partion_date, while the PK was table_name_id, partition_date.

Queries were _slow_, even when the application knew the partion_date range (since queries might span months).  PG just wouldn't prune. 

Was there a datatype issue here?  Like having a partition key of type timestamp, but the query casting from date? 

When I departitioned the tables, performance became acceptable.




--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.
http://www.efficito.com/learn_more

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux