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

Query plan degradation 8.2 --> 8.3

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Query plan degradation 8.2 --> 8.3
Date: 2007-05-30 22:51:16
Message-ID: 200705301551.16686.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
All,

I now have a simple test case which shows significant performance 
degradation on 8.3devel for a specific query, apparenly due to an 
unnecessary call to Top-N sort.  I've tried to forward the test case to 
the lists but the package is 3.5m, so I'm putting it on pgFoundry instead:


If you run the example query (badsql.sql), you get the following for 8.2, 
which is a Good Plan:

 Limit  (cost=0.00..24.04 rows=20 width=64) (actual time=0.091..0.129 
rows=20 loops=1)
   ->  Index Scan using abc_idx_t_s_symb_dts on abc  (cost=0.00..908.55 
rows=756 width=64) (actual time=0.089..0.123 rows=20 loops=1)
         Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND (t_dts >= 
'2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts <= 
'2007-06-28 00:00:00'::timestamp without time zone))
         Filter: ((t_st_id = 'CMPT'::bpchar) AND (t_ca_id <= 
43000050000::bigint))
 Total runtime: 0.222 ms


However, running the same against 8.3-snapshot-5-28 gives you:

 Limit  (cost=631.85..631.90 rows=20 width=55) (actual time=2.325..2.336 
rows=20 loops=1)
   ->  Sort  (cost=631.85..633.61 rows=704 width=55) (actual 
time=2.323..2.326 rows=20 loops=1)
         Sort Key: t_dts
         Sort Method:  top-N heapsort  Memory: 27kB
         ->  Bitmap Heap Scan on abc  (cost=25.23..613.12 rows=704 
width=55) (actual time=0.537..1.477 rows=843 loops=1)
               Recheck Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND 
(t_dts >= '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts 
<= '2007-06-28 00:00:00'::timestamp without time zone))
               Filter: ((t_ca_id <= 43000050000::bigint) AND (t_st_id = 
'CMPT'::bpchar))
               ->  Bitmap Index Scan on abc_idx_t_s_symb_dts  
(cost=0.00..25.06 rows=704 width=0) (actual time=0.506..0.506 rows=843 
loops=1)
                     Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND 
(t_dts >= '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts 
<= '2007-06-28 00:00:00'::timestamp without time zone))
 Total runtime: 2.460 ms


.... and if you disable bitmap:

 Limit  (cost=812.35..812.40 rows=20 width=55) (actual time=2.363..2.372 
rows=20 loops=1)
   ->  Sort  (cost=812.35..814.11 rows=704 width=55) (actual 
time=2.360..2.363 rows=20 loops=1)
         Sort Key: t_dts
         Sort Method:  top-N heapsort  Memory: 27kB
         ->  Index Scan using abc_idx_t_s_symb_dts on abc  
(cost=0.00..793.62 rows=704 width=55) (actual time=0.080..1.567 rows=843 
loops=1)
               Index Cond: (((t_s_symb)::text = 'SYMPRA'::text) AND (t_dts 
>= '2006-02-15 00:00:00'::timestamp without time zone) AND (t_dts <= 
'2007-06-28 00:00:00'::timestamp without time zone))
               Filter: ((t_ca_id <= 43000050000::bigint) AND (t_st_id = 
'CMPT'::bpchar))
 Total runtime: 2.475 ms

The problem appears to be that top-N heapsort is being called even when 
it's not needed, such as immediately after an indexscan.  

Is my assessment correct?

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2007-05-30 22:55:00
Subject: Re: Query plan degradation 8.2 --> 8.3
Previous:From: Tom LaneDate: 2007-05-30 22:00:20
Subject: Re: New cast between inet/cidr and bytea

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