Runtime partition pruning

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Runtime partition pruning

Radu Radutiu
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):

select * from test  where id between>0 and  and id<1000 and client_id=? ;

partition pruning works fine.

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
Reply | Threaded
Open this post in threaded view
|

Re: Runtime partition pruning

Michael Lewis
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=?; 
Reply | Threaded
Open this post in threaded view
|

Re: Runtime partition pruning

Radu Radutiu
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 <[hidden email]> 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=?;