Re: Seqscan/Indexscan still a known issue? - Mailing list pgsql-performance

From Scott Marlowe
Subject Re: Seqscan/Indexscan still a known issue?
Date
Msg-id 1169897608.11009.2.camel@localhost.localdomain
Whole thread Raw
In response to Re: Seqscan/Indexscan still a known issue?  (Russell Smith <mr-russ@pws.com.au>)
List pgsql-performance
On Sat, 2007-01-27 at 21:44 +1100, Russell Smith wrote:
> Guido Neitzer wrote:
> > On 27.01.2007, at 00:35, Russell Smith wrote:
> >
> >> Guess 1 would be that your primary key is int8, but can't be certain
> >> that is what's causing the problem.
> >
> > Why could that be a problem?
> Before 8.0, the planner would not choose an index scan if the types were
> different int8_col = const, int8_col = 4.
> 4 in this example is cast to int4.  int8 != int4.  So the planner will
> not choose an index scan.

But, in 7.4 setting enable_seqscan off would not make it use that index.
For the OP, the problem is likely either that the stats for the column
are off, effective_cache_size is set too low, and / or random_page_cost
is too high.  there are other possibilities as well.

FYI, I upgraded the server we use at work to scan a statistical db of
our production performance, and the queries we run there, which take
anywhere from a few seconds to 20-30 minutes, run much faster.  About an
hour after the upgrade I had a user ask what I'd done to the db to make
it so much faster.  The upgrade was 7.4 to 8.1  btw...  still testing
8.2, and it looks very good.

pgsql-performance by date:

Previous
From: Russell Smith
Date:
Subject: Re: Seqscan/Indexscan still a known issue?
Next
From: Carlos Moreno
Date:
Subject: Re: Seqscan/Indexscan still a known issue?