From: | Jonathan Hseu <vomjom(at)vomjom(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Index scan plan estimates way off. |
Date: | 2009-03-05 15:56:48 |
Message-ID: | 78a8986f0903050756i7b1f6c27teb4c67d4578f11ae@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Aaron Guyon | 2009-03-05 16:06:21 | Re: Postgres 8.3, four times slower queries? |
Previous Message | Robert Haas | 2009-03-05 13:21:56 | Re: Postgres 8.3, four times slower queries? |