Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group