From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Expensive function and the optimizer
Date: Mon, 07 Nov 2005 23:12:07 -0500
Message-ID: <1158.1131423127@sss.pgh.pa.us> (raw)
In-Reply-To: <4370062B.4050202@modgraph-usa.com>
References: <4370062B.4050202@modgraph-usa.com>
"Craig A. James" <cjames@modgraph-usa.com> writes:
> Is there some way to explain this cost to the optimizer in a permanent
> way,
Nope, sorry. One thing you could do in the particular case at hand is
to rejigger the WHERE clause involving the function so that it requires
values from both tables and therefore can't be applied till after the
join is made. (If nothing else, give the function an extra dummy
argument that can be passed as a variable from the other table.)
This is an ugly and non-general solution of course.
> The only way I've thought of is something like this:
> select X.id from
> (select A.id, A.foo, A.row_num
> from A join B ON (A.id = B.id)
> where A.row_num >= 0 and A.row_num <= 43477
> and B.id = 52) as X
> where myfunc(X.foo, 'FooBar') order by X.row_num;
As written, that won't work because the planner will happily flatten the
query to the same thing you had before. You can put an OFFSET 0 into
the sub-select to prevent that from happening, but realize that this
creates a pretty impervious optimization fence ... the side-effects
might be undesirable when you come to look at real queries instead
of toy cases.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cjames@modgraph-usa.com
Subject: Re: Expensive function and the optimizer
In-Reply-To: <1158.1131423127@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox