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) ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html