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

Re: PG8.2.1 choosing slow seqscan over idx scan

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Jeremy Haile <jhaile(at)fastmail(dot)fm>
Cc: Chad Wagner <chad(dot)wagner(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Date: 2007-01-17 05:46:07
Message-ID: 1169012767.6271.7.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Guillaume LelargeDate: 2007-01-17 06:37:25
Subject: Re: Table Size
Previous:From: Chad WagnerDate: 2007-01-17 04:52:39
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan

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