X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 141D2D1D300 for ; Mon, 12 Apr 2004 14:39:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 98945-05 for ; Mon, 12 Apr 2004 14:39:52 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 6DFD2D1D282 for ; Mon, 12 Apr 2004 14:39:51 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3FCBA356E5; Mon, 12 Apr 2004 10:39:51 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3E2DA3569F; Mon, 12 Apr 2004 10:39:51 -0700 (PDT) Date: Mon, 12 Apr 2004 10:39:51 -0700 (PDT) From: Stephan Szabo To: Jeremy Dunn Cc: Postgresql Performance Subject: Re: index v. seqscan for certain values In-Reply-To: <000901c420a4$7b733650$4f01a8c0@jeremydunn> Message-ID: <20040412103711.G16827@megazone.bigpanda.com> References: <000901c420a4$7b733650$4f01a8c0@jeremydunn> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/135 X-Sequence-Number: 6435 On Mon, 12 Apr 2004, Jeremy Dunn wrote: > explain analyze select count(*) from xxx where cid=6223341; > Aggregate (cost=74384.19..74384.19 rows=1 width=0) (actual > time=11614.89..11614.89 rows=1 loops=1) > -> Index Scan using xxx_cid on emailrcpts (cost=0.00..74329.26 > rows=21974 width=0) (actual time=35.75..11582.10 rows=20114 loops=1) > Total runtime: 11615.05 msec > > However for the values that have > 20,000 rows, the plan changes to a > sequential scan, which is proportionately much slower. > > explain analyze select count(*) from xxx where cid=7191032; > Aggregate (cost=97357.61..97357.61 rows=1 width=0) (actual > time=46427.81..46427.82 rows=1 loops=1) > -> Seq Scan on xxx (cost=0.00..97230.62 rows=50792 width=0) > (actual time=9104.45..46370.27 rows=37765 loops=1) > Total runtime: 46428.00 msec > > The question: why does the planner consider a sequential scan to be > better for these top 10 values? In terms of elapsed time it is more > than twice as slow, proportionate to an index scan for the same number > of rows. One thing to do is to set enable_seqscan=off and run the above and compare the estimated and real costs. It may be possible to lower random_page_cost to a still reasonable number in order to move the point of the switchover to seqscan.