[HACKERS] Quals not pushed down into lateral - Mailing list pgsql-hackers

Hi,

During citus development we noticed that restrictions aren't pushed down
into lateral subqueries, even if they semantically could.  For example,
in this dumbed down example:

postgres[31776][1]=# CREATE TABLE t_2(id serial primary key);
postgres[31776][1]=# CREATE TABLE t_1(id serial primary key);

Comparing:

postgres[31776][1]=# EXPLAIN SELECT * FROM t_1 JOIN (SELECT * FROM t_2 GROUP BY id) s ON (t_1.id = s.id) WHERE t_1.id =
3;
┌─────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                      │
├─────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=0.31..16.37 rows=1 width=8)                                      │
│   ->  Index Only Scan using t_1_pkey on t_1  (cost=0.15..8.17 rows=1 width=4)       │
│         Index Cond: (id = 3)                                                        │
│   ->  Group  (cost=0.15..8.17 rows=1 width=4)                                       │
│         Group Key: t_2.id                                                           │
│         ->  Index Only Scan using t_2_pkey on t_2  (cost=0.15..8.17 rows=1 width=4) │
│               Index Cond: (id = 3)                                                  │
└─────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

with:

postgres[31776][1]=# EXPLAIN SELECT * FROM t_1, LATERAL (SELECT * FROM t_2 WHERE t_1.id = t_2.id GROUP BY id) s WHERE
t_1.id= 3;
 
┌─────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                      │
├─────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=0.31..16.37 rows=1 width=8)                                      │
│   ->  Index Only Scan using t_1_pkey on t_1  (cost=0.15..8.17 rows=1 width=4)       │
│         Index Cond: (id = 3)                                                        │
│   ->  Group  (cost=0.15..8.17 rows=1 width=4)                                       │
│         Group Key: t_2.id                                                           │
│         ->  Index Only Scan using t_2_pkey on t_2  (cost=0.15..8.17 rows=1 width=4) │
│               Index Cond: (id = t_1.id)                                             │
└─────────────────────────────────────────────────────────────────────────────────────┘

it's noticeable that the former has id = 3 pushed down into both
relations index scans, whereas the latter doesn't.


This seems like a worthwhile future optimization opportunity.


I've not looked into this in any detail, but the proximate source is
that set_subquery_pathlist() doesn't see any baserstrictinfos to push
down.  Which makes sense, because t_1.id = t_2.id isn't "visible" (in
the sense of deconstruct_jointree dealing with it) to the outside.

It seems possible to look into rel->lateral_vars, check whether that's
member of some equivclass, and then push the relevant equivalences down
(after taking care that the Var from the outside is known as a Param on
the inside).


I'm not planning to work on this anytime soon, but I thought it'd be
useful to have a searchable reference point about the topic.  If
somebody wants to work on it...

Greetings,

Andres Freund