[postgis-devel] Query causes Postgres to crash

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at postgis.refractions.net] On 
> Behalf Of strk at refractions.net
> Sent: 09 December 2004 18:53
> To: PostGIS Development Discussion
> Subject: Re: [postgis-devel] Query causes Postgres to crash

(cut)

> > UPDATE grid10m SET
> > lzmin = (SELECT min(lz) from unfilteredpts WHERE lreturn  &&
> > the_geom), lzmax  = (SELECT max(lz) from unfilteredpts 
> WHERE lreturn
> > && the_geom), lzavg  = (SELECT avg(lz) from unfilteredpts WHERE
> > lreturn  && the_geom), lzstddev  = (SELECT stddev(lz) from 
> > unfilteredpts WHERE lreturn  && the_geom);
> > 
> > grid10m is a set of 11,000 square polygons.  unfilteredpts
> is as set
> > of
> > 2.5 million points (2 geometry columns lreturn, freturn).
> > 
> > I am trying an explain analyze on a subset (1,700 grid
> polys & 400,000
> > pts), but it has been 2 hours and I still do not have a result.
> > 
> > Any thoughts? Thanks,
> 
> I'd concentrate to explain analyze not returning.
> Define DEBUG_GEOMETRY_STATS in lwgeom/lwgeom_estimate.c
> and see what goes on.
> --strk;


Hi strk,

Unfortunately PostGIS doesn't currently return estimates for joins of the
type <column> && <column>, only <column> && <constant>. This has been on my
TODO list for a while since it is likely that we would use this type of
query on large tables containing millions of records on our geospatial
database - unfortunately I have been needed to work on other non-spatial
projects for some time which is why I've been rather quiet here :)

My guess is that this part of the query is performing a sequential scan:

	SELECT min(lz) from unfilteredpts WHERE lreturn && the_geom

... And unfortunately in the query above it does this for each row in the
grid10m table. At the moment the join selectivity function, positionjoinsel,
is hard-wired in src/backend/utils/adt/geo_selfuncs.c to be 0.1(!) and this
is the join function specified in the operator definition for &&.

What we need to do is define our own JOIN clause estimation function in the
declaration of the && operator instead. This should use the geometry
statistics to calculate the intersection area of the two columns and return
an estimate as to how many geometries are returned within that area.
Unfortunately, unless anyone else beats me to this, I'm won't be able to get
around to this for a while :(


Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk