From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Russell Smith <mr-russ@pws.com.au>
Cc: Guido Neitzer <lists@event-s.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Seqscan/Indexscan still a known issue?
Date: Sat, 27 Jan 2007 05:33:28 -0600
Message-ID: <1169897608.11009.2.camel@localhost.localdomain> (raw)
In-Reply-To: <45BB2CFA.50609@pws.com.au>
References: <45BAB682.6060709@mochima.com>
<45BB00BB.4070009@pws.com.au>
<58F562A7-2925-4E37-BEA7-3D26748D408A@event-s.net>
<45BB2CFA.50609@pws.com.au>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, mr-russ@pws.com.au, lists@event-s.net
Subject: Re: Seqscan/Indexscan still a known issue?
In-Reply-To: <1169897608.11009.2.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox