postgresql - 在过滤未索引的列后是否可以从 PostGIS 地理空间索引中读取？

Question

是否可以强制 Postgres 规划器根据标量值的顺序扫描来过滤结果集，然后才根据空间索引执行 GIS 功能/比较？我的生产用例有点复杂，但这个例子说明了我的目标:

创建一个包含地理列和该列索引的表:

CREATE TABLE test_table (
id SERIAL,
min INTEGER,
max INTEGER,
active BOOLEAN,
geo GEOGRAPHY(Polygon,4326) );

CREATE INDEX test_table_gidx ON test_table USING gist (geo);

我在表中植入了 6000 条记录，使用了非平凡的多边形几何图形(每个约 10k 个顶点)。

根据 min 执行简单选择和 max以毫秒为单位。

geotest=> SELECT count(*) FROM test_table t WHERE t.min <= 50 AND t.max >= 50 ;
 count 
-------
  4000
(1 row)

Time: 3.066 ms

安ST_Intersects查询 geo使用我的硬件和配置，专栏大约需要 10 秒。

geotest=> SELECT count(*) FROM test_table t WHERE ST_Intersects(t.geo, ST_GeogFromText('SRID=4326;POINT(-104.70348 38.6661)'));
 count 
-------
  1000
(1 row)

Time: 11051.466 ms

结合WHERE子句，查询仍然在仅几何查询的时间范围内运行。

geotest=> SELECT count(*) FROM test_table t WHERE  t.min <= 50 AND t.max >= 50 AND ST_Intersects(t.geo, ST_GeogFromText('SRID=4326;POINT(-104.70348 38.6661)'));
 count 
-------
  1000
(1 row)

Time: 11072.337 ms

如果有帮助，下面是规划器处理组合查询的方式:

geotest=> EXPLAIN ANALYZE SELECT count(*) FROM test_table t WHERE  t.min <= 50 AND t.max >= 50 AND ST_Intersects(t.geo, ST_GeogFromText('SRID=4326;POINT(-104.70348 38.6661)'));
                                                                                            QUERY PLAN                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8.43..8.44 rows=1 width=8) (actual time=10962.332..10962.333 rows=1 loops=1)
   ->  Index Scan using test_table_gidx on test_table t  (cost=0.15..8.42 rows=1 width=0) (actual time=8.199..10961.612 rows=1000 loops=1)
         Index Cond: (geo && '0101000020E6100000F758FAD0052D5AC0CCEEC9C342554340'::geography)
         Filter: ((min <= 50) AND (max >= 50) AND (_st_distance(geo, '0101000020E6100000F758FAD0052D5AC0CCEEC9C342554340'::geography, '0'::double precision, false) < '1e-05'::double precision))
         Rows Removed by Filter: 1000
 Planning time: 0.260 ms
 Execution time: 10962.606 ms

有没有办法只执行 ST_Intersects比较匹配 min 的记录/max WHERE 的过滤器条款？

Answer 1

使用 CTE 作为优化栅栏。

WITH t1 AS (
  SELECT *
  FROM test_table t
  WHERE t.min <= 50 AND t.max >= 50
)
SELECT *
FROM t1
JOIN test_table AS t2
ON t2.id=t1.id
  AND ST_Intersects(
    t2.geom,
    ST_SetSRID(ST_MakePoint(-104.70348, 38.6661),4326)
  );