Postgres table select query is too slow

Question

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

Your table contains a lot of dead rows that can't be cleaned up ("1401490 dead row versions cannot be removed yet"). The most likely cause for that is that you have connections that are idle in transaction preventing the cleanup of the old rows. — user330315
– user330315, Commented Aug 3, 2016 at 10:01

Laurenz Albe · Accepted Answer · 2016-08-03 10:20:54Z

4

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.

answered Aug 3, 2016 at 10:20

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vignesh Bhaskar Over a year ago

Thanks a lot. I shutdown all app servers connected to the db & ran VACUUM(VERBOZE,ANALYZE) & it deleted the free dead rows. Now the queries are lot faster. I shall also try VACUUM(FULL, ANALYZE) during the maintenance hour.

Laurenz Albe Over a year ago

Don't run VACUUM (FULL) regularly. Normally your tables should never get bloated unless you do a mass delete.

Vignesh Bhaskar Over a year ago

Yes, I do mass deletion once the raw data is processed. Is that okay in that case or does it have any issues?

Laurenz Albe Over a year ago

Sure it is ok, only the table will be blocked during VACUUM (FULL). If you empty the table, you can use TRUNCATE instead of DELETE. You can also leave the table bloated if that is no problem, the next INSERTs will use the empty space.

Collectives™ on Stack Overflow

Postgres table select query is too slow

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related