Re: Pulling up direct-correlated ANY_SUBLINK - Mailing list pgsql-hackers

From Antonin Houska
Subject Re: Pulling up direct-correlated ANY_SUBLINK
Date
Msg-id 60794.1568104308@antos
Whole thread Raw
In response to Pulling up direct-correlated ANY_SUBLINK  (Richard Guo <riguo@pivotal.io>)
Responses Re: Pulling up direct-correlated ANY_SUBLINK
List pgsql-hackers
Richard Guo <riguo@pivotal.io> wrote:

> Can we try to pull up direct-correlated ANY SubLink with the help of
> LATERAL?

> By this way, we can convert the query:
>
> select * from a where a.i = ANY(select i from b where a.j > b.j);
>
> To:
>
> select * from a SEMI JOIN lateral(select * from b where a.j > b.j)
> sub on a.i = sub.i;
>

I tried this a few years ago. This is where the problems started:

https://www.postgresql.org/message-id/1386716782.5203.YahooMailNeo%40web162905.mail.bf1.yahoo.com

I'm not sure I remember enough, but the problem has something to do with one
possible strategy to plan SEMI JOIN: unique-ify the inner path and then
perform plain INNER JOIN instead.

I think the problemm was that the WHERE clause of the subquery didn't
participate in the SEMI JOIN evaluation and was used as filter instead. Thus
the clause's Vars were not used in unique keys of the inner path and so the
SEMI JOIN didn't work well.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com



pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: WIP: BRIN multi-range indexes
Next
From: Kyotaro Horiguchi
Date:
Subject: Re: Remove page-read callback from XLogReaderState.