How make this filter sargeable or improve index

Question

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

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 1

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)