pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (12+ messages)  latest in thread

Message-ID: <200404151844.33672.dev@archonet.com>
Permalink:  ../200404151844.33672.dev@archonet.com/
Also on:    postgresql.org/message-id/200404151844.33672.dev@archonet.com

 · 

reply

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