[postgis-users] Query crashed

[Stephen Woodbridge]

Mark,

I will run the queries and report back the results. I currently takes 
about 2 hours per query. For what I need to do now, this is ok, but it 
would be nice if it is possible to improve this for the future.

Thank you for your analysis on this.

-Steve

Mark Cave-Ayland wrote:
>>-----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.
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>