我们需要极高效率地运行查询,在其中我们需要在区域+在一个时间范围内找到一个条目。这两列都已编入索引。当运行像这样的查询时:
SELECT * FROM "tablename"
WHERE ("tablename"."geom" @ ST_GeomFromEWKB('\x0103000020e61000000100000005000000da194bfd61c81140605edb36e7db2040da194bfd61c81140745c3233d80d21409858cfff61a91240745c3233d80d21409858cfff61a91240605edb36e7db2040da194bfd61c81140605edb36e7db2040'::bytea)
AND "tablename"."date" >= '2019-01-01T00:00:00+00:00'::timestamptz)解释显示Postgres运行两个位图索引扫描,然后对这些子集进行位图堆扫描。这是可执行的,但性能不够,因为DB有数千万个条目,而这些子集有几十万个条目,而这种堆扫描+可能将子集写入磁盘会导致不可接受的~1000 is延迟。
多列索引将大大提高性能,但我们不知道如何制作Postgres将在这些查询中使用的索引。我们找到了btree-gist,我们可以用它创建一个多列索引:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX test_mult_idx ON tablename USING GIST (geom, date);但是,当我们使用这个多列索引运行上面的查询时,不会使用它,因为查询计划器使用每个列的单列索引。我们删除了这些单个col索引,以测试我们是否可以强制使用它,并且它使用了多列索引,但是性能没有改善。它只做两个索引扫描,然后进行堆扫描。
我们已经浏览过互联网,在任何地方都找不到任何关于空间+其他字段的多列索引的内容。我们不理解运行btree / gist /其他类型索引的数学--在不同的数据类型下,这在逻辑上是可能的吗?有什么我们能做的吗?
评论者要求的额外信息:从explain (analyze, buffers) select ...生成的未格式化的执行计划
"Bitmap Heap Scan on historic (cost=1894.90..2485.28 rows=150 width=286) (actual time=110.183..226.016 rows=24115 loops=1)"
" Recheck Cond: ((geom @ '0103000020E610000001000000050000005E11FC6F25E26240ED2AA4FCA4FE40C05E11FC6F25E26240C0EC9E3C2CF440C0C58F31772DE96240C0EC9E3C2CF440C0C58F31772DE96240ED2AA4FCA4FE40C05E11FC6F25E26240ED2AA4FCA4FE40C0'::geometry) AND (date >= '2019-01-01 11:00:00+11'::timestamp with time zone))"
" Rows Removed by Index Recheck: 173436"
" Heap Blocks: exact=6673 lossy=7503"
" Buffers: shared read=18750"
" -> BitmapAnd (cost=1894.90..1894.90 rows=150 width=0) (actual time=108.636..108.637 rows=0 loops=1)"
" Buffers: shared read=4574"
" -> Bitmap Index Scan on geom_idx (cost=0.00..237.10 rows=5425 width=0) (actual time=81.729..81.729 rows=261632 loops=1)"
" Index Cond: (geom @ '0103000020E610000001000000050000005E11FC6F25E26240ED2AA4FCA4FE40C05E11FC6F25E26240C0EC9E3C2CF440C0C58F31772DE96240C0EC9E3C2CF440C0C58F31772DE96240ED2AA4FCA4FE40C05E11FC6F25E26240ED2AA4FCA4FE40C0'::geometry)"
" Buffers: shared read=4106"
" -> Bitmap Index Scan on date_idx (cost=0.00..1657.47 rows=150005 width=0) (actual time=21.662..21.662 rows=530545 loops=1)"
" Index Cond: (date >= '2019-01-01 11:00:00+11'::timestamp with time zone)"
" Buffers: shared read=468"
"Planning Time: 0.164 ms"
"Execution Time: 227.673 ms"select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS,不知道为什么空间字段是“用户定义的”。其他的cols是整数、实、文本和双精度:
"column_name" "data_type"
"geom" "USER-DEFINED"
"date" "timestamp without time zone"发布于 2022-02-03 16:39:11
您的表表示列是“没有时区的时间戳”,但是查询的值指定为“带时区的时间戳”。在我看来,仅仅这种类型的不匹配就足以阻止它使用多列索引。当然,我无法访问您的真实数据集,所以我不知道会发生什么。
https://stackoverflow.com/questions/70964593
复制相似问题