index on user defined type

From: Jeff Davis <jdavis-pgsql(at)empires(dot)org>
To: PgSQL General List <pgsql-general(at)postgresql(dot)org>
Subject: index on user defined type
Date: 2005-01-12 20:15:58
Message-ID: 1105560958.2886.323.camel@jeff
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I think I created a type that was compatible with the btree index, and
everything seems fine, except that it doesn't actually use the index. I
created the operators and the opclass as well.

=> create type type2 as (i int);
=> create operator = (leftarg=type2,rightarg=type2,procedure=type2_eq);
=> create operator < (leftarg=type2,rightarg=type2,procedure=type2_lt);
=> create operator <=(leftarg=type2,rightarg=type2,procedure=type2_lte);
=> create operator >=(leftarg=type2,rightarg=type2,procedure=type2_gte);
=> create operator > (leftarg=type2,rightarg=type2,procedure=type2_gt);
=> create operator class type2_opclass default for type type2 using
btree as operator 1 <, operator 2 <=, operator 3 =, operator 4 >=,
operator 5 >, function 1 type2_cmp(type2,type2);
=> create table test(t type2 unique);
... insert '(1)', '(2)', and '(3)';
... enable_seqscan is off
=> explain select * from test where t = '(2)';
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on test (cost=100000000.00..100000002.54 rows=1 width=32)
Filter: ((t).i = ('(2)'::type2).i)
(2 rows)

Is there something obvious that I'm doing wrong? The operators'
functions are pretty basic sql functions that just do the obvious thing,
and those operators seem to work correctly when used in queries. If I
make a table except with an int instead of a type2, it uses the index as
soon as I turn seqscan off.

Regards,
Jeff Davis

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ed L. 2005-01-12 20:30:46 Re: Postgresql didn't start after power failure
Previous Message Clodoaldo Pinto 2005-01-12 20:08:26 Postgresql didn't start after power failure