sql - 比较几何图形时查询速度慢

Question

我正在尝试比较并创建一个新表。但它需要更多时间来比较。

表 1(架构)

+-------------+----------+-------------+
| Column      | Type     | Modifiers   |
|-------------+----------+-------------|
| line_id     | bigint   |             |
| junction    | integer  |             |
| geom        | geometry |             |
+-------------+----------+-------------+
Indexes:
    "points_geom_gix" gist (geom)

结点包含 0 或 1。

表2

+----------+----------+-------------+
| Column   | Type     | Modifiers   |
|----------+----------+-------------|
| line_id  | bigint   |             |
| geom     | geometry |             |
+----------+----------+-------------+
Indexes:
    "jxn_geom_gix" gist (geom)

我想通过比较两个表的几何形状来创建一个新表表 3。

条件

• 从两个表中选择两个几何图形相等的几何图形。
• 从 table1 中选择 geom，其中 junction = 1 并且 geom 不存在于表 3。

我试过如下

CREATE TABLE table3 as select a.geom from table1 a, table2 b where st_equals(a.geom,b.geom);

(在表 3 的 geom 列上创建要点索引)

和

INSERT INTO table3 SELECT a.geom from table1 a, table3 b where a.junction = 1 and NOT st_equals(a.geom,b.geom);

但是第二个查询花费了大量时间。

有人会帮助我优化查询吗？

Answer 1

在你最后的 sql 中，你产生了近乎笛卡尔的结果。例如，如果表 1 中有 10 000 个 junciton = 1 的几何图形不存在于表 3 中，而在表 3 中您已经有 10 000 个其他几何图形，那么对于每个 juncition = 1 的几何图形，您将返回 10 000 行。在这种情况下，当您想查找不在其他表中的某些行时，使用 EXISTS 子句它不会增加您的结果，也不会产生笛卡尔。

INSERT INTO table3 
SELECT a.geom 
  from table1 a
 where a.junction = 1 
   and NOT exists (select from table3 b 
                    where st_equals(a.geom,b.geom)
                      and st_DWithin(a.geom, b.geom,0));

我编辑了查询 - 添加了 st_Dwithin(a.geom, b.geom,0) 它应该使查询更快，因为不存在应该只比较它们之间距离为 0 的这些 geom(如果它们之间没有 0 距离)肯定有不平等)。通常 st_dwithin 将使用 gist 索引来过滤不够接近以至于不相等的 geoms。