Re: [External] postgresql11.1 - stabilize partition pruning at execution 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(dot)Abulafia(at)amdocs(dot)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 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