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 5A0E853551 for ; Wed, 20 Apr 2005 11:39:20 -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 41368-09 for ; Wed, 20 Apr 2005 14:39:17 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 896A453514 for ; Wed, 20 Apr 2005 11:39:17 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3KEdLoN026711; Wed, 20 Apr 2005 10:39:21 -0400 (EDT) To: Richard van den Berg Cc: pgsql-perform Subject: Re: When are index scans used over seq scans? In-reply-to: <426665A0.1080102@trust-factory.com> References: <426665A0.1080102@trust-factory.com> Comments: In-reply-to Richard van den Berg message dated "Wed, 20 Apr 2005 16:22:24 +0200" Date: Wed, 20 Apr 2005 10:39:21 -0400 Message-ID: <26710.1114007961@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/498 X-Sequence-Number: 11895 Richard van den Berg writes: > 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) The explain shows no such thing. What is the *actual* runtime of each plan per EXPLAIN ANALYZE, please? (In general, any time you are complaining about planner misbehavior, it is utterly pointless to give only planner estimates and not reality. By definition, you don't think the estimates are right.) regards, tom lane