Partition Pruning - cheeyoung/sqlplus-public GitHub Wiki

MySQL PG Oracle


24.4 Partition Pruning (MySQL 8.0 Reference Manual)

The optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases:

  • partition_column = constant
  • partition_column IN (constant1, constant2, ..., constantN)

5.11.4. Partition Pruning (PostgreSQL 15)

Partition pruning can be performed not only during the planning of a given query, but also during its execution. This is useful as it can allow more partitions to be pruned when clauses contain expressions whose values are not known at query planning time, for example, parameters defined in a PREPARE statement, using a value obtained from a subquery, or using a parameterized value on the inner side of a nested loop join. Partition pruning during execution can be performed at any of the following times:

  • During initialization of the query plan. ...
  • During actual execution of the query plan. ...

Partition pruning can be disabled using the enable_partition_pruning setting.

3.1.4 Static Partition Pruning (VLDB and Partitioning Guide, 21c)

Oracle determines when to use static pruning primarily based on static predicates.

For many cases, Oracle determines the partitions to be accessed at compile time. Static partition pruning occurs if you use static predicates, except for the following cases: Dynamic Pruning with Bind Variables