On Jan 5, 2017, at 8:50 AM, Paul Ramsey <pramsey@xxxxxxxxxxxxxxxxx> wrote:
Ok, I think I understand the concept. So attempting to follow your advice, I modified the query to be: SELECT elevation FROM data WHERE ST_DWithin( location, (SELECT ST_MakeLine(geom)::geography as split_line FROM (SELECT (ST_DumpPoints( ST_Segmentize( ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'), 600 )::geometry )).geom ) s1), 600 ) ORDER BY elevation DESC limit 1; It took some fiddling to find a syntax that Postgresql would accept, but eventually that's what I came up with. Unfortunately, far from improving performance, it killed it - in running the query, it went from 22 seconds to several minutes (EXPLAIn ANALYZE has yet to return a result). Looking at the query execution plan shows, at least partially, why: QUERY PLAN ------------------------------------------------------------------------------ Limit (cost=17119748.98..17119748.98 rows=1 width=4) InitPlan 1 (returns $0) -> Aggregate (cost=17.76..17.77 rows=1 width=32) -> Result (cost=0.00..5.25 rows=1000 width=32) -> Sort (cost=17119731.21..17171983.43 rows=20900890 width=4) Sort Key: data.elevation DESC -> Seq Scan on data (cost=0.00..17015226.76 rows=20900890 width=4) Filter: st_dwithin(location, $0, '600'::double precision) (8 rows) So apparently it is now doing a sequential scan on data rather than using the index. And, of course, sorting 20 million rows is not trivial either. Did I do something wrong with forming the query? ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 -----------------------------------------------
|