Hello
I try to look on one slow query with correlated subquery:
create table xx(a int primary key);
create table yy(a int);
insert into xx select generate_series(1,100);
insert into yy select (random()*100)::int from generate_series(1,10);
create index on yy(a);
Query A
select a, (select true from yy where xx.a = yy.a limit 1) from xx
limit 10 offset 0;
postgres= explain select a, (select true from yy where xx.a = yy.a
limit 1) from xx;
QUERY PLAN
--
Seq Scan on xx (cost=0.00..4392325.00 rows=100 width=4)
SubPlan 1
- Limit (cost=0.00..4.38 rows=1 width=0)
- Index Only Scan using yy_a_idx on yy (cost=0.00..4.38
rows=1 width=0)
Index Cond: (a = xx.a)
(5 rows)
plan for this query is expected
But when I rewrote query I got strange plan (query B):
postgres= explain select a, exists(select 1 from yy where xx.a =
yy.a) from xx limit 10 offset 0;
QUERY PLAN
--
Limit (cost=0.00..43.92 rows=10 width=4)
- Seq Scan on xx (cost=0.00..4392325.00 rows=100 width=4)
SubPlan 1
- Index Only Scan using yy_a_idx on yy (cost=0.00..4.38
rows=1 width=0)
Index Cond: (a = xx.a)
SubPlan 2
- Seq Scan on yy (cost=0.00..1443.00 rows=10 width=4)
(7 rows)
Why there are a SubPlan 2?
But query B is two time faster than query A
public | xx| table | pavel| 35 MB |
public | yy| table | pavel| 3576 kB|
regards
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
