Re: query plan different for "SELECT ..." and "DECLARE

Tom Lane wrote:

> You may need to bite the bullet and try to devise some real selectivity
> estimation techniques for your geometric operators. The stuff in
> src/backend/utils/adt/geo_selfuncs.c at the moment is all just stubs :-(

I've already done this - it actually gives pretty accurate estimates.
Basically, I do a two-stage pass through the geometry table. The first
pass just calculates the bounding box of all the geometries. I make a
2D histogram structure (much like a really really simple quad tree) that
fits this bounding box. The 2nd pass looks at each individual geometry
in the table - it updates one (or more) cells in the 2D histogram.

When the planner asks for the selectivity of a '&&' query, I pull in the
2D Histogram (its in the geometry_column metadata table) and look at the
query's 'query bounding box' and see which cells of the 2D histogram it
overlaps. I make an estimate of the number of rows the query will
return by looking at the % overlap of the query window and the number of
geometries in that 2D histogram cells. You'd think such a crude test
wouldnt give you good results, but in fact its amazingly accurate.

If for some reason I cannot calculate an estimate, I fall back to the
geo_selfuncs.c method and return some really small number.

The PostGIS mailing list (postgis.refractions.net) has more technical
details.

The only real problem is that the user has to manual keep stats
up-to-date. Is there anyway to attach something to VACUUM ANALYSE?

As an aside, PostGIS is now passing the Open GIS Consortium's "Simple
Features For SQL" conformance test. We're hoping to submit it for the
OGC stamp of approval "very soon."

Thanks for the info on start-up cost being more heavily weighted. The
user who reported this problem didnt have the index-selectivity-stats
package turned on in their database. Once they turned it on, everything
worked correctly. I was just confused as to why the DECLARE and SELECT
were making different plans.

dave