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) > 0The 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?
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