Обсуждение: Partitions and planner
This is an odd question. I hope there is a workaround or feature for this that can use.
We have a huge table close to a billion rows that we are testing a partition by by hash and sub partitions by date range so we can drop the old partitions.
The hash partition we want for performance but the dat range partition just for maintenance. Having said that. On our non partitioned table the execution time is fine and the planning time is down to 4ms. However, on the partitioned table the execution time is fast but the planning time jumps to 300ms. A change of just a few milliseconds is impactful. But, a change of a few hundred milliseconds is not acceptable.
Having said all this, is there a way to tell the planner not to plan using the sub partitions and only use the primary hash partition? This way we could eliminate the extra planning time used on the sub partitions since those sub partitions will ever only be used for pruning data.
--
Thanks,
JT
JT
----
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
On Mon, 2020-04-27 at 16:03 -0700, J T wrote: > This is an odd question. I hope there is a workaround or feature for this that can use. > > We have a huge table close to a billion rows that we are testing a partition by by hash and sub partitions > by date range so we can drop the old partitions. > > The hash partition we want for performance but the dat range partition just for maintenance. Having said that. > On our non partitioned table the execution time is fine and the planning time is down to 4ms. > However, on the partitioned table the execution time is fast but the planning time jumps to 300ms. > A change of just a few milliseconds is impactful. But, a change of a few hundred milliseconds is not acceptable. > > Having said all this, is there a way to tell the planner not to plan using the sub partitions and only > use the primary hash partition? This way we could eliminate the extra planning time used on the sub partitions > since those sub partitions will ever only be used for pruning data. The planner *has* to consider the subpartitions, because that's where the actual data is. It seems like you have too many partitions for the PostgreSQL version you are using. Consider upgrading to v12, and consider doing away with hash partitioning. Do you have any evidence that it improves performance in your case? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com