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,1000000); insert into yy select (random()*1000000)::int from generate_series(1,100000); 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=1000000 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=1000000 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=100000 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