postgresql - 在 t1 中的所有点的 1000 米范围内找到 t2 中的所有点

Question

我有 2 个表，t1 和 t2，每个表都有一个名为 pts_geog 的 geography 类型的列，并且每个都有一个 id 列，这是一个单元标识符。我想在 t1 中添加一列，它计算 t2 到 t1 中任何给定点的 1000 米距离内有 多少 单位。两个表都相当大，每个表大约有 150000 行。计算 t1 中每个点到 t2 中每个点的距离会导致非常昂贵的操作，所以我正在寻找一些关于我在做什么的指导有什么希望。由于内存不足，我一直无法完成此操作。我可以以某种方式拆分操作(使用 where 沿着 t1 的另一个维度)，但我需要更多帮助。这是我想使用的 select:

select
    count(nullif(
        ST_DWithin(
        g1.pts_geog,
        g2.gts_geog,
        1000,
        false),
        false)) as close_1000
from
    t1 as g1,
    t2 as g2
where
    g1.pts_geog IS NOT NULL
    and
    g2.pts_geog IS NOT NULL
GROUP BY g1.id

建议的答案和解释:

airbnb=> EXPLAIN ANALYZE
airbnb-> SELECT t1.listing_id, count(*)
airbnb-> FROM paris as t1
airbnb-> JOIN airdna_property as t2
airbnb-> ON ST_DWithin( t1.pts_geog, t2.pts_geog,1000 )
airbnb-> WHERE t2.city='Paris'
airbnb-> group by t1.listing_id;
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1030317.33..1030386.39 rows=6906 width=8) (actual time=2802071.616..2802084.109 rows=54400 loops=1)
   Group Key: t1.listing_id
   ->  Nested Loop  (cost=0.41..1030282.80 rows=6906 width=8) (actual time=0.827..2604319.421 rows=785571807 loops=1)
         ->  Seq Scan on airdna_property t2  (cost=0.00..74893.44 rows=141004 width=56) (actual time=0.131..738.133 rows=141506 loops=1)
               Filter: (city = 'Paris'::text)
               Rows Removed by Filter: 400052
         ->  Index Scan using paris_pts_geog_idx on paris t1  (cost=0.41..6.77 rows=1 width=64) (actual time=0.133..17.865 rows=5552 loops=141506)
               Index Cond: (pts_geog && _st_expand(t2.pts_geog, '1000'::double precision))
               Filter: ((t2.pts_geog && _st_expand(pts_geog, '1000'::double precision)) AND _st_dwithin(pts_geog, t2.pts_geog, '1000'::double precision, true))
               Rows Removed by Filter: 3260
 Planning time: 0.197 ms
 Execution time: 2802086.005 ms

版本输出:

                                                 version                                                  |            postgis_version            
----------------------------------------------------------------------------------------------------------+---------------------------------------
 PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit | 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

更新 2

这是在按照建议创建索引之后。请注意，由于我添加了新数据，行数略有增加，但这仍然是相同大小的问题。需要52分钟。它仍然在 city 上显示 Seq Scan，我不明白:既然我创建了索引扫描，为什么它不在那里进行索引扫描？

                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=904989.83..905049.21 rows=5938 width=8) (actual time=3118569.759..3118581.444 rows=54400 loops=1)
   Group Key: t1.listing_id
   ->  Nested Loop  (cost=0.41..904960.14 rows=5938 width=8) (actual time=2.624..2881694.755 rows=837837851 loops=1)
         ->  Seq Scan on airdna_property t2  (cost=0.00..74842.84 rows=121245 width=56) (actual time=2.263..949.073 rows=151018 loops=1)
               Filter: (city = 'Paris'::text)
               Rows Removed by Filter: 435564
         ->  Index Scan using paris_pts_geog_idx on paris t1  (cost=0.41..6.84 rows=1 width=64) (actual time=0.139..18.555 rows=5548 loops=151018)
               Index Cond: (pts_geog && _st_expand(t2.pts_geog, '1000'::double precision))
               Filter: ((t2.pts_geog && _st_expand(pts_geog, '1000'::double precision)) AND _st_dwithin(pts_geog, t2.pts_geog, '1000'::double precision, true))
               Rows Removed by Filter: 3257
 Planning time: 0.377 ms
 Execution time: 3118583.203 ms
(12 rows)

Answer 1

您所做的只是选择计数，只需将子句移出选择列表即可修剪连接。

SELECT t1.id, count(*)
FROM t1
JOIN t2
  ON ST_DWithin( t1.pts_geog, t2.pts_geog, 1000 )
GROUP BY t1.id;

如果你需要一个 ST_DWithin 可以使用的索引，运行这个..

CREATE INDEX ON t1 USING gist (pts_geog);
CREATE INDEX ON t2 USING gist (pts_geog);
VACUUM ANALYZE t1;
VACUUM ANALYZE t2;

现在运行上面的 SELECT 查询。

更新2

你的计划表明你对城市进行了序列扫描，所以在城市上创建一个索引，然后我们看看我们还能做些什么

CREATE INDEX ON airdna_property (city);
ANALYZE airdna_property;