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?  (David Rowley <david.rowley@2ndquadrant.com>)
Список 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 по дате отправления:

Предыдущее
От: La Cancellera Yoann
Дата:
Сообщение: Issues with PAM : log that it failed, whether it actually failed or not
Следующее
От: Ajay Pratap
Дата:
Сообщение: Pgbackrest backup is too slow