On Thu, Aug 31, 2006 at 08:06:57PM +0700, Jeroen T. Vermeulen wrote:
> On Thu, August 31, 2006 18:56, Peter Eisentraut wrote:
> > With time, it becomes ever clearer to me that prepared SQL
> > statements are just a really bad idea.  On some days, it seems like
> > half the performance problems in PostgreSQL-using systems are
> > because a bad plan was cached somewhere.
> Is there any kind of pattern at all to this problem?  Anything
> recognizable?  A few typical pitfalls?

Frequently I have found preplanning will result in a horrible plan
because it is assumed parameters may be volatile while in practice they
are literals. Here is a function from my database:

CREATE FUNCTION nullorblank(character varying) RETURNS boolean
    AS $_$ select $1 is null or trim($1) = '' $_$

This is used in stored procedures that answer search queries. For
example, let's consider one that searches products, filtered on any
number of "part number", "manufacturer", or "name". If one of these is
not specified, it does not restrict the query. One might write that
query so:

-- $1: part number
-- $2: manufacturer
-- $3: name

  (nullorblank($1) OR lower(partnumber) = lower($1))
  AND (nullorblank($2) OR manufacturername = $2)
  AND (nullorblank($3) OR name = $3)

The parameters will always be literal strings, taken from some form
presented to the user. If one does the parameter subsitution manually,
the plans are quite reasonable:

  (nullorblank('int2100/512') OR lower(partnumber) = lower('int2100/512'))
  AND (nullorblank('') OR manufacturername = '')
  AND (nullorblank('') OR name = '');

                                                                   QUERY PLAN   
 Result  (cost=15.54..4494.71 rows=1867 width=254) (actual time=43.502..43.507 
rows=1 loops=1)
   ->  Bitmap Heap Scan on product  (cost=15.54..4494.71 rows=1867 width=254) 
(actual time=43.161..43.162 rows=1 loops=1)
         Recheck Cond: (lower((partnumber)::text) = 'int2100/512'::text)
         ->  Bitmap Index Scan on product_partnumber_loweridx  
(cost=0.00..15.54 rows=1867 width=0) (actual time=43.022..43.022 rows=1 loops=1)
               Index Cond: (lower((partnumber)::text) = 'int2100/512'::text)
 Total runtime: 51.626 ms
(7 rows)

The 'manufacturername' and 'name' disjuncts have been removed by
simplification, since the expression is known to be true.

However, if "prepared", it's horrible:

PREPARE to_be_slow(text, text, text) AS
  (nullorblank($1) OR lower(partnumber) = lower($1))                      
  AND (nullorblank($2) OR manufacturername = $2)
  AND (nullorblank($3) OR name = $3);

explain analyze execute to_be_slow('int2100/512', NULL, NULL);
QUERY PLAN                                                                      
 Result  (cost=0.00..22317.13 rows=1 width=254) (actual time=1115.167..1579.535 
rows=1 loops=1)
   ->  Seq Scan on product  (cost=0.00..22317.12 rows=1 width=254) (actual 
time=1114.845..1579.211 rows=1 loops=1)
         Filter: (((($1)::character varying IS NULL) OR (btrim(($1)::text) = 
''::text) OR (lower((partnumber)::text) = lower($1))) AND ((($2)::character 
varying IS NULL) OR (btrim(($2)::text) = ''::text) OR (manufacturername = $2)) 
AND ((($3)::character varying IS NULL) OR (btrim(($3)::text) = ''::text) OR 
((name)::text = $3)))
 Total runtime: 1580.006 ms
(5 rows)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to