Обсуждение: postgresql11.1 - stabilize partition pruning at execution time
Subject: postgresql11.1 - stabilize partition pruning at execution time
Hi,
we are evaluating postgresql11.1 for our productions, trying to use partitions to ease vacuum work,
(we have a system with 4251 updates per second, ~1000 delete per second and ~3221 inserts per second and 1billion transaction per day). we face a problem, that partition pruning is not working steadily with updates although we have: 1) part_key=value at our “where” clause 2) enable_partition_pruning = 'on'. we understood that there is a new patch at 11, that is supposed to support ( Faster Partition Pruning + Partition Pruning at Execution Time)
how can we stable partition pruning?, how to identify the reason when it does not work? what parameters affect it? appreciate if someone has experience with this.
thanks in advance, Aliza.
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service
Re: [External] postgresql11.1 - stabilize partition pruning atexecution time
do you have a reproducible scenario where this is not working? i am myself evaluating partitioning, but i refer to test cases when i have doubt :) https://github.com/postgres/postgres/blob/master/src/test/regress/sql/partition_prune.sql https://paquier.xyz/postgresql-2/postgres-11-partition-pruning/ also not sure but have a look at https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which child table the function's value might fall into at run time. As always, if i am diverting, i hope i'll be corrected by the experts here :) Regards, Vijay On Mon, Mar 4, 2019 at 9:32 PM Aliza Abulafia <Aliza.Abulafia@amdocs.com> wrote: > > Subject: postgresql11.1 - stabilize partition pruning at execution time > > > > Hi, > > > > we are evaluating postgresql11.1 for our productions, trying to use partitions to ease vacuum work, > (we have a system with 4251 updates per second, ~1000 delete per second and ~3221 inserts per second and 1billion transactionper day). we face a problem, that partition pruning is not working steadily with updates although we have: 1)part_key=value at our “where” clause 2) enable_partition_pruning = 'on'. we understood that there is a new patch at 11,that is supposed to support ( Faster Partition Pruning + Partition Pruning at Execution Time) > > how can we stable partition pruning?, how to identify the reason when it does not work? what parameters affect it? appreciateif someone has experience with this. > > > > thanks in advance, Aliza. > > > > This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms ofService, which you may review at https://www.amdocs.com/about/email-terms-of-service