Обсуждение: Runtime partition pruning
Hello list,
Is runtime partition pruning available in PostgreSQL 12?
I have a table partitioned by range on column id (primary key).
For the query (client_id is passed as a parameter from the application):
I have a table partitioned by range on column id (primary key).
For the query (client_id is passed as a parameter from the application):
select * from test where id between>0 and and id<1000 and client_id=? ;
partition pruning works fine.
However
However
select * from test where id between client_id-10 and client_id+10 and client_id=?;
does not (it scans all partitions in parallel) .
Is it expected?
Regards,
Radu
select * from test where id between client_id-10 and client_id+10 and client_id=?;does not (it scans all partitions in parallel) .Is it expected?
Yes. But the below would work fine I expect since the planner would know a constant range for id. I would be very surprised if the optimizer had some condition rewrite rules to handle just the scenario you show.
select * from test where id between ?-10 and ?+10 and client_id=?;
Thanks. Yes, the query with the same parameter seems to work as expected. It doesn't help us though as we are trying to transparently support partitioning using an ORM and we cannot change the parameters. Using the column name would have been much easier.
Regards,
Radu
On Mon, Mar 23, 2020 at 5:56 PM Michael Lewis <mlewis@entrata.com> wrote:
select * from test where id between client_id-10 and client_id+10 and client_id=?;does not (it scans all partitions in parallel) .Is it expected?Yes. But the below would work fine I expect since the planner would know a constant range for id. I would be very surprised if the optimizer had some condition rewrite rules to handle just the scenario you show.select * from test where id between ?-10 and ?+10 and client_id=?;