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 B6313D1E943 for ; Wed, 24 Mar 2004 16:44:46 +0000 (GMT) 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 99204-04 for ; Wed, 24 Mar 2004 12:44:44 -0400 (AST) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id F3C83D1E29F for ; Wed, 24 Mar 2004 12:44:43 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1B6BUg-000AZ5-0W; Wed, 24 Mar 2004 16:44:43 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id DDE0F16A71; Wed, 24 Mar 2004 16:44:41 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id D8D0E162C6; Wed, 24 Mar 2004 16:44:39 +0000 (GMT) From: Richard Huxton To: Woody Woodring , pgsql-performance@postgresql.org Subject: Re: Help with query plan inconsistencies Date: Wed, 24 Mar 2004 16:44:39 +0000 User-Agent: KMail/1.5 References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200403241644.39363.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/353 X-Sequence-Number: 6209 On Tuesday 23 March 2004 18:49, Woody Woodring wrote: > Hello, > > I am using postgres 7.4.2 as a backend for geocode data for a mapping > application. My question is why can't I get a consistent use of my indexes > during a query, I tend to get a lot of seq scan results. I'm not sure it wants to be using the indexes all of the time. > Nested Loop Left Join (cost=0.00..23433.18 rows=1871 width=34) (actual > time=0.555..5095.434 rows=3224 loops=1) > Total runtime: 5100.028 ms > Nested Loop Left Join (cost=0.00..76468.90 rows=9223 width=34) (actual > time=0.559..17387.427 rows=19997 loops=1) > Total runtime: 17416.501 ms > Nested Loop Left Join (cost=0.00..29160.02 rows=2327 width=34) (actual > time=0.279..510.773 rows=5935 loops=1) > Total runtime: 516.782 ms #1 = 630 rows/sec (with index on cable_billing) #2 = 1,148 rows/sec (without index) #3 = 11,501 rows/sec (with index) The third case is so much faster, I suspect the data wasn't cached at the beginning of this run. In any case #2 is faster than #1. If the planner is getting things wrong, you're not showing it here. -- Richard Huxton Archonet Ltd