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 <[email protected]> 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 > ([email protected]) > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
