How to make runtime partition pruning work?
От | Markus Heiden |
---|---|
Тема | How to make runtime partition pruning work? |
Дата | |
Msg-id | 1a47e5be-7d21-e625-f8bc-7e9979f1b14b@markusheiden.de обсуждение исходный текст |
Ответы |
Re: How to make runtime partition pruning work?
|
Список | 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
В списке pgsql-general по дате отправления: