Re: spurious function execution in prepared statements. - Mailing list pgsql-performance

Stephan Szabo wrote:
> On Thu, 30 Sep 2004, Merlin Moncure wrote:
>
> > 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.
> An actual boolean expr on t? Or on a column in t?
[...]
> I think a reproducible example would be good. Simple attempts to
duplicate
> this on 8.0b2 have failed for me, unless I'm using order by.

Note: I confirmed that breaking out the 'where' part of the query into
subquery suppresses the behavior.

Here is the actual query:
select lock_cuid(id), *
    from data3.wclaim_line_file
    where wcl_vin_no >= '32-MHAB-C-X-7243' and
        (wcl_vin_no >  '32-MHAB-C-X-7243' or  wcl_claim_no >=
001) and
        (wcl_vin_no >  '32-MHAB-C-X-7243' or  wcl_claim_no >
001 or  id >  2671212)
    order by wcl_vin_no, wcl_claim_no, id
    limit 1


Here is the prepared statement declaration:
prepare data3_read_next_wclaim_line_file_1_lock (character varying,
numeric, int8, numeric)
    as select lock_cuid(id), *
    from data3.wclaim_line_file
    where wcl_vin_no >= $1 and
        (wcl_vin_no >  $1 or  wcl_claim_no >= $2) and
        (wcl_vin_no >  $1 or  wcl_claim_no >  $2 or  id >  $3)
    order by wcl_vin_no, wcl_claim_no, id limit $4


Here is the plan when it runs lock_cuid repeatedly (aside: disabling
seqscans causes an index plan, but that's not the point):

esp=# explain execute data3_read_next_wclaim_line_file_1_lock
('32-MHAB-C-X-7243', 001, 2671212, 1);


           QUERY PLAN

------------------------------------------------------------------------
----------------------------
------------------------------------------------------------------------
----------------------------
--------------------------------
 Limit  (cost=13108.95..13162.93 rows=21592 width=260)
   ->  Sort  (cost=13108.95..13162.93 rows=21592 width=260)
         Sort Key: wcl_vin_no, wcl_claim_no, id
         ->  Seq Scan on wclaim_line_file  (cost=0.00..11554.52
rows=21592 width=260)
               Filter: (((wcl_vin_no)::text >= ($1)::text) AND
(((wcl_vin_no)::text > ($1)::text) OR
 ((wcl_claim_no)::numeric >= $2)) AND (((wcl_vin_no)::text > ($1)::text)
OR ((wcl_claim_no)::numeric
 > $2) OR ((id)::bigint > $3)))
(5 rows)