pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hannu Krosing <hannu@tm.ee>
To: Chadwick, Russell <Russell.Chadwick@idc-mcs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Excessive rows/tuples seriously degrading query
Date: Tue, 16 Dec 2003 23:24:45 +0200
Message-ID: <1071609885.5397.8.camel@fuji.krosing.net> (raw)
In-Reply-To: <B37E267CB3D7C841A09F5078F117AFBC059409@q.idc-mcs.com>
References: <B37E267CB3D7C841A09F5078F117AFBC059409@q.idc-mcs.com>

Chadwick, Russell kirjutas L, 13.12.2003 kell 00:40:
>  
> Hello everyone.
> Can anyone explain why this table which has never had more than a
> couple rows in it shows > 500k in the query planner even after running
> vacuum full.  Its terribly slow to return 2 rows of data.  The 2 rows
> in it are being updated a lot but I couldn't find any explanation for
> this behavior. 

It can be that there is an idle transaction somewhere that has locked a
lot of rows (i.e. all your updates have been running inside the same
transaction for hour or days)

try: 
$ ps ax| grep post

on my linux box this gives

 1683 ?      S    0:00 /usr/bin/postmaster -p 5432
 1704 ?      S    0:00 postgres: stats buffer process
 1705 ?      S    0:00 postgres: stats collector process
 5520 ?      S    0:00 postgres: hu hannu [local] idle in transaction
 5524 pts/2  S    0:00 grep post

where backend 5520 seems to be the culprit.

>  Anything I could try besides droping db and recreating?  

make sure that no other backend is connected to db and do your 
> vacuum full; analyze;


or if there seems to be something unidentifieable making your table
unusable, then just recreate that table:

begin;
create table stock_log_positions_tmp 
    as select * from stock_log_positions;
drop table stock_log_positions;
alter table stock_log_positions_tmp
     rename to stock_log_positions;
-- if you have any constraints, indexes or foreign keys
-- then recreate them here as well
commit;

> Thanks - Russ
>  
---------------
hannu




view thread (3+ messages)  latest in thread

Message-ID: <1071609885.5397.8.camel@fuji.krosing.net>
Permalink:  ../1071609885.5397.8.camel@fuji.krosing.net/
Also on:    postgresql.org/message-id/1071609885.5397.8.camel@fuji.krosing.net

 · 

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: hannu@tm.ee, Russell.Chadwick@idc-mcs.com
  Subject: Re: Excessive rows/tuples seriously degrading query
  In-Reply-To: <1071609885.5397.8.camel@fuji.krosing.net>

* 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