spurious function execution in prepared statements.

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: spurious function execution in prepared statements.
Date: 2004-09-30 13:45:51
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3412A74DD@Herge.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

OK, I have a situation that might be a performance problem, a bug, or an
unavoidable consequence of using prepared statements. The short version
is that I am getting function executions for rows not returned in a
result set when they are in a prepared statement.

In other words, I have a query:
select f(t.c) from t where [boolean expr on t] limit 1;

because of the limit phrase, obviously, at most one record is returned
and f executes at most once regardless of the plan used (in practice,
sometimes index, sometimes seq_scan.

Now, if the same query is executed as a prepared statement,
prepare ps(...) as select f(t.c) from t where [expr] limit 1;
execute ps;

now, if ps ends up using a index scan on t, everything is ok. However,
if ps does a seqscan, f executes for every row on t examined until the
[expr] criteria is met. Is this a bug? If necessary I should be able
to set up a reproducible example. The easy workaround is to not use
prepared statements in these situations, but I need to be able to
guarantee that f only executes once (even if that means exploring
subqueries).

Merlin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Adler 2004-09-30 14:13:24 Re: [HACKERS] spurious function execution in prepared statements.
Previous Message Merlin Moncure 2004-09-30 12:09:30 Re: shared memory release following failed lock acquirement.

Browse pgsql-performance by date

  From Date Subject
Next Message Michael Adler 2004-09-30 14:13:24 Re: [HACKERS] spurious function execution in prepared statements.
Previous Message Guy Thornley 2004-09-30 07:02:32 Re: O_DIRECT setting