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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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