[postgis-users] Query crashed

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Paul Ramsey
> Sent: 20 November 2005 05:40
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Query crashed
> 
> We'll wait until Mark gets back on the list and comments, probably a
> problem with the function(geometry) && geometry construction and the
> index selectivity calculation.  Mark was working on spatial join
> selectivity a couple months ago, but this might be a case he did not
> anticipate.
> 
> P


Hi Paul,

You're exactly right in that the issue is because the query uses F(geom)
rather than just geom. The NOTICE that is emitted in this case is just to
point out that when we asked for the cost of the F(geom), it didn't match
any constants and so we return the default cost which is extremely low and
looks very favourable to the planner. This wouldn't cause the query to crash
though, so as confirmed by Steve's latest mail, it is more than likely to be
a GEOS issue.

The reason we do this is because F(geom), where F() is any arbitrary
function, could transform() the geometry in such a way so that it bears no
relation to the original version in the column and hence we could only
return the default.

The only potential speedup I can think of is for the join selectivity code
to make a note of which functions return only linear transformations to the
bounding box such as expand() - we could then evaluate the function and
apply its bounding box result against the stored column histograms instead
of simply returning the default.

However, this would need quite a bit of time doing investigative work so I'd
want to check the Steve's queries would actually benefit from this before
jumping into any coding. The quick way to check this would be to post back
the EXPLAIN ANALYZE results of the following queries:


Steve's working query using just the spatial indices:

select count(*) from roadseg r, streets s where r.the_geom && s.the_geom;


Steve's working query:

select count(*) from roadseg r, streets s where expand(r.the_geom,
0.00007) && s.the_geom and
  within(s.the_geom, buffer(r.the_geom, 0.00007));


Steve's working query without expand()

select count(*) from roadseg r, streets s where r.the_geom
&& s.the_geom and
  within(s.the_geom, buffer(r.the_geom, 0.00007));


This will tell us how accurate the cost estimates are which are used to plan
the query, and also where the majority of the time is being spent during
query processing.


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.