Re: query is taking longer time after a while

Bill Moran wrote:
>
> My apologies, I should have asked for the output of VACUUM VERBOSE on
> the problem table in conjunction with these settings. (make sure you
> do VACUUM VERBOSE when the table is exhibiting the speed problem)
>

INFO: vacuuming "public.session_allocation_info"
INFO: scanned index "session_allocation_info_pkey" to remove 175234 row
versions
DETAIL: CPU 0.69s/1.47u sec elapsed 70.77 sec.
INFO: scanned index "session_allocation_info_request_time_idx" to remove
175234 row versions
DETAIL: CPU 0.22s/0.55u sec elapsed 11.64 sec.
INFO: scanned index "session_allocation_info_teardown_time_idx" to remove
175234 row versions
DETAIL: CPU 0.05s/0.49u sec elapsed 3.81 sec.
INFO: scanned index "session_allocation_info_initiator_idx" to remove
175234 row versions
DETAIL: CPU 0.37s/0.74u sec elapsed 14.48 sec.
INFO: scanned index "session_allocation_info_status_idx" to remove 175234
row versions
DETAIL: CPU 0.08s/0.51u sec elapsed 11.83 sec.
INFO: "session_allocation_info": removed 175234 row versions in 25596 pages
DETAIL: CPU 0.52s/0.53u sec elapsed 23.85 sec.
INFO: index "session_allocation_info_pkey" now contains 4662043 row
versions in 55781 pages
DETAIL: 65733 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "session_allocation_info_request_time_idx" now contains 4662043
row versions in 69879 pages
DETAIL: 65943 index row versions were removed.
17354 index pages have been deleted, 17074 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "session_allocation_info_teardown_time_idx" now contains
4662043 row versions in 21677 pages
DETAIL: 65943 index row versions were removed.
8793 index pages have been deleted, 8548 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "session_allocation_info_initiator_idx" now contains 4662043
row versions in 65869 pages
DETAIL: 175234 index row versions were removed.
16261 index pages have been deleted, 15624 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "session_allocation_info_status_idx" now contains 4662043 row
versions in 25463 pages
DETAIL: 175234 index row versions were removed.
9217 index pages have been deleted, 8583 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "session_allocation_info": found 38814 removable, 4662043
nonremovable row versions in 205325 pages
DETAIL: 1590 dead row versions cannot be removed yet.
There were 11978040 unused item pointers.
89189 pages contain useful free space.
0 pages are entirely empty.
CPU 3.27s/5.20u sec elapsed 237.90 sec.
INFO: vacuuming "pg_toast.pg_toast_17383"
INFO: index "pg_toast_17383_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_17383": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 238981.517 ms

--
View this message in context: http://www.nabble.com/query-is-taking-longer-time-after-a-while-tp25661219p25737471.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.