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=10width=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.