[postgis-users] Is my query planner failing me, or vice versa?

[strk at refractions.net]

On Tue, Dec 13, 2005 at 03:58:12PM -0700, Jessica M Salmon wrote:
> strk,
> 
>                                          postgis_full_version
> ------------------------------------------------------------------------------------------------------
>  POSTGIS="1.0.2" GEOS="2.1.3" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
> DBPROC="0.3.0" RELPROC="0.3.0"
> 
> GeomUnion is a major component of the trigger function, but it only is used
> when the query has a result (fires grow together). Such is not the case for
> any of the explain analyze output shown in the chart. I do have some
> massively complex geometries, indeed, but the geometry complexity itself
> does not appear to account for the occasionally much-slower-than-expected
> queries. Would the filter on the Distance function have the same
> unpredictability (in terms of speed) as GeomUnion?

Distance's worst case performance is a factor of the square of number
of points in input: O(n^2)
Best case is really quick, but depends on luck (wheter the first
first vertexe of a polygon falls inside the other).

Intersects (implemented trhough GEOS) would be more stable, but practically
often just much slower. Plans to optimize it are in the air, but would require
a fair amount of work.

--strk;