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!
