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
- List partitioning by expression
PARTITION BY LIST (col1 % 15),... FOR VALUES IN (0), selected withWHERE col1 % 15 = 0 - 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).