Re: Operator Classes and ANALYZE

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.

Be interested in a full report of your research, once you're done.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com