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 estimated total 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
