问postgresql:分层树的实现

EN

然后在后端，我计划构建一个字符串，该字符串应该指向“给定缩放级别的视口”，例如'02113‘，并且我希望在树坐标列中找到具有此前缀('02113')的所有点。

只要在索引列中查看字符串的最左边的五个(或六个或七个)字符，普通索引在任何现代dbms上都应该表现良好。

SELECT ...
...
WHERE column_name LIKE '02113%';

在PostgreSQL中，您还可以构建index on an expression。因此，您可以在前五个字符上创建索引。

CREATE INDEX your_index_name ON your_table (left(column_name, 5));

如果有三到四个这样的索引，我希望PostgreSQL的查询优化器选择正确的索引。(一个用于5个字符，一个用于6个字符，依此类推)

我构建了一个表，并在其中填充了一百万行随机数据。

在下面的查询中，PostgreSQL的查询优化器确实选择了正确的索引。

explain analyze
select s
from coords
where left(s, 5) ='12345';

它在0.1ms内返回。

我还使用GROUP BY进行了测试。同样，PostgreSQL的查询优化器选择了正确的索引。

"GroupAggregate  (cost=0.00..62783.15 rows=899423 width=8) (actual time=91.300..3096.788 rows=90 loops=1)"
"  ->  Index Scan using coords_left_idx1 on coords  (cost=0.00..46540.36 rows=1000000 width=8) (actual time=0.051..2915.265 rows=1000000 loops=1)"
"Total runtime: 3096.914 ms"

GROUP BY子句中类似left(name, 2)的表达式将要求PostgreSQL接触索引中的每一行(如果不是表中的每一行)。这就是我的查询耗时3096ms的原因；它必须触及索引中的一百万行。但是您可以从EXPLAIN计划中看到它使用了索引。

通常，我希望地理应用程序对PostGIS表使用边界框来减少您访问的行数。如果您的四叉树实现不能做得更好，我会坚持使用PostGIS足够长的时间来成为它的专家。(除非您花了一些时间，否则您不会确定它不能完成工作。)