Re: index on user defined type

On Wed, 12 Jan 2005, Tom Lane wrote:

> Jeff Davis <jdavis-pgsql@empires.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)