Re: [GENERAL] btree index on a char(8) field (fwd)

Frank Mandarino wrote:

> Thanks for your response.
>
> I knew from the programming documentation that the opclass was optional.
> I'm pretty sure, although I will check again tonight, that I tried
> creating the index without specifying the opclass, but I found that the
> index was still not used in my example query.
>
> Do you know which opclass that Postgres should choose for char(8) types?

Owing very much to Franks question, I looked around and made the
following discovery. I have always been puzzled why only my own types
require an opclass in CREATE INDEX. The answer is that I failed to
provide the default. I based my code on the outdated postgres schema,
which still exists in the docs:

http://www.postgresql.org/docs/programmer/extend289.htm
(I'd love to see it fixed one day!)

If I got it right, the default opclass is snow specified in pg_opclass:

SELECT DISTINCT pg_am.amname, pg_opclass.opcname, pg_type.typname
FROM pg_am, pg_amop, pg_opclass, pg_type
WHERE pg_amop.amopid = pg_am.oid
  AND pg_amop.amopclaid = pg_opclass.oid
  AND pg_opclass.opcdeftype = pg_type.oid;

amname|opcname     |typname
------+------------+--------
btree |abstime_ops |abstime
btree |bpchar_ops  |bpchar
btree |char_ops    |char
btree |date_ops    |date
btree |datetime_ops|datetime
btree |float4_ops  |float4
btree |float8_ops  |float8
btree |int2_ops    |int2
btree |int4_ops    |int4
btree |int8_ops    |int8
btree |macaddr_ops |macaddr
btree |name_ops    |name
btree |network_ops |cidr
btree |network_ops |inet
btree |oid8_ops    |oid8
btree |oid_ops     |oid
btree |text_ops    |text
btree |time_ops    |time
btree |timespan_ops|timespan
btree |varchar_ops |varchar
hash  |bpchar_ops  |bpchar
hash  |char_ops    |char
hash  |date_ops    |date
hash  |datetime_ops|datetime
hash  |float4_ops  |float4
hash  |float8_ops  |float8
hash  |int2_ops    |int2
hash  |int4_ops    |int4
hash  |int8_ops    |int8
hash  |macaddr_ops |macaddr
hash  |name_ops    |name
hash  |network_ops |cidr
hash  |network_ops |inet
hash  |oid8_ops    |oid8
hash  |oid_ops     |oid
hash  |text_ops    |text
hash  |time_ops    |time
hash  |timespan_ops|timespan
hash  |varchar_ops |varchar
rtree |bigbox_ops  |box
rtree |box_ops     |box
rtree |circle_ops  |circle
rtree |poly_ops    |polygon
(43 rows)

The way I understand it is that for each access method (amname) and
data type (typname) the default opclass is specified in
(opcname). This doesn't tell you, however, that char(n) is a bpchar
(thanks to SQL92 compliance, you can't anymore find that out by just
looking at it). Also, the above query does not list the types not
having a default opclass:

SELECT * FROM pg_opclass WHERE opcdeftype IS NULL;
opcname     |opcdeftype
------------+----------
ec_code_ops |
gist_seg_ops|

And by the way, what I have just found makes me believe that one does
not even have to mention the access method ("using"-clause) in their
CREATE INDEX.

"If nothing else helps, read the manual"

--Gene