Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 70A9C9FB314 for ; Tue, 16 Jan 2007 17:23:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 77790-02 for ; Tue, 16 Jan 2007 17:23:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from out4.smtp.messagingengine.com (out4.smtp.messagingengine.com [66.111.4.28]) by postgresql.org (Postfix) with ESMTP id 3015A9FB319 for ; Tue, 16 Jan 2007 17:23:02 -0400 (AST) Received: from out1.internal (unknown [10.202.2.149]) by out1.messagingengine.com (Postfix) with ESMTP id 3D74C7DEFB for ; Tue, 16 Jan 2007 16:23:01 -0500 (EST) Received: from web2.messagingengine.com ([10.202.2.211]) by out1.internal (MEProxy); Tue, 16 Jan 2007 16:23:01 -0500 Received: by web2.messagingengine.com (Postfix, from userid 99) id 117CAD407; Tue, 16 Jan 2007 16:23:01 -0500 (EST) Message-Id: <1168982580.8275.1169588211@webmail.messagingengine.com> X-Sasl-Enc: JA9g5Y0SUCKr1uz/X1p9oVm8swwP/mlMIbkVdM67eFB0 1168982580 From: "Jeremy Haile" To: pgsql-performance@postgresql.org Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="ISO-8859-1" MIME-Version: 1.0 X-Mailer: MessagingEngine.com Webmail Interface Subject: PG8.2.1 choosing slow seqscan over idx scan Date: Tue, 16 Jan 2007 16:23:00 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/244 X-Sequence-Number: 22845 Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq scan over index scan even though index scan is faster (as shown by disabling seqscan). Table is recently analyzed and row count estimates seem to be in the ballpark. Another tidbit - I haven't done a "vacuum full" ever, although I "vacuum analyze" regularly (and autovacuum). I recently noticed that the PG data drive is 40% fragmented (NTFS). Could that be making the seqscan slower than it should be? Regardless of the fragmentations affect on performance, is the query planner making a good decision here? SOME CONFIGURATION PARAMS effective_cache_size=1000MB random_page_cost=3 default_statistics_target=50 shared_buffers=400MB temp_buffers=10MB work_mem=10MB checkpoint_segments=12 QUERY select merchant_dim_id, dcms_dim_id, sum(success) as num_success, sum(failed) as num_failed, count(*) as total_transactions, (sum(success) * 1.0 / count(*)) as success_rate from transaction_facts where transaction_date >= '2007-1-16' and transaction_date < '2007-1-16 15:20' group by merchant_dim_id, dcms_dim_id; EXPLAIN ANALYZE (enable_seqscan=true) HashAggregate (cost=339573.01..340089.89 rows=15904 width=16) (actual time=140606.593..140650.573 rows=10549 loops=1) -> Seq Scan on transaction_facts (cost=0.00..333928.25 rows=322558 width=16) (actual time=19917.957..140036.910 rows=347434 loops=1) Filter: ((transaction_date >= '2007-01-16 00:00:00'::timestamp without time zone) AND (transaction_date < '2007-01-16 15:20:00'::timestamp without time zone)) Total runtime: 140654.813 ms EXPLAIN ANALYZE (enable_seqscan=false) HashAggregate (cost=379141.53..379658.41 rows=15904 width=16) (actual time=3720.838..3803.748 rows=10549 loops=1) -> Bitmap Heap Scan on transaction_facts (cost=84481.80..373496.76 rows=322558 width=16) (actual time=244.568..3133.741 rows=347434 loops=1) Recheck Cond: ((transaction_date >= '2007-01-16 00:00:00'::timestamp without time zone) AND (transaction_date < '2007-01-16 15:20:00'::timestamp without time zone)) -> Bitmap Index Scan on transaction_facts_transaction_date_idx (cost=0.00..84401.16 rows=322558 width=0) (actual time=241.994..241.994 rows=347434 loops=1) Index Cond: ((transaction_date >= '2007-01-16 00:00:00'::timestamp without time zone) AND (transaction_date < '2007-01-16 15:20:00'::timestamp without time zone)) Total runtime: 3810.795 ms