From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: Dav Coleman <dav@danger-island.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Query Optimization
Date: Thu, 18 Apr 2002 12:15:43 -0400
Message-ID: <12872.1019146543@sss.pgh.pa.us> (raw)
In-Reply-To: <web-1375952@davinci.ethosmedia.com>
References: <web-1375952@davinci.ethosmedia.com>
"Josh Berkus" <josh@agliodbs.com> writes:
>> ( "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.
Not at all. Postgres understands very well that it's allowed to
rearrange AND'ed clauses. Using current sources (so that you can
see the index condition in EXPLAIN):
regression=# create table foo (f1 int, f2 int, unique(f1,f2));
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'foo_f1_key' for table 'foo'
CREATE
regression=# explain select * from foo where f1 = 1 and f2 = 42;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using foo_f1_key on foo (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((f1 = 1) AND (f2 = 42))
(2 rows)
regression=# explain select * from foo where f2 = 42 and f1 = 1;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using foo_f1_key on foo (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((f1 = 1) AND (f2 = 42))
(2 rows)
I was curious about the details of Dav's query because it wasn't obvious
why he'd be getting a different result. Perhaps the two query plans are
mistakenly estimated to have exactly the same cost? (Although WHERE
clause order doesn't affect the set of plans considered, it can affect
the order in which they're considered, which might result in a different
choice between two plans that are estimated to have identical costs.)
Another possibility: perhaps neither condition is indexable, but cond1
is vastly more expensive to compute than cond2? (Maybe it's a
sub-SELECT.) Right now I don't believe there's any code in there that
will rearrange AND-clause order strictly on the basis of
cost-to-compute-the-clauses-themselves.
regards, tom lane
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: tgl@sss.pgh.pa.us, josh@agliodbs.com, dav@danger-island.com
Subject: Re: SQL Query Optimization
In-Reply-To: <12872.1019146543@sss.pgh.pa.us>
* 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