Discussion about this post

User's avatar
justin's avatar

I missing the point of this. why test query plan timing on partitioned table including all the partitions.?

The core idea of partitioning is to separate data into logical blocks to speed up query times. Not including the partitioning key in the WHERE clause guarantees slow planning time and slow queries as partition pruning is not being used.

There is another gotcha with queries that include all the partition segments the lock manager is going to puke on high transaction table. As all the related objects have a lock entry, its very easy to have 10,000 entries in lock manager from just a hand full of sessions touching the partitioned table. Its right around 10,000 entries in the lock manager for

a table performance falls of cliff. If the lock manager gets to around 200,000 entries all sessions for all queries grands to a halt.

Seen this many times when partitioning is deployed badly. For this to happen must have a high TPC load. not easy to duplicate in test environments.

I would like to understand the opening comment "people complaining about planning time are slow". Why are the plans' slow and for what kind of queries. If they involve partitioning betting its do to bad partitioning scheme

Expand full comment
Pgm's avatar

Hi, what I don't get is why postgres insists on using a parameter outside of the query: plan_cache_mode. It would be much more user friendly if we could use a word inside the query like 'select with-custom-plan * from'. Postgres already has such features, e.g. the 'materialized' keyword. I am no advocate for oracle style hints, but either postgres finds a way to solve the problem without outside parameters or there should be syntax to put inside the query to solve it manually.

Expand full comment
4 more comments...

No posts