X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B8DB95369A for ; Wed, 20 Apr 2005 11:22:34 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91656-10 for ; Wed, 20 Apr 2005 14:22:26 +0000 (GMT) Received: from localhost.net (cust.89.121.adsl.cistron.nl [195.64.89.121]) by svr1.postgresql.org (Postfix) with ESMTP id 8E29953688 for ; Wed, 20 Apr 2005 11:22:25 -0300 (ADT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by localhost.net (Postfix) with ESMTP id 872EC83D5E for ; Wed, 20 Apr 2005 16:22:27 +0200 (CEST) Message-ID: <426665A0.1080102@trust-factory.com> Date: Wed, 20 Apr 2005 16:22:24 +0200 From: Richard van den Berg Organization: Trust Factory User-Agent: Debian Thunderbird 1.0.2 (X11/20050331) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-perform Subject: When are index scans used over seq scans? X-Enigmail-Version: 0.90.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/496 X-Sequence-Number: 11893 We have a table with 1M rows that contain sessions with a start and finish timestamps. When joining this table with a 10k table with rounded timestamps, explain shows me sequential scans are used, and the join takes about 6 hours (2s per seq scan on session table * 10000): Nested Loop (cost=252.80..233025873.16 rows=1035480320 width=97) Join Filter: (("outer".starttime <= "inner".ts) AND ("outer".finishtime >= "inner".ts)) -> Seq Scan on sessions us (cost=0.00..42548.36 rows=924536 width=105) -> Materialize (cost=252.80..353.60 rows=10080 width=8) -> Seq Scan on duration du (cost=0.00..252.80 rows=10080 width=8) However, during the initial loading of the data (we first load into text tables, then convert to tables using timestamps etc, then run this query) the same query took only 12 minutes. While debugging, I increased cpu_tuple_cost to 0.1 (from 0.01). Now the explain shows an index scan, and the run time comes down to 11 minutes: Nested Loop (cost=0.00..667700310.42 rows=1035480320 width=97) -> Seq Scan on sessions us (cost=0.00..125756.60 rows=924536 width=105) -> Index Scan using ix_du_ts on duration du (cost=0.00..604.46 rows=1120 width=8) Index Cond: (("outer".starttime <= du.ts) AND ("outer".finishtime >= du.ts)) I am glad that I found a way to force the use of the index, but still can't explain why in the initial run the planner made the right choice, but now I need to give it a hand. Could this have to do with the statistics of the tables? I make very sure (during the initial load and while testing) that I vacuum analyze all tables after I fill them. I'm runing postgres 7.4.7. Any help is appreciated. -- Richard van den Berg, CISSP ------------------------------------------- Trust Factory B.V. | www.dna-portal.net Bazarstraat 44a | www.trust-factory.com 2518AK The Hague | Phone: +31 70 3620684 The Netherlands | Fax : +31 70 3603009 -------------------------------------------