Re: Query organization question

On Mon, Apr 27, 2009 at 02:15:05PM -0700, Gauthier, Dave wrote:
> The stored procedure calls another recursive stored procedure that
> can take a long time to run, usually about 3-4 seconds. Not bad for
> a handful of records, but it is now operating on a table with over
> 40,000 records.

The most general solution I can think of would be to set the "cost" of
a function when creating it, have a look at the docs for create[1] or
alter[2] function.

The answer to your other question is that the inner select statement is
normally called a "sub-select". The way to force the sub-select to be
evaluated first is to put an OFFSET 0 at the end of it, i.e.:

SELECT a, b
FROM (
SELECT a.i AS a, b.i AS b
FROM foo a, foo b
OFFSET 0) x
WHERE a < b;

will force PG to create the complete cross product of "foo" with itself
before trying to apply the outer WHERE clause. I'd try changing the
cost of the function first as it should cause PG to do the "right thing"
when you use the function in other queries.

--
Sam http://samason.me.uk/

[1] http://www.postgresql.org/docs/current/static/sql-createfunction.html
[2] http://www.postgresql.org/docs/current/static/sql-alterfunction.html