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) ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org