Re: Why does not subquery pruning conditions inherit to parent query?

On Thu, 6 Jun 2019 at 19:47, Kato, Sho <kato-sho(at)jp(dot)fujitsu(dot)com> wrote:
>
> On Friday, May 31, 2019 9:33 PM, David Rowley wrote:
> > Sure, it's possible, but it would require writing code. When it can and
> > cannot/should not be done would need to be determined.
>
> Is there any harmful effect by pulling up a subquery quals into outer query?

There are certainly cases where it can't be done, for example, if the
subquery is LEFT or FULL joined to. There's probably no shortage of
other cases too. Someone will need to do the analysis into cases where
it can and can't be done. That's likely more work than writing code to
make it work.

> Even if this feature is not be needed, it will be a problem if user execute this query to a table partitioned into a lot.
> So, I think it would be better to put together a query that partition pruning does not work on the wiki.
> Thoughts?

It's not really a restriction of partition pruning. Pruning done
during query planning can only use the base quals of the partitioned
relation. Run-time pruning goes only a little further and expands
that to allow parameters from other relations to be used too. The good
thing is that you can easily determine what those quals are by looking
at EXPLAIN. They're the ones that make it down to the scan level.
There's also a series of restrictions on top of that too, which are
not very well documented outside of the code.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services