Jeff Hoffmann <jeff@propertykey.com> writes:
> rtree indexes are really bulky -- in fact, i have some index
> files that are larger than the database file in a lot of cases, which
> would almost immediately make a sequential scan interesting. granted,
> this is a funny case where there's only three attributes, but it
> happens. it might be useful to just modify the genericcostestimate
> function and slash the indexPages into some fraction (e.g., an rtree
> index on a box attribute is greater than 5x the size of a btree index on
> an integer attribute, so maybe just cut the indexPages by 5 or 10 or
> something like that.) it's no substitute for a decent selectivity
> function, but it might help.
Mmm ... the existing estimator essentially assumes that if you are
executing a query that ultimately returns X% of the tuples, you are
going to have to touch about X% of the pages in the index to do it.
While I'm prepared to be shown that that's not true for rtrees,
it seems like a pretty plausible first-order estimate.
I don't think that making the rtree estimator less realistic is an
appropriate solution to problems in the operator estimator, anyway.
If the estimator gives bad results given an accurate selectivity,
then we should change it; but if the problem is upstream then we
should work on the upstream.
It could even be that the notion of representing selectivity as a
single number is inadequate for rtrees, and that more info needs to
be passed back from the operator-specific routine (selectivity in
two dimensions, say). But considering that we haven't even tried
to use the existing structure, discarding it is probably premature...
regards, tom lane