Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

pgsql-performance by date

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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group