sql - 如何针对矩形区域优化此 SQL 查询？

Question

我正在尝试优化以下查询，但我不清楚哪种索引或哪些索引最好。我在二维平面中存储图 block 并查询该平面的矩形区域。出于这个问题的目的，该表具有以下列:

• id: 主键整数
• world_id:一个整数外键，充当图 block 子集的 namespace
• tileY:Y坐标整数
• tileX:X坐标整数
• 值:此磁贴的内容，如果重要，则为 varchar。

我有以下索引:

• "ywot_tile_pkey"主键，btree (id)
• "ywot_tile_world_id_key"UNIQUE, btree (world_id, "tileY", "tileX")
• "ywot_tile_world_id"btree (world_id)

这是我要优化的查询:

ywot=> EXPLAIN ANALYZE SELECT * FROM "ywot_tile" WHERE ("world_id" = 27685  AND "tileY" <= 6  AND "tileX" <= 9  AND "tileX" >= -2  AND "tileY" >= -1 );                                                                    QUERY PLAN                                                                 -------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ywot_tile  (cost=11384.13..149421.27 rows=65989 width=168) (actual time=79.646..80.075 rows=96 loops=1)
   Recheck Cond: ((world_id = 27685) AND ("tileY" <= 6) AND ("tileY" >= (-1)) AND ("tileX" <= 9) AND ("tileX" >= (-2)))
   ->  Bitmap Index Scan on ywot_tile_world_id_key  (cost=0.00..11367.63 rows=65989 width=0) (actual time=79.615..79.615 rows=125 loops=1)
         Index Cond: ((world_id = 27685) AND ("tileY" <= 6) AND ("tileY" >= (-1)) AND ("tileX" <= 9) AND ("tileX" >= (-2)))
 Total runtime: 80.194 ms

所以世界是固定的，我们正在查询一 block 矩形区域。一些可能相关的更多信息:

查询区域的所有图 block 可能存在也可能不存在

查询矩形的高度和宽度通常约为 10x10-20x20

对于任何给定的 (world, X) 或 (world, Y) 对，可能存在无限数量的匹配图 block ，但最坏的情况目前约为 10,000，而且通常要少得多。

新磁贴的创建频率远低于现有磁贴的更新频率(更改“值”)，而且更新频率本身也远低于上面查询中的读取频率。

我唯一能想到的就是在 (world, X) 和 (world, Y) 上建立索引。我的猜测是数据库将能够获取这两个集合并将它们相交。问题在于，对于其中任何一个，可能 的匹配次数是无限的。有没有其他类型的索引更合适？

Answer 1

在“ywot_tile_world_id_key”上集群表，主键似乎只是一个人工 ID。如果您有更多独特的垂直值，而不是水平值，您可能需要颠倒顺序 (world-id, y, x)。同时删除 world-id 上的单独索引，它与复合索引重复。