Dynamically select HASH partition in postgresql 11

Viewed 1737

How to write a select query that selects from a parent (partitioned) table, partitioned using HASH partitioning (new in Postgresql 11), that translates to selecting all records from a single partition?

Example: I want this

CREATE TABLE parent_table (
  col1
  .....
) PARTITION BY HASH (col1);

CREATE TABLE child_table_0
PARTITION OF parent_table FOR VALUES WITH (MODULUS 15, REMAINDER 0);

[...]

CREATE TABLE child_table_14
PARTITION OF parent_table FOR VALUES WITH (MODULUS 15, REMAINDER 14);

-- Query in question
EXPLAIN
SELECT
    *
FROM parent_table
WHERE some_undocumented_hash_expression(col1, 15) = C

To return this plan

Append  (cost=0.00..67.44 rows=X width=Y)
  ->  Seq Scan on child_table_C  (cost=0.00..67.38 rows=X width=Y)
        Filter: ([?????????]=C)

Alternatives that I know of

  1. List partitioning by expression PARTITION BY LIST (col1 % 15), ... FOR VALUES IN (0), selected with WHERE col1 % 15 = 0
  2. Using an auxiliary column to store (cache) the expression value

Is there a more direct way? I want to get away from PARTITION BY LIST when I'm actually partitioning by HASH, but to do this I need a way to select the proper partition without writing dynamic SQL or making it even more complicated than PARTITION BY LIST (hash_expression).

0 Answers
Related