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