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