GiST indexing question

Поиск
Список
Период
Сортировка
От Greg Landrum
Тема GiST indexing question
Дата
Msg-id AANLkTi=XX=CV+RVXR6+DBA_LP=AaY7RnDe_yxPZMCKfT@mail.gmail.com
обсуждение исходный текст
Ответы Re: GiST indexing question  (Yeb Havinga <yebhavinga@gmail.com>)
Список pgsql-general
Hi,

I'm attempting to expand an existing postgresql extension and I've run
into a wall with the way operator classes should be defined for GiST
indices.

What I have that works is the following two operators:
CREATE OPERATOR <@ (
    LEFTARG = mol,
    RIGHTARG = mol,
    PROCEDURE = rsubstruct(mol, mol),
    COMMUTATOR = '@>',
    RESTRICT = contsel,
    JOIN = contjoinsel
);
CREATE OPERATOR @> (
    LEFTARG = mol,
    RIGHTARG = mol,
    PROCEDURE = substruct(mol, mol),
    COMMUTATOR = '<@',
    RESTRICT = contsel,
    JOIN = contjoinsel
);

combined into an operator class for use in indexing :

CREATE OPERATOR CLASS mol_ops
DEFAULT FOR TYPE mol USING gist
AS
    OPERATOR    3    @> (mol, mol),
    OPERATOR    4    <@ (mol, mol),
    FUNCTION    1   gmol_consistent (bytea, internal, int4),
    FUNCTION    2   gmol_union (bytea, internal),
    FUNCTION    3   gmol_compress (internal),
    FUNCTION    4   gmol_decompress (internal),
    FUNCTION    5   gmol_penalty (internal, internal, internal),
    FUNCTION    6   gmol_picksplit (internal, internal),
    FUNCTION    7   gmol_same (bytea, bytea, internal),
STORAGE         bytea;

I'm now trying to add an equality operator (==) as follows:

CREATE OPERATOR == (
    LEFTARG = mol,
    RIGHTARG = mol,
    PROCEDURE = mol_eq,
    COMMUTATOR = '=',
    NEGATOR = '<>',
    RESTRICT = eqsel,
    JOIN = eqjoinsel
);

and I want to use this to extend the operator class:

CREATE OPERATOR CLASS mol_ops
DEFAULT FOR TYPE mol USING gist
AS
    OPERATOR    3    @> (mol, mol),
    OPERATOR    4    <@ (mol, mol),
    OPERATOR    6    == (mol, mol),
    FUNCTION    1   gmol_consistent (bytea, internal, int4),
    FUNCTION    2   gmol_union (bytea, internal),
    FUNCTION    3   gmol_compress (internal),
    FUNCTION    4   gmol_decompress (internal),
    FUNCTION    5   gmol_penalty (internal, internal, internal),
    FUNCTION    6   gmol_picksplit (internal, internal),
    FUNCTION    7   gmol_same (bytea, bytea, internal),
STORAGE         bytea;

I made something of a guess as to which strategy I should use.

I can now do basic tests with my == operator:
moltest=# select 'c1nnccc1'::mol=='c1ccnnc1'::mol;
 ?column?
----------
 t
(1 row)

moltest=# select 'c1nnccc1'::mol=='c1cnnnc1'::mol;
 ?column?
----------
 f
(1 row)

(yes, those are both correct).

I can create an index using these definitions:
moltest=# create index molidx on mols using gist(m);
CREATE INDEX


And as far as I can tell it looks like the index should be working:

moltest=# explain select count(*) from mols where m=='c1ncnnc1';
                               QUERY PLAN
-------------------------------------------------------------------------
 Aggregate  (cost=8.27..8.28 rows=1 width=0)
   ->  Index Scan using molidx on mols  (cost=0.00..8.27 rows=1 width=0)
         Index Cond: (m == 'c1cnncn1'::mol)
(3 rows)

But when I actually run a query I get an error:

moltest=# select count(*) from mols where m=='c1ncnnc1';
ERROR:  Unknown strategy: 6

The index isn't totally screwed up though, because an @> query still works:

moltest=# select count(*) from mols where m@>'c1ncnnc1';
 count
-------
     5
(1 row)

I guess I must be doing something stupid, but neither the docs nor the
internet have been particularly helpful in letting me know what.

These tests are all being done using postgresql 9.0.1 built on an
ubuntu 10.10 box.

In case it's helpful, the full extension code (without the
non-functioning attempts at adding == to the index) is here:
http://rdkit.svn.sourceforge.net/viewvc/rdkit/trunk/Code/PgSQL/rdkit/

Thanks in advance for any help,
-greg

В списке pgsql-general по дате отправления:

Предыдущее
От: jan
Дата:
Сообщение: pg_dump order of rows
Следующее
От: c k
Дата:
Сообщение: Re: Asynchronous query execution