Thread: index on user defined type

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

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?

            regards, tom lane

On Wed, Jan 12, 2005 at 04:43:13PM -0500, Tom Lane wrote:
> Jeff Davis <jdavis-pgsql@empires.org> writes:
> >
> > => 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 started looking at this in 8.0.0rc5 and got the same thing:

EXPLAIN ANALYZE SELECT * FROM test WHERE t = '(2)';
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=100000000.00..100000001.04 rows=1 width=32) (actual time=0.078..0.088 rows=1 loops=1)
   Filter: ((t).i = ('(2)'::type2).i)
 Total runtime: 0.203 ms
(3 rows)

If I add another column to the type I get this:

EXPLAIN ANALYZE SELECT * FROM test WHERE t = '(2,3)';
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=100000000.00..100000001.05 rows=1 width=36) (actual time=0.093..0.107 rows=1 loops=1)
   Filter: (((t).i = ('(2,3)'::type2).i) AND ((t).j = ('(2,3)'::type2).j))
 Total runtime: 0.226 ms
(3 rows)

Table columns having a composite type are new in 8.0, right?  Has
indexing a composite type been done before?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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)

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I'm wondering if the function under = is an SQL function being inlined.

Bingo --- that's surely it.  After inlining, the expression would no
longer look like it matched the index.

You don't want to use SQL functions to define indexable operators
anyway.  They leak memory, and are slow, and neither of those are
good properties for an index support function.

            regards, tom lane

On Wed, 2005-01-12 at 18:12 -0500, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > I'm wondering if the function under = is an SQL function being inlined.
>
> Bingo --- that's surely it.  After inlining, the expression would no
> longer look like it matched the index.
>
> You don't want to use SQL functions to define indexable operators
> anyway.  They leak memory, and are slow, and neither of those are
> good properties for an index support function.
>
>             regards, tom lane

Thanks very much guys. I got confused and tried to simplify it to a
simple test case and confused myself more when it still didn't work.
Just to be sure I redid it in plpgsql with no problems.

I understand that index operations should be written in C for production
use, or at least tested in C to see if it helps matters. Comparison
operators are of course very simple to write in any language anyway.

I attached a proposed documentation patch. I wasn't able to readily see
the implications of writing a function in SQL regarding an index, so
perhaps this will help someone in the future (not that many people will
attempt writing index access methods in SQL, but someone could get
confused like I did).

Regards,
    Jeff Davis

Jeff Davis <jdavis-pgsql@empires.org> writes:
> I attached a proposed documentation patch. I wasn't able to readily see
> the implications of writing a function in SQL regarding an index, so
> perhaps this will help someone in the future (not that many people will
> attempt writing index access methods in SQL, but someone could get
> confused like I did).

Actually the inlining issue is only a problem for the operators, not the
support functions (since the support functions don't appear in queries
that are going to use the index).  I applied a modified version of your
patch.

            regards, tom lane