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
