Re: PG8.2.1 choosing slow seqscan over idx scan

On Tue, 2007-01-16 at 21:58 -0500, Jeremy Haile wrote:
> Hey Chad,
>
> The table is heavily inserted and deleted from. Recently I had done a
> very large delete.

I still keep wondering if this table is bloated with dead tuples. Even
if you vacuum often if there's a connection with an idle transaction,
the tuples can't be reclaimed and the table would continue to grow.

Anyway, what does

vacuum analyze tablename

say (where tablename is, of course, the name of the table we're looking
at)? Pay particular attention to DETAIL statements.

Assuming the table's NOT bloated, you may do well to increase the
effective_cache_size, which doesn't allocate anything, but just tells
the query planner about how big your operating systems file cache is as
regards postgresql. It's a bit of a course setting, i.e. you can make
rather large changes to it without blowing things up. If you've got a
couple gigs on your machine, try setting it to something like 512MB or
so.

If your table is bloating, and you don't have idle transactions hanging
of the database, it could be that your fsm settings are too low.