From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: Chad Wagner <chad.wagner@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
Date: Tue, 16 Jan 2007 23:46:07 -0600
Message-ID: <1169012767.6271.7.camel@localhost.localdomain> (raw)
In-Reply-To: <1169002739.19758.1169636285@webmail.messagingengine.com>
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
<26412.1168983547@sss.pgh.pa.us>
<1168986053.16393.1169598917@webmail.messagingengine.com>
<81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>
<1169002739.19758.1169636285@webmail.messagingengine.com>
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.
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, jhaile@fastmail.fm, chad.wagner@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: PG8.2.1 choosing slow seqscan over idx scan
In-Reply-To: <1169012767.6271.7.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