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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
