Re: slow pgsql tables - need to vacuum?

On Apr 5, 6:36 pm, d(dot)(dot)(dot)(at)mcnaught(dot)org ("Douglas McNaught") wrote:
> On Thu, Apr 3, 2008 at 2:34 PM, Dan99 <power(dot)(dot)(dot)(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
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Unfortunately, I did not design this database (or the website for that
matter) and am only maintaining it. As a result of the inexperience
of the website designer, there are no indexes in any of the tables and
it would be rather difficult to put them in after the fact since this
is a live website. Does TRUNCATE TABLE keep all necessary table
information such as indexes, constraints, triggers, rules, and
privileges? Currently a mass DELETE is being used to remove the data.
Since VACUUM has never been done on the tables before, should a VACUUM
FULL be done first? If so, approximately how long does a VACUUM FULL
take on a database with 25 tables each having anywhere form 1,000 to
50,000 rows? The reason I ask is because this is a live website, and
any down time is very inconvenient. Also, would it be sufficient
(after the first VACUUM FULL) to simply use a VACUUM ANALYZE after the
tables are repopulated (ie. every night)?

Your help is much appreciated.
Daniel