Am having a gps-tracking application.It has a table named gps_vehicle_data where incoming gps data are stored frequently. I query this table at frequent intervals to process it as it contains just raw data. Recently, I am witnessing long delays in the execution of select statements on the table. Below is the results of EXPLAIN. I also tried doing VACUUM & pasting the results below. What could be the reason?
EXPLAIN (ANALYZE, BUFFERS) select * from gps_vehicle_data;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Seq Scan on gps_vehicle_data (cost=0.00..130818.81 rows=1400881 width=1483) (actual time=209.129..62488.822 rows=9635 loops=1)
Buffers: shared hit=13132 read=103678 dirtied=67 written=25
Planning time: 0.050 ms
Execution time: 62500.850 ms
VACUUM OUTPUT.
VACUUM (VERBOSE,ANALYSE) gps_vehicle_data;
INFO: vacuuming "public.gps_vehicle_data"
INFO: index "gps_vehicle_data_pkey" now contains 1398939 row versions in 10509 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.09u sec elapsed 9.38 sec.
INFO: index "gps_vehicle_data_status_idx" now contains 1398939 row versions in 4311 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.04u sec elapsed 4.50 sec.
INFO: index "gps_vehicle_data_url_data_idx" now contains 1399004 row versions in 98928 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.76s/0.88u sec elapsed 82.74 sec.
INFO: index "gps_vehicle_data_createdon_idx" now contains 1399007 row versions in 3946 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.02u sec elapsed 1.92 sec.
INFO: "gps_vehicle_data": found 0 removable, 1402484 nonremovable row versions in 116884 out of 116884 pages
DETAIL: 1401490 dead row versions cannot be removed yet.
There were 143431 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 1.70s/2.38u sec elapsed 200.61 sec.
INFO: vacuuming "pg_toast.pg_toast_17296"
INFO: index "pg_toast_17296_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.01 sec.
INFO: "pg_toast_17296": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: analyzing "public.gps_vehicle_data"
INFO: "gps_vehicle_data": scanned 30000 of 116884 pages, containing 335 live rows and 359656 dead rows; 335 rows in sample, 1042851 estimated total rows
VACUUM
You read over 100000 blocks to get some 10000 rows, that means that your table consists almost entirely of nothing (it is suffering from table bloat).
The table must have contained many more data at some time, and most of these have been deleted since, which caused the bloat.
As @a_horse_with_no_name mentioned, some of your rows cannot be reclaimed because there are some old transactions blocking them, but while VACUUM will free dead rows, it will not reorganize the table to get rid of the bloat.
The correct solution in this case is to use VACUUM (FULL, ANALYZE) gps_vehicle_data (the ANALYZE is just for good measure since it looks like your table statistics are off), which will reorganize the table. Be warned, however, that all access to the table is blocked while VACUUM (FULL) is running.