我有两个正在运行的Postgresql实例,一个在运行版本10.5的本地计算机上,另一个在运行9.5.10的生产计算机上。我在本地计算机上创建表,并使用pg_dump和pr_restore将它们移动到生产计算机。当我在本地计算机上运行以下命令时,大约需要 100 毫秒:
CREATE TABLE test_point AS
SELECT a.*, b.*, a.total_score + b.total_score_table_2 AS total_score_all
FROM master_enigma_table_designations b,
ST_Transform(ST_SetSRID(ST_Makepoint(-408601.4826183041,6707237.695265564), 3857), 27700) dropped_pin LEFT JOIN
master_enigma_table a
ON ST_Within(dropped_pin, a.wkb_geometry)
WHERE a.poly_id = b.poly_id_new;
当我运行解释分析时,我得到以下输出:
"Nested Loop (cost=1119.13..180619.12 rows=9594 width=4224) (actual time=0.157..0.225 rows=1 loops=1)"
" Buffers: shared hit=22"
" -> Nested Loop (cost=1118.69..118339.83 rows=9594 width=2444) (actual time=0.126..0.189 rows=1 loops=1)"
" Buffers: shared hit=18"
" -> Function Scan on dropped_pin (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.006 rows=1 loops=1)"
" -> Bitmap Heap Scan on master_enigma_table a (cost=1118.69..118243.88 rows=9594 width=2444) (actual time=0.108..0.167 rows=1 loops=1)"
" Recheck Cond: (wkb_geometry ~ dropped_pin.dropped_pin)"
" Filter: _st_contains(wkb_geometry, dropped_pin.dropped_pin)"
" Rows Removed by Filter: 2"
" Heap Blocks: exact=3"
" Buffers: shared hit=18"
" -> Bitmap Index Scan on master_enigma_table_gist_index (cost=0.00..1116.29 rows=28783 width=0) (actual time=0.089..0.090 rows=3 loops=1)"
" Index Cond: (wkb_geometry ~ dropped_pin.dropped_pin)"
" Buffers: shared hit=8"
" -> Index Scan using master_enigma_table_designations_poly_id on master_enigma_table_designations b (cost=0.44..6.48 rows=1 width=1772) (actual time=0.021..0.024 rows=1 loops=1)"
" Index Cond: (poly_id_new = a.poly_id)"
" Buffers: shared hit=4"
"Planning time: 1.397 ms"
"Execution time: 10.058 ms"
当我在生产计算机上运行完全相同的查询时,需要 8 分钟。当我运行解释分析时,我得到:
"Nested Loop (cost=0.44..15399024.56 rows=9594 width=4208) (actual time=326842.620..478541.379 rows=1 loops=1)"
" Buffers: shared hit=1314092 read=6890152"
" -> Nested Loop (cost=0.00..15323938.18 rows=9594 width=2425) (actual time=326842.576..478541.332 rows=1 loops=1)"
" Join Filter: ((a.wkb_geometry ~ dropped_pin.dropped_pin) AND _st_contains(a.wkb_geometry, dropped_pin.dropped_pin))"
" Rows Removed by Join Filter: 28783093"
" Buffers: shared hit=1314088 read=6890152"
" -> Function Scan on dropped_pin (cost=0.00..0.01 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)"
" -> Seq Scan on master_enigma_table a (cost=0.00..7768445.30 rows=28782830 width=2425) (actual time=0.018..458071.770 rows=28783094 loops=1)"
" Buffers: shared hit=590465 read=6890152"
" -> Index Scan using master_enigma_table_designations_new_poly_id on master_enigma_table_designations b (cost=0.44..7.81 rows=1 width=1783) (actual time=0.012..0.013 rows=1 loops=1)"
" Index Cond: (poly_id_new = a.poly_id)"
" Buffers: shared hit=4"
"Planning time: 26.628 ms"
"Execution time: 478582.199 ms"
我的生产计算机似乎没有使用位图索引扫描,而我的本地计算机却使用了。两个实例都有相同的表、索引,我已经对所有表运行了 ANALYZE。我已经运行了全部显示,并且位图扫描也设置为打开。
有没有人对我能做些什么来解决我的问题有任何建议。
不同版本的Postgres意味着您也使用不同版本的PostGIS。9.5 的旧版本没有使用 st_within(( 函数上的索引。在 9.5 中,您必须添加 st_dwithin(dropped_pin, a.wkb_geometry,0( 才能使用索引值。
CREATE TABLE test_point AS
SELECT a.*, b.*, a.total_score + b.total_score_table_2 AS total_score_all
FROM master_enigma_table_designations b,
ST_Transform(ST_SetSRID(ST_Makepoint(-408601.4826183041,6707237.695265564), 3857), 27700) dropped_pin
LEFT JOIN master_enigma_table a ON ST_Within(dropped_pin, a.wkb_geometry)
and ST_DWithin(dropped_pin, a.wkb_geometry,0)
WHERE a.poly_id = b.poly_id_new;
简单的建议 - 在两台计算机上使用相同的 Postgres 和 PostGIS 版本,否则您将遇到更多此类不兼容问题。