From: | Markus Heiden <markus(at)markusheiden(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | How to make runtime partition pruning work? |
Date: | 2019-10-11 08:49:09 |
Message-ID: | 1a47e5be-7d21-e625-f8bc-7e9979f1b14b@markusheiden.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Thanks,
Markus
From | Date | Subject | |
---|---|---|---|
Next Message | Ajay Pratap | 2019-10-11 10:43:53 | Pgbackrest backup is too slow |
Previous Message | La Cancellera Yoann | 2019-10-11 08:38:58 | Issues with PAM : log that it failed, whether it actually failed or not |