Exists, limit and alternate plans

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Exists, limit and alternate plans
Date: 2010-07-12 13:29:07
Message-ID: AANLkTikRAHA-kTh5q65zdSQvaiAPEewMj01yf16mNPIa@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello.

Today I've found out strange results for query below.
select version();
version

----------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.2 on amd64-portbld-freebsd8.0, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 64-bit

--Original query:
explain analyze select exists(select * from investor i where i.company_id =
this_.id) from COMPANY this_ order by this_.rank desc, this_.id asc limit
10;
Limit (cost=0.00..50.67 rows=10 width=16) (actual time=144.489..144.556
rows=10 loops=1)
-> Index Scan using comp_rank_id on company this_
(cost=0.00..34616009.08 rows=6831169 width=16) (actual
time=144.484..144.524 rows=10 loops=1)
SubPlan 1
-> Index Scan using company_invs on investor i (cost=0.00..9.52
rows=2 width=0) (never executed)
Index Cond: ((company_id)::bigint = $0)
SubPlan 2
-> Seq Scan on investor i (cost=0.00..1836.17 rows=41717
width=8) (actual time=0.006..72.364 rows=41722 loops=1)
Total runtime: 144.975 ms
(8 rows)

--set enable_seqscan=false;
explain analyze select exists(select * from investor i where i.company_id =
this_.id) from COMPANY this_ order by this_.rank desc, this_.id asc limit
10;
Limit (cost=0.00..50.67 rows=10 width=16) (actual time=0.045..0.177
rows=10 loops=1)
-> Index Scan using comp_rank_id on company this_
(cost=0.00..34616009.08 rows=6831169 width=16) (actual time=0.041..0.146
rows=10 loops=1)
SubPlan 1
-> Index Scan using company_invs on investor i (cost=0.00..9.52
rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=10)
Index Cond: ((company_id)::bigint = $0)
SubPlan 2
-> Seq Scan on investor i (cost=10000000000.00..10000001836.17
rows=41717 width=8) (never executed)
Total runtime: 0.253 ms
(8 rows)

--limit inside exists
explain analyze select exists(select * from investor i where i.company_id =
this_.id limit 1) from COMPANY this_ order by this_.rank desc, this_.id asc
limit 10;
Limit (cost=0.00..50.67 rows=10 width=16) (actual time=0.052..0.219
rows=10 loops=1)
-> Index Scan using comp_rank_id on company this_
(cost=0.00..34616009.08 rows=6831169 width=16) (actual time=0.049..0.189
rows=10 loops=1)
SubPlan 1
-> Limit (cost=0.00..4.76 rows=1 width=422) (actual
time=0.011..0.011 rows=1 loops=10)
-> Index Scan using company_invs on investor i
(cost=0.00..9.52 rows=2 width=422) (actual time=0.007..0.007 rows=1
loops=10)
Index Cond: ((company_id)::bigint = $0)
Total runtime: 0.291 ms
(7 rows)

So, my Qs:
1) Do we really have alternative plans for SubPlan that are selected at
runtime? Wow.
2) Why "Seq scan" plan is selected by default? Is it because of outer limit
not being applied when calculating costs for subplans at runtime?
3) Why does limit inside exists helps? Is it simply because new
"alternative" logic in not applied for "complex case"?

--
Best regards,
Vitalii Tymchyshyn

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-07-12 14:45:14 Re: Exists, limit and alternate plans
Previous Message Kenneth Marshall 2010-07-12 13:26:52 Re: now() gives same time within the session