@
249239432 我单机 pg 查了 1000w 个点,每个点附近 50 个点的数量(平均 5 个左右)也就 20 分钟
explain analyse select id,(select count(*) from geo where st_dwithin(point,g.point,50)) from geo g;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on geo g (cost=0.00..165365708334.00 rows=10000000 width=12) (actual time=1.076..1049434.731 rows=10000000 loops=1)
SubPlan 1
-> Aggregate (cost=16536.54..16536.55 rows=1 width=8) (actual time=0.105..0.105 rows=1 loops=10000000)
-> Index Scan using geo_point_idx on geo (cost=0.54..16534.04 rows=1000 width=0) (actual time=0.027..0.104 rows=14 loops=10000000)
Index Cond: (point && _st_expand(g.point, '50'::double precision))
Filter: st_dwithin(point, g.point, '50'::double precision, true)
Rows Removed by Filter: 7
Planning Time: 0.756 ms
Execution Time: 1049626.246 ms
(9 rows)
Time: 1049627.962 ms (17:29.628)