Re: why partition pruning doesn't work?

2018-06-05 17:07 GMT+02:00 David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>:

> On 5 June 2018 at 22:31, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
> wrote:
> > Maybe, David (added to cc) has something to say about all this,
> especially
> > whether he considers this a feature and not a bug fix.
>
> Thanks, Amit. I had missed this thread.
>
> Yeah. I admit if I'd thought about this case when I wrote the code,
> then I'd have made any non-volatile Expr work, but I didn't :-(
>
> It was pointed out to be a few months ago in a comment in [1]. I
> initially thought that this was v12 material, but it seems there are a
> few people here that are pretty unhappy about it.
>
> I was going to describe what such a patch should look like here, but
> that seemed like about as much work as writing it, so:
>
> Please see the attached patch. I've only just finished with it and
> it's not fully done yet as there's still an XXX comment where I've not
> quite thought about Exprs with Vars from higher levels. These might
> always be converted to Params, so the code might be okay as is, but
> I've not checked this yet, hence the comment remains.
>
> I'm slightly leaning towards this being included in v11. Without this
> people are forced into hacks like WHERE partkey = (SELECT
> stablefunc()); to get pruning working at all. If that SQL remains
> after this patch then pruning can only take place during actual
> execution. With the attached patch the pruning can take place during
> the initialization of the executor, which in cases with many
> partitions can be significantly faster, providing actual execution is
> short. I'd rather people didn't get into bad habits like that if we
> can avoid it.
>

This is really great

Regards

Pavel

> [1] https://blog.2ndquadrant.com/partition-elimination-postgresql-11/
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>