[POC] Allow flattening of subquery with a link to upper query

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: [POC] Allow flattening of subquery with a link to upper query
Date: 2022-08-31 06:35:09
Message-ID: f26a8b9e-5b41-4013-9e95-7a056456e30c@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

One of the most annoying things in the planner for me is unnesting of
correlated queries [1]. A number papers on this subject were published
starting 1980s, but only trivial optimizations exists in the Core. It
means a lack of performance, especially when we use foreign tables in
subquery.
In the patch I'm trying to propose a sort of sketch of solution.

Before flattening procedure we just look through the quals of subquery,
pull to the upper level OpExpr's containing variables from the upper
relation and replace their positions in the quals with true expression.
Further, the flattening machinery works as usual.

This patch is dedicated to simplest variant of correlated queries -
without aggregate functions in the target list. It passes regression
tests and contains some additional tests to demonstrate achievements.

I'd like to get critics on the approach.

[1] Kim, Won. “On optimizing an SQL-like nested query.” ACM Trans.
Database Syst. 7 (1982): 443-469.

--
Regards
Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
0001-Transform-correlated-subquery-of-type-N-J-1-into-ord.patch text/x-patch 36.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2022-08-31 06:51:18 Re: SQL/JSON features for v15
Previous Message Jeff Davis 2022-08-31 06:28:46 Re: New strategies for freezing, advancing relfrozenxid early