[postgis-users] Getting data from table with interval

[Luca Bertoncello]

Am 07.01.2022 um 20:53 schrieb Paul Ramsey:
> You have an index on the geography, but you cast to geometry in your distance calc, so the index helps you not at all.
> 
> Do this
> 
> SELECT *, ST_Distance(latlng,
> '0101000020E6100000E17A14AE47412C40BC74931804A64940') AS dist FROM
> elevation ORDER BY dist LIMIT 5;

Mmmm... it does not seem to work better:


gis=# explain analyze SELECT *, ST_Distance(latlng,
'0101000020E6100000E17A14AE47412C40BC74931804A64940') AS dist FROM
elevation2x2 ORDER BY dist LIMIT 5;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=193161.20..193161.79 rows=5 width=60) (actual
time=36660.997..36687.559 rows=5 loops=1)
   ->  Gather Merge  (cost=193161.20..341959.16 rows=1275322 width=60)
(actual time=36660.994..36687.553 rows=5 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=192161.18..193755.33 rows=637661 width=60)
(actual time=36296.405..36296.408 rows=4 loops=3)
               Sort Key: (_st_distance(latlng,
'0101000020E6100000E17A14AE47412C40BC74931804A64940'::geography,
'0'::double precision, true))
               Sort Method: top-N heapsort  Memory: 26kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Parallel Seq Scan on elevation2x2
(cost=0.00..181569.86 rows=637661 width=60) (actual
time=10.543..32669.581 rows=510129 loops=3)
 Planning Time: 2.952 ms
 Execution Time: 36687.620 ms
(12 Zeilen)

Thanks
Luca Bertoncello
(lucabert at lucabert.de)