Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 9B683475452 for ; Thu, 18 Apr 2002 11:43:49 -0400 (EDT) Received: from [63.195.55.98] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1375952; Thu, 18 Apr 2002 08:43:53 -0700 From: "Josh Berkus" Subject: Re: SQL Query Optimization To: Dav Coleman , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Thu, 18 Apr 2002 08:43:53 -0700 Message-ID: In-Reply-To: <20020417223051.C24932@danger-island.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200204/226 X-Sequence-Number: 7490 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