Skip site navigation (1) Skip section navigation (2)

Re: Seqscan/Indexscan still a known issue?

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
Cc: Guido Neitzer <lists(at)event-s(dot)net>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Seqscan/Indexscan still a known issue?
Date: 2007-01-27 11:33:28
Message-ID: 1169897608.11009.2.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: 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.

In response to

pgsql-performance by date

Next:From: Carlos MorenoDate: 2007-01-27 17:09:16
Subject: Re: Seqscan/Indexscan still a known issue?
Previous:From: Russell SmithDate: 2007-01-27 10:44:10
Subject: Re: Seqscan/Indexscan still a known issue?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group