Re: rtree indexes aren't being used with 7.0 - Mailing list pgsql-general

From Jeff Hoffmann
Subject Re: rtree indexes aren't being used with 7.0
Date
Msg-id 39205239.1B5D5561@propertykey.com
Whole thread Raw
In response to Re: rtree indexes aren't being used with 7.0  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tom Lane wrote:

> Right now the "index-specific estimator" is no such thing; all the index
> types share a single default routine.  If you know more about rtree
> access patterns than that code does, feel free to suggest improvements.
>

well, i probably do, but not a whole lot more.  like i said in my other
message, rtrees are really bulky.  is there an easy way of figuring out
how many pages are actually touched in an index during a query? or even
things like the values that go into adding up the cost (like, for
example, would the numIndexPages value totally overwhelm the rest of the
equation)?  it'd be nice to have some empirical data to see if the
estimates are actually working.

> But the real problem is of course the selectivity routine; for the
> geometric operators we've never had anything but stub routines that
> return a constant.  You might want to look at the scalar-comparison
> selectivity routines (selfuncs.c) to see how they work.  Basically
> the idea for those is that VACUUM ANALYZE stores the min and max values
> of each column, and given an operator like "var < constant" we can do
> a linear interpolation between the min and max to estimate the
> selectivity.  For example, if min=0, max=100, and the constant is 40
> then we'd estimate 0.40 selectivity.  (This works great as long as the
> data is reasonably uniformly distributed between the min and max, but
> falls down badly if it's not.  One of the items on our very long TODO
> list is to change VACUUM ANALYZE to store more extensive statistics so
> that we can be a little smarter...)
>

again, i'm making some assumptions here.  i should probably be reading
instead of asking questions, but it's nice to get some verification that
i actually understand what's going on.  my understanding is that vacuum
stuffs the statistics in pg_statistic and that staloval and stahival are
text representations of the lo & high values (which then can be
converted to a numerical value if need be).  so essentially you can
stuff whatever you want in there.  is the stacommonval the median?  is
there some place where all of the system tables are described?
theoretically, then you should be able to use vacuum to put an upper
left corner in staloval, for example, and the lower right in stahival.
i guess it's off to trying to figure out where the vacuum code is...

jeff

pgsql-general by date:

Previous
From: Jeff Hoffmann
Date:
Subject: Re: rtree indexes aren't being used with 7.0
Next
From: Tom Lane
Date:
Subject: Re: rtree indexes aren't being used with 7.0