Index scan plan estimates way off. - Mailing list pgsql-performance

From Jonathan Hseu
Subject Index scan plan estimates way off.
Date
Msg-id 78a8986f0903050756i7b1f6c27teb4c67d4578f11ae@mail.gmail.com
Whole thread Raw
Responses Re: Index scan plan estimates way off.
List pgsql-performance
I have a relatively simple query with a single index on (contract_id, time):

vjtrade=> EXPLAIN SELECT * FROM ticks WHERE contract_id=1 ORDER BY time;
                              
               QUERY PLAN                                             
-----------------------------------------------------------------------------------------------------
 Sort  (cost=11684028.44..11761274.94 rows=30898601 width=40)
   Sort Key: "time"
   ->  Bitmap Heap Scan on ticks  (cost=715657.57..6995196.08 rows=30898601 width=40)
         Recheck Cond: (contract_id = 1)
         ->  Bitmap Index Scan on contract_id_time_idx  (cost=0.00..707932.92 rows=30898601 width=0)
               Index Cond: (contract_id = 1)
(6 rows)

This plan doesn't complete in a reasonable amount of time.  I end up having to kill the query after it's been running for over an hour.

If I do a:
SET enable_sort=FALSE;
SET enable_bitmapscan=FALSE;

Then it gives me this plan:

Index Scan using contract_id_time_idx on ticks  (cost=0.00..117276552.51 rows=30897044 width=40) (actual time=34.025..738583.609 rows=27858174 loops=1)
  Index Cond: (contract_id = 1)
Total runtime: 742323.102 ms

Notice how the estimated cost is so much different from the actual time.  The row estimate is pretty good, however.

This is on postgresql 8.3.5 with:
shared_buffers = 512MB
temp_buffers = 256MB
work_mem = 256MB
max_fsm_pages = 153600
effective_cache_size = 1500MB

Is there any way to give postgresql a better estimate of the index scan time?  I tried setting random_page_cost=1, but it still gave me the bitmap plan.

Thanks,
Jonathan Hseu

pgsql-performance by date:

Previous
From: Robert Haas
Date:
Subject: Re: Postgres 8.3, four times slower queries?
Next
From: Aaron Guyon
Date:
Subject: Re: Postgres 8.3, four times slower queries?