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

Re: spurious function execution in prepared statements.

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>,<pgsql-hackers(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: spurious function execution in prepared statements.
Date: 2004-09-30 14:58:34
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3412A74DF@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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)

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-09-30 15:02:01
Subject: Re: spurious function execution in prepared statements.
Previous:From: Tom LaneDate: 2004-09-30 14:54:50
Subject: Re: [HACKERS] spurious function execution in prepared statements.

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-09-30 15:02:01
Subject: Re: spurious function execution in prepared statements.
Previous:From: Tom LaneDate: 2004-09-30 14:54:50
Subject: Re: [HACKERS] spurious function execution in prepared statements.

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