我使用的是与AWS aurora-postgresql兼容的。Postgresql版本为11.7，postgis版本为2.5
我有vehicle和vehicle_current_status表。
vehicle表几乎有4000行。
vehicle表的id列是自动递增主键。
vehicle_current_status表与vehicle表具有一一对应的关系。
vehicle_current_status表的id列是自动递增主键。
vehicle_current_status表的coordinate列是SRID为4326的几何体。我没有在coordinate列上使用索引，因为更新坐标查询执行了很多。
有2845个条目的大IN条件。
查询1(不带类型转换(
SELECT "v"."id" AS "v_id"
FROM "vehicle" "v"
LEFT JOIN "vehicle_current_status" "vs" ON "vs"."vehicle_id" = "v"."id"
WHERE
ST_DWITHIN(
"vs"."coordinate",
ST_SETSRID(
ST_GEOMFROMGEOJSON('{"type": "Point", "coordinates": [127.03,37.509]}'),
4326),
0.017)
AND "v"."id" IN (VALUES(1023),(1006),(3674),(1692)... 2845 entries)
AND "v".IS_ACTIVE IS TRUE
AND "vs".BATTERY_PERCENTAGE > 30
查询1解释
"Nested Loop Semi Join (cost=0.28..12330.99 rows=2 width=4) (actual time=1.118..83.764 rows=121 loops=1)"
" Join Filter: (vs.vehicle_id = ""*VALUES*"".column1)"
" Rows Removed by Join Filter: 578765"
" Buffers: shared hit=11846"
" -> Nested Loop (cost=0.28..12160.29 rows=3 width=8) (actual time=0.028..9.577 rows=250 loops=1)"
" Buffers: shared hit=11846"
" -> Seq Scan on vehicle_current_status vs (cost=0.00..12135.39 rows=3 width=4) (actual time=0.017..8.799 rows=250 loops=1)"
" Filter: ((coordinate && '0103000020E6100000010000000500000046B6F3FDD4C05F40E5D022DBF9BE424046B6F3FDD4C05F4017D9CEF753C342405EBA490C02C35F4017D9CEF753C342405EBA490C02C35F40E5D022DBF9BE424046B6F3FDD4C05F40E5D022DBF9BE4240'::geometry) AND (battery_percentage > 30) AND ('0101000020E610000052B81E85EBC15F40FED478E926C14240'::geometry && st_expand(coordinate, '0.017'::double precision)) AND _st_dwithin(coordinate, '0101000020E610000052B81E85EBC15F40FED478E926C14240'::geometry, '0.017'::double precision))"
" Rows Removed by Filter: 3607"
" Buffers: shared hit=11094"
" -> Index Scan using ""PK_187fa17ba39d367e5604b3d1ec9"" on vehicle v (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=250)"
" Index Cond: (id = vs.vehicle_id)"
" Filter: (is_active IS TRUE)"
" Buffers: shared hit=752"
" -> Materialize (cost=0.00..49.79 rows=2845 width=4) (actual time=0.000..0.131 rows=2316 loops=250)"
" -> Values Scan on ""*VALUES*"" (cost=0.00..35.56 rows=2845 width=4) (actual time=0.001..0.533 rows=2845 loops=1)"
"Planning Time: 2.045 ms"
"Execution Time: 83.853 ms"
查询2(类型转换为地理位置(
SELECT "v"."id" AS "v_id"
FROM "vehicle" "v"
LEFT JOIN "vehicle_current_status" "vs" ON "vs"."vehicle_id" = "v"."id"
WHERE
ST_DWITHIN(
"vs"."coordinate"::geography,
ST_SETSRID(
ST_GEOMFROMGEOJSON('{"type": "Point", "coordinates": [127.03,37.509]}'),
4326)::geography,
1800, false)
AND "v"."id" IN (VALUES(1023),(1006),(3674),(1692)... 2845 entries)
AND "v".IS_ACTIVE IS TRUE
AND "vs".BATTERY_PERCENTAGE > 30
查询2解释
"Nested Loop (cost=106.97..12760.97 rows=35 width=4) (actual time=1.988..13.254 rows=123 loops=1)"
" Join Filter: (vs.vehicle_id = v.id)"
" Buffers: shared hit=11466"
" -> Hash Join (cost=106.69..12744.01 rows=35 width=8) (actual time=1.977..12.937 rows=123 loops=1)"
" Hash Cond: (vs.vehicle_id = ""*VALUES*"".column1)"
" Buffers: shared hit=11097"
" -> Seq Scan on vehicle_current_status vs (cost=0.00..12636.80 rows=47 width=4) (actual time=0.145..11.040 rows=253 loops=1)"
" Filter: ((battery_percentage > 30) AND ((coordinate)::geography && '0101000020E610000052B81E85EBC15F40FED478E926C14240'::geography) AND ('0101000020E610000052B81E85EBC15F40FED478E926C14240'::geography && _st_expand((coordinate)::geography, '1800'::double precision)) AND _st_dwithin((coordinate)::geography, '0101000020E610000052B81E85EBC15F40FED478E926C14240'::geography, '1800'::double precision, true))"
" Rows Removed by Filter: 3604"
" Buffers: shared hit=11097"
" -> Hash (cost=71.12..71.12 rows=2845 width=4) (actual time=1.809..1.809 rows=2845 loops=1)"
" Buckets: 4096 Batches: 1 Memory Usage: 133kB"
" -> HashAggregate (cost=42.67..71.12 rows=2845 width=4) (actual time=1.071..1.392 rows=2845 loops=1)"
" Group Key: ""*VALUES*"".column1"
" -> Values Scan on ""*VALUES*"" (cost=0.00..35.56 rows=2845 width=4) (actual time=0.001..0.532 rows=2845 loops=1)"
" -> Index Scan using ""PK_187fa17ba39d367e5604b3d1ec9"" on vehicle v (cost=0.28..0.47 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=123)"
" Index Cond: (id = ""*VALUES*"".column1)"
" Filter: (is_active IS TRUE)"
" Buffers: shared hit=369"
"Planning Time: 2.274 ms"
"Execution Time: 13.380 ms"
这很奇怪为什么选择地理位置更快
如果我删除大IN条件"v"."id" IN (VALUES...)，那么查询1比查询2快。
查询1解释(没有类型强制转换，删除大IN条件(
"Nested Loop (cost=0.28..12531.73 rows=4 width=4) (actual time=0.023..9.378 rows=250 loops=1)"
" Buffers: shared hit=11846"
" -> Seq Scan on vehicle_current_status vs (cost=0.00..12498.54 rows=4 width=4) (actual time=0.013..8.744 rows=250 loops=1)"
" Filter: ((coordinate && '0103000020E6100000010000000500000046B6F3FDD4C05F40E5D022DBF9BE424046B6F3FDD4C05F4017D9CEF753C342405EBA490C02C35F4017D9CEF753C342405EBA490C02C35F40E5D022DBF9BE424046B6F3FDD4C05F40E5D022DBF9BE4240'::geometry) AND (battery_percentage > 30) AND ('0101000020E610000052B81E85EBC15F40FED478E926C14240'::geometry && st_expand(coordinate, '0.017'::double precision)) AND _st_dwithin(coordinate, '0101000020E610000052B81E85EBC15F40FED478E926C14240'::geometry, '0.017'::double precision))"
" Rows Removed by Filter: 3607"
" Buffers: shared hit=11094"
" -> Index Scan using ""PK_187fa17ba39d367e5604b3d1ec9"" on vehicle v (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=250)"
" Index Cond: (id = vs.vehicle_id)"
" Filter: (is_active IS TRUE)"
" Buffers: shared hit=752"
"Planning Time: 0.347 ms"
"Execution Time: 9.415 ms"
查询2解释(类型转换为地理，删除大IN条件(
"Nested Loop (cost=0.28..12886.79 rows=47 width=4) (actual time=0.122..13.833 rows=253 loops=1)"
" Buffers: shared hit=11858"
" -> Seq Scan on vehicle_current_status vs (cost=0.00..12636.80 rows=47 width=4) (actual time=0.114..13.037 rows=253 loops=1)"
" Filter: ((battery_percentage > 30) AND ((coordinate)::geography && '0101000020E610000052B81E85EBC15F40FED478E926C14240'::geography) AND ('0101000020E610000052B81E85EBC15F40FED478E926C14240'::geography && _st_expand((coordinate)::geography, '1800'::double precision)) AND _st_dwithin((coordinate)::geography, '0101000020E610000052B81E85EBC15F40FED478E926C14240'::geography, '1800'::double precision, true))"
" Rows Removed by Filter: 3604"
" Buffers: shared hit=11097"
" -> Index Scan using ""PK_187fa17ba39d367e5604b3d1ec9"" on vehicle v (cost=0.28..5.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=253)"
" Index Cond: (id = vs.vehicle_id)"
" Filter: (is_active IS TRUE)"
" Buffers: shared hit=761"
"Planning Time: 0.348 ms"
"Execution Time: 13.880 ms"
为什么在有大IN条件的情况下施放到地理位置更快
当它认为只需要在列表中搜索3次时，似乎不值得将其预处理为哈希表。事实证明这是一个错误，因为它实际上需要搜索250次。
当你投下它时，它会认为它必须在列表中搜索47次。这虽然仍然是错误的，但更接近现实，并导致一个更好的计划。
为什么选角会给出不同的行估计？不知道。也许几何与地理？如果你想研究一下，你应该简化查询，去掉连接和battery_percentage的标准，只关注空间方面。