Thread: Optimize expresiions.

Optimize expresiions.

From
han.holl@informationslogik.nl
Date:
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


Re: Optimize expresiions.

From
Richard Huxton
Date:
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

Re: Optimize expresiions.

From
han.holl@informationslogik.nl
Date:
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

Re: Optimize expresiions.

From
"Adam Rich"
Date:
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


Re: Optimize expresiions.

From
Alban Hertroys
Date:
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 //

Re: Optimize expresiions.

From
han.holl@informationslogik.nl
Date:
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

Re: Optimize expresiions.

From
han.holl@informationslogik.nl
Date:
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