当查询中有“where-in”时，postgis为什么不应用索引？

hl0ma9xz 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(344)

我们有一张table shops ，列名为 location 类型 GEOMETRY(POINT,4326) 以及 version 类型 tinyint 还有一张table version 一行包含一个整数。
为什么下面的查询不使用gist（location）的索引？

SELECT * FROM shops 
WHERE ("version" IN (SELECT "version" FROM "version")) 
ORDER BY (location <-> '0020000001000010e64029d460d2a8aee0404bc8bb0955ea17'::geometry) LIMIT 10;

其中作为没有 IN 是否使用索引？

SELECT * FROM shops 
WHERE ("version" = (SELECT "version" FROM "version" LIMIT 1)) 
ORDER BY (location <-> '0020000001000010e64029d460d2a8aee0404bc8bb0955ea17'::geometry) LIMIT 10;

这是影响我们，因为我们从9日更新到11日。我可以追溯到上述选择的问题。
编辑：添加qry分析
第一个查询（不带索引应用程序）：

"Limit  (cost=25260.30..25260.32 rows=10 width=1275) (actual time=254.809..254.814 rows=10 loops=1)"
"  ->  Sort  (cost=25260.30..25260.39 rows=36 width=1275) (actual time=254.807..254.809 rows=10 loops=1)"
"        Sort Key: ((shops.location <-> '0101000020E6100000E0AEA8D260D4294017EA5509BBC84B40'::geometry))"
"        Sort Method: top-N heapsort  Memory: 54kB"
"        ->  Nested Loop  (cost=41.88..25259.52 rows=36 width=1275) (actual time=0.099..215.201 rows=58179 loops=1)"
"              Join Filter: (shops.version = version.version)"
"              ->  HashAggregate  (cost=41.88..43.88 rows=200 width=4) (actual time=0.014..0.016 rows=1 loops=1)"
"                    Group Key: version.version"
"                    ->  Seq Scan on version  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.009..0.010 rows=1 loops=1)"

第二个查询：

"Limit  (cost=0.28..440.04 rows=10 width=1275) (actual time=0.194..0.233 rows=10 loops=1)"
"  ->  Nested Loop Semi Join  (cost=0.28..1574995.44 rows=35815 width=1275) (actual time=0.193..0.230 rows=10 loops=1)"
"        Join Filter: (shop.version = version.version)"
"        ->  Index Scan using shop_location_idx on shops  (cost=0.28..101549.81 rows=71630 width=1267) (actual time=0.182..0.213 rows=10 loops=1)"
"              Order By: (location <-> '0101000020E6100000E0AEA8D260D4294017EA5509BBC84B40'::geometry)"
"        ->  Materialize  (cost=0.00..48.25 rows=2550 width=4) (actual time=0.001..0.001 rows=1 loops=10)"
"              ->  Seq Scan on version  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.006..0.006 rows=1 loops=1)"

解决了的
请看下面的答案，感谢@jimjones和@jimmacaulay

sql postgresql postgis

来源：https://stackoverflow.com/questions/62173007/why-is-postgis-not-applying-an-index-when-there-is-a-where-in-in-the-query

1条答案

按热度按时间

iecba09b1#

在version表上添加索引可以帮助postgres使用 location 列也是。
因此，添加此索引是第一个查询的修复方法：

CREATE INDEX version_idx
    ON public.version USING btree
    (version)
    TABLESPACE pg_default;

正确地对查找中的所有列应用索引，有助于postgres制定一个性能良好的查询计划。

赞(0）回复(0）举报 2021-07-26

我来回答

当查询中有“where-in”时，postgis为什么不应用索引？

1条答案

相关问题

热门标签

最新问答