Horton

Re: Optimization issue of branching UNION ALL

Richard Guo <guofenglinux@gmail.com>

View thread View on postgr.es
From: Richard Guo <guofenglinux@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrey Lepikhov <a.lepikhov@postgrespro.ru>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-12-22T02:37:35Z
Lists: pgsql-hackers 
On Thu, Dec 22, 2022 at 9:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Andrey Lepikhov <a.lepikhov@postgrespro.ru> writes:
> > Superficial study revealed possibly unnecessary operations that could be
> > avoided:
> > 1. Walking across a query by calling substitute_phv_relids() even if
> > lastPHId shows that no one phv is presented.
>
> Yeah, we could do that, and it'd help some.


I noticed we also check 'parse->hasSubLinks' when we fix PHVs and
AppendRelInfos in pull_up_simple_subquery.  I'm not sure why we have
this check.  It seems not necessary.

In remove_result_refs, I don't think we need to check 'lastPHId' again
before calling substitute_phv_relids, since it has been checked a few
lines earlier.

Thanks
Richard

Commits

  1. Avoid O(N^2) cost when pulling up lots of UNION ALL subqueries.

  2. Add some recursion and looping defenses in prepjointree.c.