[postgis-users] Question on performance probably related to detoast and/or bboxes

[Regina Obe]

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:postgis-users-bounces at lists.osgeo.org] On Behalf Of Stephen Woodbridge
Sent: Thursday, November 17, 2016 4:15 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
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
postgis-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users