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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to