Re: Sequential scan on FK join

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
>> <martin@portant.com> 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