X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 19B01D77A5 for ; Mon, 17 Oct 2005 16:46:59 -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 89631-07 for ; Mon, 17 Oct 2005 19:46:56 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9AF54D7357 for ; Mon, 17 Oct 2005 16:46:56 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 9E86631058; Mon, 17 Oct 2005 21:46:57 +0200 (MET DST) From: Martin Nickel X-Newsgroups: pgsql.performance Subject: Re: Sequential scan on FK join Date: Mon, 17 Oct 2005 14:56:43 -0500 Organization: Hub.Org Networking Services Lines: 34 Message-ID: References: <4353648C.8040507@archonet.com> <4353E342.2060708@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.) To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200510/257 X-Sequence-Number: 15025 When I turn of seqscan it does use the index - and it runs 20 to 30% longer. Based on that, the planner is correctly choosing a sequential scan - but that's just hard for me to comprehend. I'm joining on an int4 key, 2048 per index page - I guess that's a lot of reads - then the data -page reads. Still, the 8-minute query time seems excessive. On Mon, 17 Oct 2005 18:45:38 +0100, Richard Huxton wrote: > Martin Nickel wrote: >> Subject: Re: Sequential scan on FK join From: Martin Nickel >> Newsgroups: pgsql.performance >> Date: Wed, 12 Oct 2005 15:53:35 -0500 >> >> Richard, here's the EXPLAIN ANALYZE. I see your point re: the 2.7M >> expected vs the 2 actual, but I've run ANALYZE on the lead table and it >> hasn't changed the plan. Suggestions? >> >> Hash Join (cost=62.13..2001702.55 rows=2711552 width=20) (actual >> time=40.659..244709.315 rows=2 125270 loops=1) > ^^^ > Hmm - is that not just a formatting gap there? Is it not 2,125,270 rows > matching which would suggest PG is getting it more right than wrong. > > Try issuing "SET enable_seqscan=false" before running the explain analyse > - that will force the planner to use any indexes it can find and should > show us whether the index would help. -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org