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

``````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`
?

You can use `BETWEEN` and/ or greater than/less than:
``````    SELECT *