如何在PostgreSQL中创建包含POINT类型的复合索引以优化查询

首先注意到你的查询里写了SUM(packets)，但原表结构里对应的字段是count，应该是笔误，修正后的查询应为：

SELECT SUM(count) FROM bins
WHERE (start BETWEEN '2023-10-30' AND '2023-10-31')
AND bits = B'0000000000001001'
AND topleft <@ BOX '(90500000000,135800000000)(90600000000,135900000000)';

针对这个查询的过滤条件（bits等值匹配、start时间范围、topleft空间范围），推荐创建带btree_gist扩展的复合GIST索引，具体方案如下：

步骤1：安装btree_gist扩展（若未安装） #

PostgreSQL原生GIST索引不支持timestamp类型的范围查询，需要btree_gist扩展让GIST兼容btree类字段，从而在复合索引中同时处理时间范围和空间条件：

CREATE EXTENSION IF NOT EXISTS btree_gist;

步骤2：创建复合GIST索引 #

将等值匹配的bits放在索引最前列，接着是范围查询的start，最后是空间字段topleft，同时通过INCLUDE子句加入count字段做成覆盖索引，避免查询时回表读取原数据：

CREATE INDEX idx_bins_bits_start_topleft ON bins USING gist (bits, start, topleft) INCLUDE (count);

设计逻辑说明 #

• bits作为等值条件放在最前，可快速筛选出符合条件的数据集，缩小后续索引扫描范围。
• 借助btree_gist，start的时间范围查询能直接在GIST索引内生效，进一步过滤数据。
• topleft的空间包含查询<@是GIST索引的原生高效场景，能精准匹配空间范围。
• INCLUDE(count)让索引包含聚合所需字段，数据库仅扫描索引就能完成SUM(count)计算，无需访问主表，大幅提升查询效率。

验证索引效果 #

执行以下语句查看查询计划，确认新创建的复合索引是否被使用：

EXPLAIN ANALYZE
SELECT SUM(count) FROM bins
WHERE (start BETWEEN '2023-10-30' AND '2023-10-31')
AND bits = B'0000000000001001'
AND topleft <@ BOX '(90500000000,135800000000)(90600000000,135900000000)';

内容的提问来源于stack exchange，提问作者fadedbee