Let me first say that I will routinely be dealing with one million+ rows, so I want to take care to optimize my work as much as possible, and to consider carefully my design decisions.
The only type of search will be of the type:
Select smiles,id from structure where oe_matches(smiles,'c1ccccc1C(=O)N');
or joins with other tables e.g.
Select id,smiles,clogp from structure,properties where oe_matches(smiles,'c1ccccc1C(=O)N') and id = prop_id; with id being a sequence (hence unique) and foreign key prop_id column of properties.
There may be other useful functions of smiles, e.g. int oe_count_matches(smiles,'CCC'), and these would also prefer to use the pre-parsed smiles c++ object.
After I parse the smiles,
the character string smiles is really of no use anymore.
It might be output, for example to an external program such as smiles_to_jpeg which re-parses the smiles and need not be fast.
So, there is no real use for indexing smiles. So I want to
borrow the internal tables normally used for indexing to store
my parsed smiles and use the parsed smiles in oe_matches and other
functions.
If I do this, maybe I would have to use operators (=,<,>,LIKE?)
to do the matching. A c-function is simply called with data and
would have no access to indexes, correct?
TJ
Pierre-Frédéric Caillaud wrote:
Well, first and easy thing you can do is create a column to store the parsed representation and update it via a trigger when the original, unparsed column is updated or inserted.
Is this sufficiently "hidden from the user" for you ? I know it's not really hidden, but the fact that updating is automatic could be good enough.
You could also store this column in another table and join with the main table.
What are the kinds of searches you do more often ? Can you give a few examples ?
Yes, my c function and it's sql counterpart, oe_matches(smiles)
uses two steps (1) parse smiles (2) search parsed smiles.
Parsing is expensive. The smiles has an external string representation,
which is stored in a smiles column, but only the parsed form is actually searchable.
The smiles representation is never searched in a traditional string
manner, except perhaps for a direct lookup (string equals).
LIKE has no meaning for smiles strings, similarly < or > are
meaningless.
Smiles is parsed into atom and bond representations using 3rd party c++ code/methods. I simply call their methods to parse and search. A binary string can be got from them for persistent storage in a postgres column. It can then be restored into a c++ object for searching, thus avoiding the parsing stage, except for the initial creation of a row with a smiles column.
My goal here is to optimize the search by storing the parsed smiles, YET HIDE THIS FROM THE USER. I thought I might be able to store the parsed smiles in an index for me to use while searching, even though it would not be used for indexing in the traditional manner. This would mean creating a new indexing method. I've read up on this and it seems a daunting task. Am I perverting the index method if I try to do this?
So, aside from having the user be responsible for maintaining a column of parsed smiles (and possibly other related columns which would speed up the search), is there a way I can create and maintain a table related to the table containing the smiles - and all behind the scenes so the sql user is unaware of this. My thought was that an index is like that and I might borrow some of the internal uses of indexing for my purposes.
TJ O'Donnell [EMAIL PROTECTED]
Pierre-Frédéric Caillaud wrote:
I gather your program uses two steps, let's call them :
- parse( smiles ) -> data
- search( data ) -> result
You can create a functional index on your smiles column, but I don't know if this will help you ; you can do things like CREATE INDEX ... ON mytable( lower( myfield )), then SELECT ... FROM mytable WHERE lower(myfield) = something, BUT in your case I gather your search function which processes the parsed data does a lot more than just dome character match, so creating a functional index on parse(smile) would be useless for selecting on search(parse(smile))...
So, in any case, if the parsing phase is slow, you can store the preparsed data in a text or binary field and search it directly, but this will not be indexed.
If you can map a subset of your searchable properties to simple datatypes, you could do a first search for related matches, as you said.
You say nothing about how your system works internally, whta kind of representation is it and what kind of searches do you actually do ?
On Sat, 08 Jan 2005 15:50:06 -0800, TJ O'Donnell <[EMAIL PROTECTED]> wrote:
I've written a c-language extension to postgresql to implement a chemical search of a varchar column (named smiles, typically).
It might be called as:
oe_matches(smiles,'COCC') where 'COCC' is a typical search string.
This uses 3rd party functions to parse the varchar inputs into c++ objects. In order to optimize this search, I'd like to parse the whole table containing smiles just once, store it and use the parsed data instead of the varchar smiles.
So, I could create another column, say smiles_ob and have the
oe_matches function take that column. I'd have to be sure the smiles_ob column was related (by a trigger?) to the smiles column.
But I was thinking I might be able to hide these parsed objects from the user by somehow incoporating the parsed objects into a type of index.
I'd like also to use additional columns (like molecular formula) in
the match function to "triage" the table to quickly rule out impossible
matches, before doing a full search/match.
Am I way off the track here? Is it a bad idea to clutter the index with things like this? Is it possible? Is there another approach that could hide some of these details from the user - meaning they would not have to create/update these additional columns?
Thanks, TJ O'Donnell
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
