How to make runtime partition pruning work?

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

Responses

Browse pgsql-general by date

  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