Thread: 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
han.holl@informationslogik.nl wrote: > 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 ? Not really. Perhaps look into using a functional/expressional index and see if that does the job for you. http://www.postgresql.org/docs/8.2/static/indexes-expressional.html http://www.postgresql.org/docs/8.2/static/sql-createfunction.html -- Richard Huxton Archonet Ltd
On Thursday 11 January 2007 12:34, Richard Huxton wrote: > > 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 ? > > Not really. Perhaps look into using a functional/expressional index and > see if that does the job for you. > Oh well, pity. Thanks anyway. In my case, indexes don't help. Do you know if functionality like this has ever be considered ? I seem to encounter many queries where the order in the where clause matters, (but then we have a kind of weird database). Cheers, Han Holl
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
han.holl@informationslogik.nl
Sent: Thursday, January 11, 2007 4:59 AM
To: pgsql-general@postgresql.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
han.holl@informationslogik.nl wrote: > On Thursday 11 January 2007 12:34, Richard Huxton wrote: > >>> 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 ? >> Not really. Perhaps look into using a functional/expressional index and >> see if that does the job for you. >> > Oh well, pity. Thanks anyway. > In my case, indexes don't help. Maybe your functions can be marked STABLE or even IMMUTABLE? That should help the planner evaluate them less often. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
On Thursday 11 January 2007 15:12, Alban Hertroys wrote: > Maybe your functions can be marked STABLE or even IMMUTABLE? That should > help the planner evaluate them less often. They are immutable, nevertheless they will have to be computed for each record. I'd just like to have some influence on the order in case of lazy evaluation. Han Holl
On Thursday 11 January 2007 15:03, Adam Rich wrote: > 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 > > Well, there is nothing wrong with the much simpler where cheap_function(item) > 0 and where very_expensive_function(item) > 0. It's all about the order of evaluation. Thanks, Han Holl