postgresql - 优化大型 PostGIS 查询

Question

我目前有一个大表mivehdetailedtrajectory(25B 行)和一个小表cell_data_tower(400 行)，我需要使用 PostGIS 加入它们。具体来说，我需要运行这个查询:

SELECT COUNT(traj.*), tower.id
FROM cell_data_tower tower LEFT OUTER JOIN mivehdetailedtrajectory traj
ON ST_Contains(tower.geom, traj.location)
GROUP BY tower.id
ORDER BY tower.id;

它愤怒地错误地指出它不能写入磁盘。这对于 SELECT 来说似乎很奇怪，所以我运行了 EXPLAIN:注意:gserialized_gist_joinsel:不支持连接类型 1

                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Sort  (cost=28905094882.25..28905094883.25 rows=400 width=120)
   Sort Key: tower.id
   ->  HashAggregate  (cost=28905094860.96..28905094864.96 rows=400 width=120)
         ->  Nested Loop Left Join  (cost=0.00..28904927894.80 rows=33393232 width=120)
               Join Filter: ((tower.geom && traj.location) AND _st_contains(tower.geom, traj.location))
               ->  Seq Scan on cell_data_tower tower  (cost=0.00..52.00 rows=400 width=153)
               ->  Materialize  (cost=0.00..15839886.96 rows=250449264 width=164)
                     ->  Seq Scan on mivehdetailedtrajectory traj  (cost=0.00..8717735.64 rows=250449264 width=164)

我不明白为什么 postgres 认为它​​应该具体化内表。另外，老实说，我总体上不了解该计划。似乎它应该将 cell_data_tower 表保存在内存中并迭代 mivehdetailedtrajectory 表。关于如何优化它以 (a) 运行，(b) 在合理的时间内运行的任何想法。具体来说，这似乎应该可以在不到 1 天的时间内完成。

编辑:Postgres 版本 9.3

Answer 1

需要大量内存的查询是相关子查询执行得更好的罕见地方(LATERAL JOIN 应该也可以，但那些超出了我的范围)。另请注意，您没有选择 tower.id，因此您的结果不会太有用。

SELECT tower.id, (SELECT COUNT(traj.*) 
                  FROM mivehdetailedtrajectory traj
                  WHERE ST_Contains(tower.geom, traj.location))
FROM cell_data_tower tower
ORDER BY tower.id;

首先尝试使用 LIMIT 1 运行它。总运行时间应该是一个塔的运行时间 * 塔的数量。