Re: Prepared statements considered harmful

On Thu, 2006-08-31 at 15:19, Peter Eisentraut wrote:
> OK, why don't you work out an example. Let's look at this query:
>
> SELECT * FROM t1 WHERE a LIKE $1;
>
> What two plans would you prepare?

if substring($1 from 1 for 1) != '%' then
use plan 1 (see below);
else
use plan 2 (see below);
end if;

Save both plans from below with the meta-plan from above, and call it a
prepared plan.

cnagy=# create table t1 (a text);
CREATE TABLE
cnagy=# insert into t1 select round(10000000 * random()) from
generate_series(1,10000);
INSERT 0 10000
cnagy=# create index idx_t1_a on t1 (a);
CREATE INDEX
cnagy=# analyze verbose t1;
INFO: analyzing "public.t1"
INFO: "t1": scanned 55 of 55 pages, containing 10000 live rows and 0
dead rows; 3000 rows in sample, 10000 estimated total rows
ANALYZE
cnagy=# explain select a from t1 where a like '121%';
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=2.06..27.63 rows=10 width=10)
Filter: (a ~~ '121%'::text)
-> Bitmap Index Scan on idx_t1_a (cost=0.00..2.06 rows=10 width=0)
Index Cond: ((a >= '121'::text) AND (a < '122'::text))
(4 rows)

cnagy=# explain select a from t1 where a like '%121';
QUERY PLAN
------------------------------------------------------
Seq Scan on t1 (cost=0.00..180.00 rows=80 width=10)
Filter: (a ~~ '%121'::text)
(2 rows)

Cheers,
Csaba.