Re: Optimize expresiions.

How about this?

select item, very_expensive_function(item) as exp, cheap
from ( Select item, cheap_function(item) as cheap
From atable where cheap_function(item) > 0 ) sub
where very_expensive_function(item) > 0

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
han(dot)holl(at)informationslogik(dot)nl
Sent: Thursday, January 11, 2007 4:59 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Optimize expresiions.

Hello,

Given a table:
create table atable (
item integer;
);
and a view:
create view aview as select
item,
very_expensive_function(item) as exp,
cheap_function(item) as cheap
from atable;

Now the query:
select item from aview where exp > 0 and cheap > 0;
will lead to a sequential scan on atable with filter:
very_expensive_function(item) > 0 and cheap_function(item) > 0

The query would run much faster with the filter reordered.
Is there a way to tell the planner/optimizer that certain functions are
more
expensive than others, and should be postponed in lazy evaluation ?
Or is there a hook in the system that would allow me too look at and
maybe
reorder expressions before they are executed ?

Cheers,

Han Holl

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq