Re: SQL Query Optimization

"Josh Berkus" <josh(at)agliodbs(dot)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