Hello,
I am using postgresql to house chemical informatics data which consists of
several interlinked tables with tens of thousands (maximum) of rows. When
doing search queries against these tables (which always requires multiple
joins) I have noticed that the semantically equivalent SQL queries can differ
vastly in speed performance depending on the order of clauses ANDed
together ( "WHERE cond1 AND cond2" takes forever, but "WHERE cond2
AND cond1" comes right back).
So it appears I need to do some pre-optimization of the SQL query generated
by the user before submitting it to postgresql in order to guarantee (or
at least increase the likelihood of) the fastest results. I've tried STFW and
RTFM but haven't found any good pointers on where to start with this,
although I feel that there must be some published algorithms or theories.
Can anyone point me to a URL or other source to get me on my way?
Also, I wonder if this sort of query optimization is built into other
databases such as Oracle?
I did find this URL: http://redbook.cs.berkeley.edu/lec7.html
which seems to be interesting, but honestly I'm far from a DB expert so I
can't follow most of it, and I can't tell if it is talking about optimization
that can be done in application space (query rewrite) or something that has
to be done in the database engine itself. I'm going to try to find the book
it references though.
Basically I feel a bit in over my head, which is ok but I don't want to
waste time paddling in the wrong direction, so I'm hoping someone can
recognize where I need to look and nudge me in that direction. Maybe I
just need proper terminology to plug into google.
Thanks,
Dav
--
Dav Coleman
http://www.danger-island.com/dav/