Re: [ SOLVED ] select count(*) very slow on an already - Mailing list pgsql-performance

I am running an update on the same table

update rfis set inquiry_status='APPROVED' where inquiry_status='a';

Its running for past 20 mins. and top output is below.
The PID which is executing the query above is 6712. Can anyone
tell me why it is in an uninterruptable sleep and does it relate
to the apparent poor performance? Is it problem with the disk
hardware. I know at nite this query will run reasonably fast.

I am running on a decent hardware .



Regds
mallah.



 1:41pm  up 348 days, 21:10,  1 user,  load average: 11.59, 13.69, 11.49
85 processes: 83 sleeping, 1 running, 0 zombie, 1 stopped
CPU0 states:  8.1% user,  2.3% system,  0.0% nice, 89.0% idle
CPU1 states:  3.3% user,  2.3% system,  0.0% nice, 93.2% idle
CPU2 states:  7.4% user,  1.4% system,  0.0% nice, 90.0% idle
CPU3 states:  9.3% user,  7.4% system,  0.0% nice, 82.2% idle
Mem:  2064796K av, 2053964K used,   10832K free,       0K shrd,   22288K buff
Swap: 2048244K av,   88660K used, 1959584K free                 1801532K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
 6712 postgres  16   0 86592  84M 83920 D    11.1  4.1   1:36 postmaster
13103 postgres  15   0 54584  53M 52556 S     3.5  2.6   0:01 postmaster
13034 root      16   0  1072 1072   848 R     2.1  0.0   0:02 top
13064 postgres  15   0 67256  65M 64516 D     2.1  3.2   0:01 postmaster
13088 postgres  16   0 43324  42M 40812 D     2.1  2.0   0:00 postmaster
13076 postgres  15   0 49016  47M 46628 S     1.9  2.3   0:00 postmaster
26931 postgres  15   0 84880  82M 83888 S     1.7  4.1   3:52 postmaster
13107 postgres  15   0 18400  17M 16488 S     1.5  0.8   0:00 postmaster
13068 postgres  15   0 44632  43M 42324 D     1.3  2.1   0:00 postmaster
13074 postgres  15   0 68852  67M 66508 D     1.3  3.3   0:00 postmaster
13108 postgres  15   0 11692  11M 10496 S     1.3  0.5   0:00 postmaster
13075 postgres  15   0 50860  49M 47680 S     1.1  2.4   0:04 postmaster
13066 postgres  15   0 56112  54M 53724 S     0.9  2.7   0:01 postmaster
13109 postgres  15   0 14528  14M 13272 S     0.9  0.7   0:00 postmaster
24454 postgres  15   0  2532 2380  1372 S     0.7  0.1  11:58 postmaster
   12 root      15   0     0    0     0 SW    0.5  0.0 816:30 bdflush
24455 postgres  15   0  1600 1476  1380 S     0.5  0.0   9:11 postmaster
12528 postgres  15   0 84676  82M 79920 S     0.3  4.0   0:02 postmaster
12575 postgres  15   0 76660  74M 75796 D     0.3  3.7   0:09 postmaster
13038 postgres  15   0 48952  47M 46436 D     0.3  2.3   0:00 postmaster
13069 postgres  15   0 57464  56M 54852 S     0.3  2.7   0:00 postmaster
13102 postgres  15   0 17864  17M 16504 D     0.3  0.8   0:00 postmaster



















Richard Huxton wrote:

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.