Re: SQL Query Optimization

Dav,

> 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).

In most cases, the above kind of optimization difference is due to how
you indexed the table. If, for example, you have an index on (field2,
field1), and you do a "WHERE field1 = y and field2 = x" then the query
parser probably won't use the index because the field order is
different.

Fortunately, in Postgres 7.2, you now get index usage statistics.
Hopefully another user will follow-up this e-mail by explaining how to
access them.

The idea is that, if you find that certain views and queries are very
slow, then check what tables they all have in common. Then check the
indexes and statistics for each table. If you see a large table with
only 3 indexes, none of which are getting much use, then they are
pobpably the wrong indexes or you need to change the structure of your
WHERE clause. Also, EXPLAIN can be a big help here.

See http://techdocs.postgresql.org for more stuff about optimization.

I can understand that you'd like a tool to make all this easier for
you, but I haven't seen any such thing, unless it ships with the
Enterprise version of Oracle.

-Josh Berkus