From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | "Chadwick, Russell" <Russell(dot)Chadwick(at)idc-mcs(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Excessive rows/tuples seriously degrading query |
Date: | 2003-12-16 21:24:45 |
Message-ID: | 1071609885.5397.8.camel@fuji.krosing.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2003-12-16 22:47:59 | Re: Optimizing FK & PK performance... |
Previous Message | Nick Fankhauser | 2003-12-16 17:11:59 | Nested loop performance |