From: Richard Huxton <dev@archonet.com>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Bill Moran <wmoran@potentialtech.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: [ SOLVED ] select count(*) very slow on an already
Date: Thu, 15 Apr 2004 18:44:33 +0100
Message-ID: <200404151844.33672.dev@archonet.com> (raw)
In-Reply-To: <407EB605.70502@trade-india.com>
References: <407D7A89.60508@trade-india.com>
<407E8FF6.4050601@potentialtech.com>
<407EB605.70502@trade-india.com>
On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote:
> Bill Moran wrote:
> > Rajesh Kumar Mallah wrote:
> >> Hi,
> >>
> >> The problem was solved by reloading the Table.
> >> the query now takes only 3 seconds. But that is
> >> not a solution.
> >
> > If dropping/recreating the table improves things, then we can reasonably
> > assume that the table is pretty active with updates/inserts. Correct?
>
> Yes the table results from an import process and under goes lots
> of inserts and updates , but thats before the vacuum full operation.
> the table is not accessed during vacuum. What i want to know is
> is there any wat to automate the dumping and reload of a table
> individually. will the below be safe and effective:
Shouldn't be necessary assuming you vacuum (not full) regularly. However,
looking back at your original posting, the vacuum output doesn't seem to show
any rows that need removing.
# VACUUM full verbose eyp_rfi;
INFO: vacuuming "public.eyp_rfi"
INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in
71987 pages
DETAIL: 0 dead row versions cannot be removed yet.
Since your select count(*) showed 505960 rows, I can't see how
dropping/replacing could make a difference on a sequential scan. Since we're
not using any indexes I don't see how it could be related to that.
> begin work;
> create table new_tab AS select * from tab;
> truncate table tab;
> insert into tab select * from new_tab;
> drop table new_tab;
> commit;
> analyze tab;
>
> i havenot tried it but plan to do so.
> but i feel insert would take ages to update
> the indexes if any.
It will have to update them, which will take time.
> BTW
>
> is there any way to disable checks and triggers on
> a table temporarily while loading data (is updating
> reltriggers in pg_class safe?)
You can take a look at pg_restore and copy how it does it.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, mallah@trade-india.com, wmoran@potentialtech.com
Subject: Re: [ SOLVED ] select count(*) very slow on an already
In-Reply-To: <200404151844.33672.dev@archonet.com>
* 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