Since you are doing 0.0, you might be better off using ST_Intersects So
st_dwithin(pnt, a.geom, 0.0) change to: ST_Intersects(pnt,a.geom) Or did you try that already? How many points does ST_NPoints return for your exclusion area? If a lot, then detoasting might be an issue and then you might want to consider simplifying your exclusion areas just a little. -----Original Message----- From: postgis-users [mailto:[email protected]] On Behalf Of Stephen Woodbridge Sent: Thursday, November 17, 2016 4:15 PM To: PostGIS Users Discussion <[email protected]> Subject: [postgis-users] Question on performance probably related to detoast and/or bboxes Hi all, I have an application that does a spatial search based on an expanding radius where I double the radius each time it fails to find any results. This is problematic if you are outside the the extents of the data being search because it should fail. Think of the US data but the point is in Mexico. So I created a polygon covering Mexico and overlapping the US border, and differenced out the state polygons to create an exclusion area polygon. This works fine and as expected. Now the performance question. I integrated this into my stored procedure with: if exists(SELECT * FROM pg_catalog.pg_tables WHERE tablename='exclusion_areas') then select into excl id from exclusion_areas a where st_dwithin(pnt, a.geom, 0.0) limit 1; if FOUND then return NULL; end if; end if; This added a significant amount of CPU load to the server. I modified the code to reduce the server load with: if st_y(pnt) < 33.0 then if exists(SELECT * FROM pg_catalog.pg_tables WHERE tablename='exclusion_areas') then select into excl id from exclusion_areas a where st_dwithin(pnt, a.geom, 0.0) limit 1; if FOUND then return NULL; end if; end if; end if 99% of the queries hitting the server are in the US So what is causing the additional CPU load in the first query? I'm speculating that it is related to detoasting the exclusion_areas.geom (there is only one record in the table) to get the bbox, or may it has to compute the bbox each time. Or maybe something else? Next question, is there a way to fail quickly if the point is not within the area represented by the data, in this case the road network for the US and Canada. I can't use a BBOX test because the BBOX of Mexico overlaps the BBOX of the US. I'm happy with the way it is currently working but want to better understand what is happening in this case. Thoughts? Thanks, Steve W --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
