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

[Stephen Woodbridge]

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