Re: slow pgsql tables - need to vacuum?

On Thu, Apr 3, 2008 at 2:34 PM, Dan99 <power919(at)gmail(dot)com> wrote:
> Hi,
>
> I am having some troubles with a select group of tables in a database
> which are acting unacceptably slow. For example a table with
> approximately < 10,000 rows took about 3,500ms to extract a single row
> using the following select statement:
>
> SELECT * FROM table WHERE column = 'value'
>
> I have preformed this same test on a number of different tables, only
> a few of which have this same problem. The only common thing that I
> can see between these affected tables is the fact that they are dumped
> and re-populated every day from an outside source.

You need to ANALYZE the tables after you load them, and make sure you
have indexes on the column you're querying (which it sounds like you
do, but they're not being used because the statistics for the table
are inaccurate). There may also be a lot of dead tuples which will
further slow down a sequential scan.

Do read up on VACUUM and MVCC in the docs--it's a very important
thing. You will suffer horribly unless you have a working periodic
VACUUM.

Also, are you using TRUNCATE TABLE to clear out before the reload, or
a mass DELETE? The latter will leave a lot of dead rows, bloating the
table and slowing down scans. TRUNCATE just deletes the table file
and recreates it empty.

-Doug