Re: Operator Classes and ANALYZE

On Mon, Nov 06, 2006 at 08:53:28PM +0000, Simon Riggs wrote:
> On Fri, 2006-11-03 at 15:16 -0700, Rusty Conover wrote:
>
> > It doesn't appear that ANALYZE uses the specified operator class for
> > producing statistics on an index when that operator class is not the
> > default for the data type.  This appears to be leading to poor query
> > planning.
>
> > For speed of indexing a few million urls I'm indexing them with a
> > custom data type. The data type aptly named "urlhash" has:
>
> Have you read the CREATE TYPE man page, specifically with regard to the
> analyze_function clause? Basically, if you want anything different, you
> have to write an analysis function yourself. This is what PostGIS
> (www.postgis.org) does, if you want to look for specific code examples.
>
> ANALYZE collects stats for tables, not indexes, using the default
> operator class for the datatype. So even though you've clearly specified
> an opclass for the index, no stats will be collected using it.
>
> Alternatively, perhaps you have fallen foul of this situation?
>
> /*
>  * Can't analyze if the opclass uses a storage type
>  * different from the expression result type. We'd get
>  * confused because the type shown in pg_attribute for
>  * the index column doesn't match what we are getting
>  * from the expression. Perhaps this can be fixed
>  * someday, but for now, punt.
>  */
>
> It's in the analyze.c code, but not in the docs.

Should a doc patch be in the offing here?

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!