Re: how to optimize my c-extension functions

"TJ O'Donnell" <tjo(at)acm(dot)org> writes:
> The only type of search will be of the type:

> Select smiles,id from structure where oe_matches(smiles,'c1ccccc1C(=O)N');

You haven't really said much about how you expect an index to be able to
help you with this, but I think if any index type can help you it will
be GiST. What you would do is define an operator on top of the
oe_matches function, so that the above query is written say

Select smiles,id from structure where smiles ~~ 'c1ccccc1C(=O)N';

and then construct a GiST operator class that accepts ~~ as an
indexable operator. There's not a huge amount of
plain-old-documentation about GiST but there are quite a few examples
available in the contrib/ tree.

I don't think you can completely hide the existence of the parsed
version of the smiles data. The easiest way to go at it would be to
write the queries like

Select smiles,id from structure where smiles_parsed ~~ 'c1ccccc1C(=O)N';

where smiles_parsed is the extra column holding the parsed data, and
the ~~ operator is grabbed by a GiST index over that column.

Plan B would be to construct the index as a functional index and write

Select smiles,id from structure where parsed(smiles) ~~ 'c1ccccc1C(=O)N';

However plan B doesn't readily support applying any other operations to
the parsed data, since it doesn't exist anywhere except inside the
index. Since you mentioned having other things you wanted to do with it,
I think you'll end up wanting the separate column.

regards, tom lane