On Sun, Jul 17, 2016 at 5:33 AM, Armor <yupengst...@qq.com> wrote: > Hi > I run a simple SQL with latest PG： > postgres=# explain select * from t1 where id1 in (select id2 from t2 where > c1=c2); > QUERY PLAN > ------------------------------------------------------------ > Seq Scan on t1 (cost=0.00..43291.83 rows=1130 width=8) > Filter: (SubPlan 1) > SubPlan 1 > -> Seq Scan on t2 (cost=0.00..38.25 rows=11 width=4) > Filter: (t1.c1 = c2) > (5 rows) > > and the table schema are as following: > > postgres=# \d t1 > Table "public.t1" > Column | Type | Modifiers > --------+---------+----------- > id1 | integer | > c1 | integer | > > postgres=# \d t2 > Table "public.t2" > Column | Type | Modifiers > --------+---------+----------- > id2 | integer | > c2 | integer | > > I find PG decide not to pull up this sublink because the whereClauses > in this sublink refer to the Vars of parent query, for detail please check > the function named convert_ANY_sublink_to_join in > src/backend/optimizer/plan/subselect.c. > However, for such simple sublink which has no agg, no window function, > no limit, may be we can carefully pull up the predicates in whereCluase > which refers to the Vars of parent query, then pull up this sublink and > produce a query plan as following: > > postgres=# explain select * from t1 where id1 in (select id2 from t2 where > c1=c2); > QUERY PLAN > ------------------------------------------------------------------------ > Hash Join (cost=49.55..99.23 rows=565 width=8) > Hash Cond: ((t1.id1 = t2.id2) AND (t1.c1 = t2.c2)) > -> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8) > -> Hash (cost=46.16..46.16 rows=226 width=8) > -> HashAggregate (cost=43.90..46.16 rows=226 width=8) > Group Key: t2.id2, t2.c2 > -> Seq Scan on t2 (cost=0.00..32.60 rows=2260 width=8)
It would need to be a Hash Semi Join rather than a Hash Join, wouldn't it? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
