Thread: partition pruning
When I use intervals in my query e.g col1 between current_timestamp - interval '10 days' and current_timestamp...the optimizer checks ALL partitions whereas if I use col1 between 2 hardcoded dates..only the applicable partitions are scanned.
On Mon, Mar 1, 2010 at 2:29 PM, Anj Adu <fotographs@gmail.com> wrote: > When I use intervals in my query e.g col1 between current_timestamp - > interval '10 days' and current_timestamp...the optimizer checks ALL > partitions whereas if I use col1 between 2 hardcoded dates..only > the applicable partitions are scanned. Yep. This is one example of a more general principle: constant-folding happens before planning, but anything more complex has to wait until execution time. So the plan can't take into account the value of current_timestamp in forming the plan. Unfortunately I don't think there's really any easy way around this: you have to do select current_timestamp, current_timestamp - interval '10 days' first and then build & execute a new query. ...Robert
On Thu, 2010-03-04 at 17:40 -0500, Robert Haas wrote: > On Mon, Mar 1, 2010 at 2:29 PM, Anj Adu <fotographs@gmail.com> wrote: > > When I use intervals in my query e.g col1 between current_timestamp - > > interval '10 days' and current_timestamp...the optimizer checks ALL > > partitions whereas if I use col1 between 2 hardcoded dates..only > > the applicable partitions are scanned. > > Yep. This is one example of a more general principle: > constant-folding happens before planning, but anything more complex > has to wait until execution time. So the plan can't take into account > the value of current_timestamp in forming the plan. It could, but it doesn't yet. Partition removal can take place in the executor and this is currently targeted for 9.1. -- Simon Riggs www.2ndQuadrant.com