faster SELECT

Sorry for my last blank post, anyway, im not sure this helps but have
you tried using EXPLAIN?

Dexter Tad-y

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 should be used first in WHERE . Preferrable condition "a" should be created index aware, so relatively inexpensive index scan can eliminate most of unwanted lines from output. Following this rule should you should be able to create a fastest and 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