Re: index on user defined type

On Wed, 12 Jan 2005, Tom Lane wrote:

> Jeff Davis <jdavis-pgsql(at)empires(dot)org> writes:
> > 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.
>
> > => 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)
>
> The explain doesn't seem to quite match up with what you wrote in the
> command. How did those ".i" qualifiers get in there?

I'm wondering if the function under = is an SQL function being inlined.
When I did a similar test, I got

sszabo=# create type a as (a int, b int);
CREATE TYPE
sszabo=# create table q (a a);
CREATE TABLE
sszabo=# create function feq(a, a) returns boolean as 'select $1.a = $2.a
and $1.b = $2.b;' language 'sql';
CREATE FUNCTION
sszabo=# create operator = (leftarg=a, rightarg=a, procedure=feq);
CREATE OPERATOR
sszabo=# explain select * from q where a = '(1,2)'::a;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on q (cost=0.00..0.00 rows=1 width=32)
Filter: (((a).a = ('(1,2)'::a).a) AND ((a).b = ('(1,2)'::a).b))
(2 rows)

sszabo=# drop operator=(a,a);
DROP OPERATOR
sszabo=# create function feq2(a, a) returns boolean as 'begin return $1.a
= $2.a and $1.b = $2.b; end;' language 'plpgsql';
CREATE FUNCTION
sszabo=# create operator = (leftarg=a, rightarg=a, procedure=feq2);
CREATE OPERATOR
sszabo=# explain select * from q where a = '(1,2)'::a;
QUERY PLAN
--------------------------------------------------
Seq Scan on q (cost=0.00..0.00 rows=1 width=32)
Filter: (a = '(1,2)'::a)
(2 rows)