Re: [ SOLVED ] select count(*) very slow on an already

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.
>
>
>