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)
