Re: [ADMIN] Query is stuck - Mailing list pgsql-general

I am using 8.1, so waiting coln is not there in pg_stat_activity.
I frequently see these in the server logs:

 LOG:  autovacuum: processing database "controlsmartdb"

Though I can give you the result of vacuum run (but it is not helping):
controlsmartdb=# vacuum full verbose  analyze repcopy;
INFO:  vacuuming "public.repcopy"
INFO:  "repcopy": found 0 removable, 200000 nonremovable row versions in 4652 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 182 to 182 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 416144 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 6856 free bytes are potential move destinations.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO:  index "repcopy_pk" now contains 200000 row versions in 441 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.00u sec elapsed 0.06 sec.
INFO:  "repcopy": moved 0 row versions, truncated 4652 to 4652 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_18398"
INFO:  "pg_toast_18398": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_18398_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.repcopy"
INFO:  "repcopy": scanned 3000 of 4652 pages, containing 128964 live rows and 0 dead rows; 3000 rows in sample, 199980
estimatedtotal rows
 
VACUUM
controlsmartdb=# select distinct report_status from repcopy ;

There is no update happening to the table.

-------------------------

> 1. Check if the query waits on some lock: add the column `waiting` to the
> above query from pg_stat_activity.
> 2. Run vacuum analyze on the table repcopy

In addition to that, indexes on report_time, report_status, and dm_user
might help.

And your query is not "hung", it's just taking a LOOOOONG time.  Based
on the explain, it could take several hours to complete.  How many
rows are in repcopy?  What is your vacuum schedule?  Do a vacuum verbose,
if the number of dead rows is very high on that table, you may benefit
from doing a VACUUM FULL + REINDEX or CLUSTER on the table.