Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 68B2563450D for ; Thu, 5 Mar 2009 14:17:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 12162-02 for ; Thu, 5 Mar 2009 14:16:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.27]) by mail.postgresql.org (Postfix) with ESMTP id 81EA2636059 for ; Thu, 5 Mar 2009 13:00:38 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 5so15419qwd.61 for ; Thu, 05 Mar 2009 09:00:37 -0800 (PST) MIME-Version: 1.0 Received: by 10.229.79.8 with SMTP id n8mr839861qck.37.1236268608892; Thu, 05 Mar 2009 07:56:48 -0800 (PST) Date: Thu, 5 Mar 2009 09:56:48 -0600 Message-ID: <78a8986f0903050756i7b1f6c27teb4c67d4578f11ae@mail.gmail.com> Subject: Index scan plan estimates way off. From: Jonathan Hseu To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00163646d7d649aa620464613737 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/51 X-Sequence-Number: 32973 --00163646d7d649aa620464613737 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 --00163646d7d649aa620464613737 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I have a relatively simple query with a single index on (contract_id, time)= :

vjtrade=3D> EXPLAIN SELECT * FROM ticks WHERE contract_id=3D1 O= RDER BY time;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
---------------------------------------------------------------------------= --------------------------
=A0Sort=A0 (cost=3D11684028.44..11761274.94 r= ows=3D30898601 width=3D40)
=A0=A0 Sort Key: "time"
=A0=A0 -= >=A0 Bitmap Heap Scan on ticks=A0 (cost=3D715657.57..6995196.08 rows=3D3= 0898601 width=3D40)
=A0=A0=A0=A0=A0=A0=A0=A0 Recheck Cond: (contract_id =3D 1)
=A0=A0=A0=A0= =A0=A0=A0=A0 ->=A0 Bitmap Index Scan on contract_id_time_idx=A0 (cost=3D= 0.00..707932.92 rows=3D30898601 width=3D0)
=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 Index Cond: (contract_id =3D 1)
(6 rows)

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

If I do a:
SET enable_sort=3DFALSE;
SET enable_bitmapscan=3DFALSE= ;

Then it gives me this plan:

Index Scan using contract_id_time_idx on ticks=A0 (cost=3D0.00..117276552.51 rows=3D30897044 width=3D40) (actual time=3D34.025..738583.609 rows=3D278581= 74 loops=3D1)
=A0 Index Cond: (contract_id =3D 1)
Total runtime: 742323.102 ms

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

This is on postgresql 8.3= .5 with:
shared_buffers =3D 512MB
temp_buffers =3D 256MB
work_mem =3D 256MBmax_fsm_pages =3D 153600
effective_cache_size =3D 1500MB

Is ther= e any way to give postgresql a better estimate of the index scan time?=A0 I= tried setting random_page_cost=3D1, but it still gave me the bitmap plan.<= br>
Thanks,
Jonathan Hseu
--00163646d7d649aa620464613737--