Re: HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work
Date: 2008-02-25 16:08:57
Message-ID: 27592.1203955737@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov> writes:
> --Test 1: This shows that fn_pg_costlyfunction() is the only function
> that is run -
> -- unexpected to me shouldn't no function be evaluated or the cheap one?
> --What's the difference between Test 1 and Test 2 that makes Test 2 do
> the RIGHT thing?
> TRUNCATE TABLE log_call;
> SELECT (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunction() > 2 OR 5 >
> 2);

In a SELECT with no FROM we don't run the optimizer at all; the
assumption is that when the expression will only be evaluated once,
it's not worth trying to do expression simplification on it first.

> --Test 2: This works as I would expect - shows that none of the
> functions are run presumably its going straight for 5 > 2
> --becuase it recognizes its the cheapest route
> TRUNCATE TABLE log_call;
> SELECT foo.value
> FROM (SELECT (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunction() > 2 OR
> 5 > 2 ) as value) as foo

That's just constant-folding: x OR TRUE is TRUE. It has exactly
zero to do with the cost of anything.

Offhand I think the behavior you are looking for of choosing to run more
expensive subexpressions later only occurs for top-level WHERE clauses
that are combined with AND.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Obe, Regina 2008-02-25 16:28:46 Re: HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work
Previous Message Joris Dobbelsteen 2008-02-25 16:08:13 Re: Planner: rows=1 after "similar to" where condition.