Re: Prepared statements considered harmful

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) = '' $_$
LANGUAGE sql IMMUTABLE;

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

SELECT * FROM product WHERE
(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:

EXPLAIN ANALYZE
SELECT * FROM product WHERE
(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
SELECT * FROM product WHERE
(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)