如何使此过滤器具有可增强性或改进索引
如何使此过滤器具有可增强性或改进索引
提问于 2016-07-13 21:45:36
vzla_seg是公路网的一部分。每段都有各自的方位角。COUNT(*) = 3.849.834
CREATE TABLE vzla_seg (`azimuth` int);
INSERT INTO vzla_seg (`azimuth`)
VALUES
(330), (335),
(340), (345),
(350), (355),
(359), (3),
(5), (15),
(20), (25),
(30), (35)
;我试图在与参数carAzimuth相同的总方向(+/- 30度)上找到分段。
Azimuth距离是指南针度的[0 ..359]。因此，如果carAzimuth = 345与方位角[315, 325, 359, 0, 5, 15]的路段的方向相同，那么
carAzimuth = 355
segmentAzimuth = 340 --> (355 - 340) --> 15 < 30
segmentAzimuth = 359 --> (355 - 359) --> abs(- 4) < 30
segmentAzimuth = 20 --> (355 - 20) --> 25 < 30
carAzimuth = 5
segmentAzimuth = 340 --> (5 - 340) --> abs(- 335) > 330
segmentAzimuth = 359 --> (5 - 359) --> abs(- 354) > 330
segmentAzimuth = 20 --> (5 - 20) --> abs( -15) < 30
explain analyze
SELECT *
FROM map.vzla_seg S
WHERE
abs(carAzimuth - S.azimuth) < 30
OR abs(carAzimuth - S.azimuth) > 330 指数：
CREATE INDEX vzla_seg_azimuth_idx
ON map.vzla_seg
USING btree
(azimuth);解释计划：
"Seq Scan on vzla_seg s (cost=0.00..151243.55 rows=2138790 width=84)
(actual time=0.061..14086.038 rows=690334 loops=1)"
" Filter: ((abs((345 - azimuth)) < 30) OR (abs((345 - azimuth)) > 330))"
" Rows Removed by Filter: 3159500"
"Total runtime: 14283.524 ms"奖金问题：
- 如果解释，请说
Rows Removed by Filter: 3.159.500和rows=690.334匹配总COUNT(*) = 3.849.834- 为什么要花费说
rows=2.138.790
- 为什么要花费说
回答 3
Stack Overflow用户
回答已采纳
发布于 2016-07-13 21:49:07
您可以使用BETWEEN和/或大于/小于：
SELECT *
FROM map.vzla_seg S
WHERE
(S.azimuth BETWEEN (carAzimuth - 30) AND (carAzimuth + 30))
OR S.azimuth < (carAzimuth - 330)
OR S.azimuth > (carAzimuth + 330)Stack Overflow用户
发布于 2016-07-14 00:05:04
你可以试试这个：
with param(car_azimuth) as (select 345)
select *
from vzla_seg s, param
where
s.azimuth > car_azimuth- 30 and
s.azimuth < car_azimuth+ 30 or
s.azimuth < car_azimuth- 330 or
s.azimuth > car_azimuth+ 330;该计划可能取决于参数将以何种方式传递。用常量解释(百万行)：
explain
select *
from vzla_seg s
where
s.azimuth > 345- 30 and
s.azimuth < 345+ 30 or
s.azimuth < 345- 330 or
s.azimuth > 345+ 330;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Bitmap Heap Scan on vzla_seg s (cost=3496.96..11233.44 rows=160405 width=8)
Recheck Cond: (((azimuth > 315) AND (azimuth < 375)) OR (azimuth < 15) OR (azimuth > 675))
-> BitmapOr (cost=3496.96..3496.96 rows=165574 width=0)
-> Bitmap Index Scan on vzla_seg_azimuth_idx (cost=0.00..2598.70 rows=123828 width=0)
Index Cond: ((azimuth > 315) AND (azimuth < 375))
-> Bitmap Index Scan on vzla_seg_azimuth_idx (cost=0.00..773.52 rows=41746 width=0)
Index Cond: (azimuth < 15)
-> Bitmap Index Scan on vzla_seg_azimuth_idx (cost=0.00..4.43 rows=1 width=0)
Index Cond: (azimuth > 675)
(9 rows) Stack Overflow用户
发布于 2016-07-13 21:49:48
看看这是否最佳地使用了索引：
SELECT *
FROM map.vzla_seg S
WHERE s.azimuth > 315 and s.azimuth < 375;如果是的话，我认为逻辑是：
SELECT *
FROM map.vzla_seg S
WHERE (s.azimuth > 315 and s.azimuth < 375) or
(s.azimuth > 15 and s.azimuth < 30) ;如果不使用索引，则使用union all而不是or
SELECT *
FROM map.vzla_seg S
WHERE (s.azimuth > 315 and s.azimuth < 375)
UNION ALL
SELECT *
FROM map.vzla_seg S
WHERE (s.azimuth > 15 and s.azimuth < 30) ;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38362232
复制相关文章
相似问题
腾讯云开发者
