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 ABing t1_pkey, t1_pkey, t1_pkey on t1 (cost=0.00..17.93
rows=1 width=164) (actual time=11.652..12.132 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: 12.320 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
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=24.03..28.04 rows=1 width=157) (actual
time=165.681..274.872 rows=3 loops=1)
Recheck Cond: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text
~~ 'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
Filter: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text ~~
'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
-> BitmapOr (cost=24.03..24.03 rows=1 width=0) (actual
time=126.080..126.080 rows=0 loops=1)
-> Bitmap Index Scan on t1_pkey (cost=0.00..8.01 rows=1
width=0) (actual time=61.805..61.805 rows=1 loops=1)
Index Cond: (((num)::text >= 'RT2350533'::character
varying) AND ((num)::text < 'RT2350534'::character varying))
-> Bitmap Index Scan on t1_pkey (cost=0.00..8.01 rows=1
width=0) (actual time=37.388..37.388 rows=1 loops=1)
Index Cond: (((num)::text >= 'GH0405545'::character
varying) AND ((num)::text < 'GH0405546'::character varying))
-> Bitmap Index Scan on t1_pkey (cost=0.00..8.01 rows=1
width=0) (actual time=26.876..26.876 rows=1 loops=1)
Index Cond: (((num)::text >= 'KL8403192'::character
varying) AND ((num)::text < 'KL8403193'::character varying))
Total runtime: 274.938 ms
(11 rows)
Postgres 7.4
EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in
('AB6698130','AB7076908','AB6499382','AB6438888','AB6385893','AB6378237','AB7146973','AB7127138','AB7124531','AB7124513','AB7123427','AB7121183','AB7121036','AB7110101','AB7100321','AB7089845','AB7088750','AB7031384','AB7021188','AB7006144','AB6988331','AB6973865','AB6966775','AB6935066','AB6931779','AB6923412','AB6902405','AB6892488','AB6886288','AB6880467','AB6874269','AB6871439','AB6868615','AB6819495','AB6807740','AB6799138','AB6796038','AB6769347','AB6732987','AB6722076','AB6718130','AB6717543','AB6714564','AB6701821','AB6667761','AB6666630','AB6655069','AB6648287','AB6643969','AB6636412');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan ABing t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, 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..304.64 rows=50 width=164)
(actual time=0.118..2.267 rows=50 loops=1)
Index Cond: (((num)::text = 'AB6698130'::text) OR ((num)::text =
'AB7076908'::text) OR ((num)::text = 'AB6499382'::text) OR
((num)::text = 'AB6438888'::text) OR ((num)::text = 'AB6385893'::text)
OR ((num)::text = 'AB6378237'::text) OR ((num)::text =
'AB7146973'::text) OR ((num)::text = 'AB7127138'::text) OR
((num)::text = 'AB7124531'::text) OR ((num)::text = 'AB7124513'::text)
OR ((num)::text = 'AB7123427'::text) OR ((num)::text =
'AB7121183'::text) OR ((num)::text = 'AB7121036'::text) OR
((num)::text = 'AB7110101'::text) OR ((num)::text = 'AB7100321'::text)
OR ((num)::text = 'AB7089845'::text) OR ((num)::text =
'AB7088750'::text) OR ((num)::text = 'AB7031384'::text) OR
((num)::text = 'AB7021188'::text) OR ((num)::text = 'AB7006144'::text)
OR ((num)::text = 'AB6988331'::text) OR ((num)::text =
'AB6973865'::text) OR ((num)::text = 'AB6966775'::text) OR
((num)::text = 'AB6935066'::text) OR ((num)::text = 'AB6931779'::text)
OR ((num)::text = 'AB6923412'::text) OR ((num)::text =
'AB6902405'::text) OR ((num)::text = 'AB6892488'::text) OR
((num)::text = 'AB6886288'::text) OR ((num)::text = 'AB6880467'::text)
OR ((num)::text = 'AB6874269'::text) OR ((num)::text =
'AB6871439'::text) OR ((num)::text = 'AB6868615'::text) OR
((num)::text = 'AB6819495'::text) OR ((num)::text = 'AB6807740'::text)
OR ((num)::text = 'AB6799138'::text) OR ((num)::text =
'AB6796038'::text) OR ((num)::text = 'AB6769347'::text) OR
((num)::text = 'AB6732987'::text) OR ((num)::text = 'AB6722076'::text)
OR ((num)::text = 'AB6718130'::text) OR ((num)::text =
'AB6717543'::text) OR ((num)::text = 'AB6714564'::text) OR
((num)::text = 'AB6701821'::text) OR ((num)::text = 'AB6667761'::text)
OR ((num)::text = 'AB6666630'::text) OR ((num)::text =
'AB6655069'::text) OR ((num)::text = 'AB6648287'::text) OR
((num)::text = 'AB6643969'::text) OR ((num)::text =
'AB6636412'::text))
Total runtime: 3.073 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
('AB6698130','AB7076908','AB6499382','AB6438888','AB6385893','AB6378237','AB7146973','AB7127138','AB7124531','AB7124513','AB7123427','AB7121183','AB7121036','AB7110101','AB7100321','AB7089845','AB7088750','AB7031384','AB7021188','AB7006144','AB6988331','AB6973865','AB6966775','AB6935066','AB6931779','AB6923412','AB6902405','AB6892488','AB6886288','AB6880467','AB6874269','AB6871439','AB6868615','AB6819495','AB6807740','AB6799138','AB6796038','AB6769347','AB6732987','AB6722076','AB6718130','AB6717543','AB6714564','AB6701821','AB6667761','AB6666630','AB6655069','AB6648287','AB6643969','AB6636412');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=216.70..418.95 rows=50 width=157)
(actual time=203.880..417.165 rows=50 loops=1)
Recheck Cond: ((num)::text = ANY
(('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character
varying[])::text[]))
-> Bitmap Index Scan on t1_pkey (cost=0.00..216.69 rows=50
width=0) (actual time=198.188..198.188 rows=50 loops=1)
Index Cond: ((num)::text = ANY
(('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character
varying[])::text[]))
Total runtime: 417.288 ms
(5 rows)