Re: How to make runtime partition pruning work?

On Fri, 11 Oct 2019 at 21:49, Markus Heiden <markus(at)markusheiden(dot)de> wrote:
>
> I partitioned a table "data_table" by the key "import_id" to reduce the
> number of partitions to be loaded in my queries.
> I used list partitions, each containing usually just one "import_id". I
> used a primary key (id, import_id)
> But PostgreSQL does not consider partition keys to avoid loading not
> needed partitions.
>
> My query:
> SELECT SUM(something) FROM data_table WHERE import_id IN (SELECT id FROM
> import_table WHERE ...)
> My problem:
> The query takes too long, because PostgreSQL uses a hash join over all
> partitions of "data_table" with the "import_table", instead of pruning
> the "data_table" partitions by the import_ids at runtime.
> Static pruning (when using ... IN (1, 2, 3, 4)) works fine though.
>
> What am I doing wrong that runtime partition pruning with PostgreSQL
> 11.5 does not work in my case?

The documentation for this reads:

"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. Partition pruning can be
performed here for parameter values which are known during the
initialization phase of execution. Partitions which are pruned during
this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE.
It is possible to determine the number of partitions which were
removed during this phase by observing the “Subplans Removed” property
in the EXPLAIN output.

During actual execution of the query plan. Partition pruning may also
be performed here to remove partitions using values which are only
known during actual query execution. This includes values from
subqueries and values from execution-time parameters such as those
from parameterized nested loop joins. Since the value of these
parameters may change many times during the execution of the query,
partition pruning is performed whenever one of the execution
parameters being used by partition pruning changes. Determining if
partitions were pruned during this phase requires careful inspection
of the loops property in the EXPLAIN ANALYZE output. Subplans
corresponding to different partitions may have different values for it
depending on how many times each of them was pruned during execution.
Some may be shown as (never executed) if they were pruned every time."

https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITION-PRUNING

Notice that only subqueries and parameterized nested loop joins are
mentioned. The above text does not really go into the detail of which
types of subqueries can be used, but I can confirm that they must be
subqueries that can only return a scalar value. e.g WHERE x = (SELECT
y FROM ...). The executor would raise an error if that subquery
returned more than 1 row. The IN() clause you have is not eligible.
This will be converted into a semi-join during planning, and even if
it wasn't, the executor wouldn't be raising an error if it returned
multiple rows.

Unfortunately, to do what you mention with a hash join, we'd need to
scan through the entire hash table and incrementally build the set of
partitions which could match each value in the table. I'm sure there
are times where that would be well worth the trouble, but I imagine in
the average case we might find that the cost of scanning the entire
table this way would be more than just executing the query without any
partition pruning. I don't see any good way to know in advance if it
would be worthwhile or not.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services