On Fri, 11 Oct 2019 at 21:49, Markus Heiden <markus@xxxxxxxxxxxxxxx> 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
