[postgis-users] Getting data from table with interval

[Paul Ramsey]

Yep, I cut'n'pasted the wrong example.


EXPLAIN ANALYZE
SELECT *, 
  latlng <-> '0101000020E6100000E17A14AE47412C40BC74931804A64940' AS dist 
FROM elevation2x2 
ORDER BY dist LIMIT 5;


> On Jan 7, 2022, at 12:19 PM, Luca Bertoncello <lucabert at lucabert.de> wrote:
> 
> 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)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users