Skip site navigation (1) Skip section navigation (2)

Re: Operator Classes and ANALYZE

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Rusty Conover" <rconover(at)infogears(dot)com>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Operator Classes and ANALYZE
Date: 2006-11-06 20:53:28
Message-ID: 1162846409.30200.135.camel@silverbirch.site (view raw or flat)
Thread:
Lists: pgsql-bugs
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



In response to

Responses

pgsql-bugs by date

Next:From: Rusty ConoverDate: 2006-11-06 21:47:55
Subject: Re: Operator Classes and ANALYZE
Previous:From: Mason HaleDate: 2006-11-06 19:57:01
Subject: Re: BUG #2739: INTERSECT ALL not working

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group