Postgres表选择查询太慢

Question

我有一个gps跟踪应用程序。它有一个名为gps_vehicle_data的表，其中传入的gps数据经常存储。我经常查询这个表格来处理它，因为它只包含原始数据。最近，我目睹了执行陈述中陈述的长时间拖延。以下是EXPLAIN的结果。我也试着在VACUUM &上粘贴下面的结果。可能是什么原因？

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 

Answer 1

你读100000块得到一些10000行，这意味着你的表几乎完全由虚无（它是从表膨胀痛苦）。

该表必须包含更多的数据在某些时候，其中大部分已被删除，从而导致膨胀。

正如@a_horse_with_no_name提到的那样，由于有一些旧事务阻塞了它们，所以你的某些行不能被回收，但是当VACUUM会释放死行时，它不会重新组织表以摆脱膨胀。

在这种情况下，正确的解决方案是使用VACUUM (FULL, ANALYZE) gps_vehicle_data（ANALYZE仅用于衡量表格，因为它看起来像表格统计信息已关闭），这将重新组织表格。但是，请注意，当VACUUM (FULL)正在运行时，表格的所有访问都被阻止。