Horton

Optimization issue of branching UNION ALL

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

View thread View on postgr.es
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-12-21T04:14:02Z
Lists: pgsql-hackers

Attachments

Hi,

Client report on a corner case have shown up possible minor 
non-optimality in procedure of transformation of simple UNION ALL 
statement tree.
Complaint is about auto-generated query with 1E4 simple union all's (see 
t.sh to generate a demo script). The reason: in REL_11_STABLE it is 
planned and executed in a second, but REL_12_STABLE and beyond makes 
matters worse: planning of such a query needs tons of gigabytes of RAM.

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.
2. Iterative passes along the append_rel_list for replacing vars in the 
translated_vars field. I can't grasp real necessity of passing all the 
append_rel_list during flattening of an union all leaf subquery. No one 
can reference this leaf, isn't it?

In attachment you can see some sketch that reduces a number of planner 
cycles/copyings.

-- 
Regards
Andrey Lepikhov
Postgres Professional

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.