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

[Stephen Woodbridge]

Hi Regina,

On 11/18/2016 3:28 AM, Regina Obe wrote:
> 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)

Did this change recently? I was under the impression that st_dwithin was
faster than st_intersects? Anyway, this is good to know, Thanks!

> 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.

The polygon has 33,747 points. It is in SRID:4326

select st_npoints(ST_SimplifyPreserveTopology(geom, 0.001)) from 
exclusion_areas;   --  2528
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0007)) from 
exclusion_areas;  --  3226
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0005)) from 
exclusion_areas;  --  4040
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0002)) from 
exclusion_areas;  --  7029
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0001)) from 
exclusion_areas;  -- 10424

I can probably live with 0.0007 * 111120.0 = 77.784 m tolerance, which 
reduces the npoint of the polygon by an order of magnitude.

Thanks for the suggestions.

-Steve

> -----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
>
> _______________________________________________ postgis-users mailing
> list postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus