Juan Carlos Oropeza Juan Carlos Oropeza - 4 months ago 6
SQL Question

How make this filter sargeable or improve index

vzla_seg
are the road network segments. Each segment have his general azimuth.
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)
;


Im trying to find segments on the same general direction (+/- 30 degrees) as my parameter
carAzimuth
.

Azimuth
range is
[0 ..359]
for compass degrees. So if
carAzimuth = 345
is on the same direction as a road segments with azimuth
[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


enter image description here

explain analyze
SELECT *
FROM map.vzla_seg S
WHERE
abs(carAzimuth - S.azimuth) < 30
OR abs(carAzimuth - S.azimuth) > 330


Index:

CREATE INDEX vzla_seg_azimuth_idx
ON map.vzla_seg
USING btree
(azimuth);


Explain Plan:

"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"


Bonus question:


  • If explain say
    Rows Removed by Filter: 3.159.500
    and
    rows=690.334
    that match total
    COUNT(*) = 3.849.834



    • Why cost say
      rows=2.138.790
      ?



Answer

You can use BETWEEN and/ or greater than/less than:

    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))
Comments