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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of han.holl@xxxxxxxxxxxxxxxxxxxx Sent: Thursday, January 11, 2007 4:59 AM To: pgsql-general@xxxxxxxxxxxxxx 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
