Re: trouble with (lack of) indexing

On Thu, 9 May 2002, Søren Boll Overgaard wrote:

> Hello
>
> I am currently involved in a rather large project relying heavily on the use of
> postgresql[1], but we have run into a rather annoying snag.
> We currently have two databases set up. One for testing, and one for production.
> Both run on FreeBSD, and perform very well since the last upgrade.
> However, here is the problem. When executing a certain select statement (shown
> below) on the production database, we get a sequential table scan (of a rather
> large table), which causes the machine on which it runs to max out all possible
> disk I/O. However, when the excact same query is executed on the test
> database, we get an index scan instead of a sequential one. Obviously,
> something differes between the two databases, but we simply cannot track down
> what it is. I would greatly appreciate any input you might be able to offer.
> Here are the queries and their accompanying query plans:

Couple of quick questions...

Have you run vacuum analyze on both databases?
What are your settings in the postgresql.conf for cpu_tuple_cost and such?
You can check them with 'show all' from psql in 7.2.x and later.