From: Josh Berkus <josh@agliodbs.com>
To: Dav Coleman <dav@danger-island.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL Query Optimization
Date: Thu, 18 Apr 2002 08:43:53 -0700
Message-ID: <web-1375952@davinci.ethosmedia.com> (raw)
In-Reply-To: <20020417223051.C24932@danger-island.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, dav@danger-island.com
Subject: Re: SQL Query Optimization
In-Reply-To: <web-1375952@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox