One question about transformation ANY Sublinks into joins

From: Armor <yupengstone(at)qq(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: One question about transformation ANY Sublinks into joins
Date: 2016-07-17 09:33:58
Message-ID: tencent_2FA5504751C6890043B27897@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

------------------
Jerry Yu
https://github.com/scarbrofair

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-07-17 12:27:33 Re: sslmode=require fallback
Previous Message Craig Ringer 2016-07-17 07:28:26 Re: Version number for pg_control