GiST indexing question

From: Greg Landrum <greg(dot)landrum(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: GiST indexing question
Date: 2010-12-11 05:09:01
Message-ID: AANLkTi=XX=CV+RVXR6+DBA_LP=AaY7RnDe_yxPZMCKfT@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message c k 2010-12-11 09:51:04 Re: Asynchronous query execution
Previous Message jan 2010-12-10 23:20:41 pg_dump order of rows