postgres 7.4 vs 8.x redux: query plans - Mailing list pgsql-performance

and here are the query plans referenced in my last email (apologies if
you get these twice, they didn't seem to go through the first time,
perhaps due to size?).  I cut out the longer ones.

Thanks,

Alex

postgres 7.4

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num like 'RT2350533%' or num like 'GH0405545%' or
num like 'KL8403192%';


               QUERY PLAN
----------------------------------------------------------------------
 Index Scan using t1_pkey, t1_pkey, t1_pkey on t1  (cost=0.00..17.93
rows=1 width=164) (actual time=0.103..0.238 rows=3 loops=1)
  Index Cond: ((((num)::text >= 'RT2350533'::character varying) AND
((num)::text < 'RT2350534'::character varying)) OR (((num)::text >=
'GH0405545'::character varying) AND ((num)::text <
'GH0405546'::character varying)) OR (((num)::text >=
'KL8403192'::character varying) AND ((num)::text <
'KL8403193'::character varying)))
  Filter: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text ~~
'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
 Total runtime: 0.427 ms
(4 rows)


postgres 8.2

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num like 'RT2350533%' or num like 'GH0405545%' or
num like 'KL8403192%';
                                                         QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..918295.05 rows=1 width=156) (actual
time=15.674..26225.919 rows=3 loops=1)
  Filter: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text ~~
'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
 Total runtime: 26225.975 ms
(3 rows)


posgres 7.4

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in

('AB6253262','AB6145031','AB6091431','AB6286083','AB5857086','AB5649157','AB7089381','AB5557744','AB6314478','AB6505260','AB6249847','AB5832304');



          QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey on t1
(cost=0.00..71.97 rows=12 width=164) (actual time=0.132..0.729 rows=12
loops=1)
  Index Cond: (((num)::text = 'AB6253262'::text) OR ((num)::text =
'AB6145031'::text) OR ((num)::text = 'AB6091431'::text) OR
((num)::text = 'AB6286083'::text) OR ((num)::text = 'AB5857086'::text)
OR ((num)::text = 'AB5649157'::text) OR ((num)::text =
'AB7089381'::text) OR ((num)::text = 'AB5557744'::text) OR
((num)::text = 'AB6314478'::text) OR ((num)::text = 'AB6505260'::text)
OR ((num)::text = 'AB6249847'::text) OR ((num)::text =
'AB5832304'::text))
 Total runtime: 1.019 ms
(3 rows)

postgres 8.2

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in

('AB6253262','AB6145031','AB6091431','AB6286083','AB5857086','AB5649157','AB7089381','AB5557744','AB6314478','AB6505260','AB6249847','AB5832304');

                      QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=28.98..53.25 rows=12 width=156) (actual
time=61.442..61.486 rows=12 loops=1)
  Recheck Cond: ((num)::text = ANY

(('{AB6253262,AB6145031,AB6091431,AB6286083,AB5857086,AB5649157,AB7089381,AB5557744,AB6314478,AB6505260,AB6249847,AB5832304}'::character
varying[])::text[]))
  ->  Bitmap Index Scan on t1_pkey  (cost=0.00..28.98 rows=12
width=0) (actual time=61.429..61.429 rows=12 loops=1)
        Index Cond: ((num)::text = ANY

(('{AB6253262,AB6145031,AB6091431,AB6286083,AB5857086,AB5649157,AB7089381,AB5557744,AB6314478,AB6505260,AB6249847,AB5832304}'::character
varying[])::text[]))
 Total runtime: 61.544 ms
(5 rows)