pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (6+ messages)  latest in thread

Message-ID: <web-1375952@davinci.ethosmedia.com>
Permalink:  ../web-1375952@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-1375952@davinci.ethosmedia.com

 · 

reply

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