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 >> <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
