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
The type of queries and data structure matters a great deal in understanding how to address the problem with the tools available
The entire point of partitioning data is to group data logically in the most common access pattern, to reduce all the query processing overhead. Why else do it.
The examples and arguments provided are counter to that design pattern. So its going to break very quickly.
I don't understand what is being articulated here, its counter to the design intent of partitioning
I have deployed partitioning over Multi Billion row tables that are several TB in size . And for many different databases. I am very aware of the pluses and minuses of partitioning and the affects on the planner.
My rule is if partitioning scheme can not cover 80% of queries the table is involved in Don't use partitioning or keep the number of partitions to a very small number. If the bulk of the queries don't fit the partition scheme the only problem being addressed is table maintenance.
It would be nice to read about your experience in detail: as a DBMS developer, I base my code solutions solely on personal experience and reports I have read before. This is quite a limited source.
However, I live in a situation where clients often don't intend to change their minds, keep sophisticated legacy applications as is, and use partitioning to employ parallel append, move 'cold' data to a foreign server, and so on. I'm not sure it would be a good business to impose on people how they should organise their data ;).
At the same time, they usually use micropartitioning (100-1000 partitions), which causes long planning times.
> I would like to understand the opening comment ...
It is not about a partitioning schema or pruning at all. It is about the number of variants that the planner should check to find the optimal plan. Of course, if the query pattern is designed in a way that the DBMS can prune most of the partitions during the planning phase, it is an ideal case, and in such cases, people should definitely prefer custom plans and a simple protocol. But we have a slightly different case ...
Imagine that each partition has three indexes. That means, during optimisation, the planner should probe each index scan for each partition. Plus PartitionWise JOIN, partial aggregate, parameterised NestLoop ... Sometimes planning complexity grows faster than N^2 (N - number of partitions), and that is the reason for slow planning time.
This is quite a complicated issue; I'm not sure the community will be able to resolve all the obstacles soon. So, a generic plan is an option to ease the problem.
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.
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
You did not answer the question.
The type of queries and data structure matters a great deal in understanding how to address the problem with the tools available
The entire point of partitioning data is to group data logically in the most common access pattern, to reduce all the query processing overhead. Why else do it.
The examples and arguments provided are counter to that design pattern. So its going to break very quickly.
I don't understand what is being articulated here, its counter to the design intent of partitioning
I have deployed partitioning over Multi Billion row tables that are several TB in size . And for many different databases. I am very aware of the pluses and minuses of partitioning and the affects on the planner.
My rule is if partitioning scheme can not cover 80% of queries the table is involved in Don't use partitioning or keep the number of partitions to a very small number. If the bulk of the queries don't fit the partition scheme the only problem being addressed is table maintenance.
It would be nice to read about your experience in detail: as a DBMS developer, I base my code solutions solely on personal experience and reports I have read before. This is quite a limited source.
However, I live in a situation where clients often don't intend to change their minds, keep sophisticated legacy applications as is, and use partitioning to employ parallel append, move 'cold' data to a foreign server, and so on. I'm not sure it would be a good business to impose on people how they should organise their data ;).
At the same time, they usually use micropartitioning (100-1000 partitions), which causes long planning times.
> I would like to understand the opening comment ...
It is not about a partitioning schema or pruning at all. It is about the number of variants that the planner should check to find the optimal plan. Of course, if the query pattern is designed in a way that the DBMS can prune most of the partitions during the planning phase, it is an ideal case, and in such cases, people should definitely prefer custom plans and a simple protocol. But we have a slightly different case ...
Imagine that each partition has three indexes. That means, during optimisation, the planner should probe each index scan for each partition. Plus PartitionWise JOIN, partial aggregate, parameterised NestLoop ... Sometimes planning complexity grows faster than N^2 (N - number of partitions), and that is the reason for slow planning time.
This is quite a complicated issue; I'm not sure the community will be able to resolve all the obstacles soon. So, a generic plan is an option to ease the problem.
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.
I think you may find some reasons in the following discussions:
https://www.postgresql.org/message-id/flat/4E9AFBF9.80708%40wulczer.org
https://www.postgresql.org/message-id/3e36717b-689b-473c-a40f-bb35dd56ea5b%40www.fastmail.com?utm_source=chatgpt.com
In short, there are some issues immediately raise that may cause code complexity and additional overhead.