Thread: faster SELECT
hello in a 'SELECT', does postgres read the 'WHERE' condition from left to right. for example 1) select ... where a and b; 2) select ... where b and a; 1 and 2 will use the same cpu time or not ? sylvain
Attachment
On Mon, 2004-03-08 at 17:41, sferriol wrote: > hello > in a 'SELECT', does postgres read the 'WHERE' condition from left to right. > > for example > 1) select ... where a and b; > 2) select ... where b and a; > > 1 and 2 will use the same cpu time or not ? > > sylvain
Sorry for my last blank post, anyway, im not sure this helps but have you tried using EXPLAIN? Dexter Tad-y On Mon, 2004-03-08 at 17:41, sferriol wrote: > hello > in a 'SELECT', does postgres read the 'WHERE' condition from left to right. > > for example > 1) select ... where a and b; > 2) select ... where b and a; > > 1 and 2 will use the same cpu time or not ? > > sylvain
On Monday 08 March 2004 09:41, sferriol wrote: > hello > in a 'SELECT', does postgres read the 'WHERE' condition from left to right. > > for example > 1) select ... where a and b; > 2) select ... where b and a; > > 1 and 2 will use the same cpu time or not ? I really wouldn't worry about it, for two reasons. 1. The difference in timing is going to be negligible (probably not measurable). 2. The developers might change their mind about evaluation order, in which case you've wasted your time optimising. -- Richard Huxton Archonet Ltd
> in a 'SELECT', does postgres read the 'WHERE' condition from left to right. PostgreSQL (SQL in general?) does NOT define evaluation order (unlike programming languages like C). > for example > 1) select ... where a and b; > 2) select ... where b and a; > > 1 and 2 will use the same cpu time or not ? Depends on the problem. In theory the optimizer should find the best evaluation order after analyzing a and b. Bye, Chris.
----- Původní zpráva ----- Od: "sferriol" <sylvain.ferriol@imag.fr> Komu: <pgsql-general@postgresql.org> Odesláno: 8. března 2004 10:41 Předmět: [GENERAL] faster SELECT > hello > in a 'SELECT', does postgres read the 'WHERE' condition from left to right. > > for example > 1) select ... where a and b; > 2) select ... where b and a; > > 1 and 2 will use the same cpu time or not ? > > sylvain First should be used the most RESTRICTIVE condition. ie if condition "a" limit output to few rows from many , it shouldbe used first in WHERE . Preferrable condition "a" should be created index aware, so relatively inexpensive indexscan can eliminate most of unwanted lines from output. Following this rule should you should be able to create a fastestand low cost query. So it can not be explicitely told if 1 and 2 will use the sme cpu time in real life
On Mar 8, 2004, at 5:40 AM, Chris wrote: > >> in a 'SELECT', does postgres read the 'WHERE' condition from left to >> right. > > PostgreSQL (SQL in general?) does NOT define evaluation order (unlike > programming languages like C). > It is a fairly well known optimization in Oracle (at least with the rule-based optimizer, and IIRC non-indexed clauses) to optimize the WHERE clause right to left. > >> for example >> 1) select ... where a and b; >> 2) select ... where b and a; >> >> 1 and 2 will use the same cpu time or not ? > > Depends on the problem. > In theory the optimizer should find the best evaluation order > after analyzing a and b. > > > Bye, Chris. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
sferriol <sylvain.ferriol@imag.fr> writes:
> for example
> 1) select ... where a and b;
> 2) select ... where b and a;
> 1 and 2 will use the same cpu time or not ?
They'll generate the same query plan, except possibly for the evaluation
order of a and b (assuming that those end up attached to the same plan
node in the first place). What that means for CPU time would depend on
what a and b are.
What are you trying to accomplish, exactly? Your question is
essentially useless to ask or answer as it stands, so you'll
need to readjust your thinking.
regards, tom lane
Andrew Rawnsley <ronz@ravensfield.com> writes:
> It is a fairly well known optimization in Oracle (at least with the
> rule-based optimizer, and IIRC non-indexed clauses) to optimize the
> WHERE clause right to left.
That rule doesn't apply to Postgres, though.
If the optimizer does not have any reasons of its own to reorganize the
query (and that's a pretty big "if") then the WHERE clauses will get
evaluated left-to-right. So if you know which clause is more selective,
write it first.
In practice this really only applies to restriction clauses
(single-relation tests) that are not going to be implemented via an
index scan. Join and index clauses will get sliced, diced, and used
no matter what order you list 'em in.
regards, tom lane