020 | a | 9780822342649 (pbk. : alk. paper
020 | a | 9780822342427 (cloth : alk. paper
020 | a | 0822342421 (cloth : alk. paper
020 | a | 0822342642 (pbk. : alk. paper
comet=# explain analyze SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('9780822342427%') AND fr.tag = '020' AND fr.subfield = 'a';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on real_full_rec (cost=35385.33..51207.84 rows=4039 width=233) (actual time=1394.509..1394.528 rows=2 loops=1)
Recheck Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
Filter: ("substring"(value, 1, 1024) ~~ '9780822342427%'::text)
-> BitmapAnd (cost=35385.33..35385.33 rows=4039 width=0) (actual time=1394.455..1394.455 rows=0 loops=1)
-> Bitmap Index Scan on metabib_full_rec_value_tpo_index (cost=0.00..14845.47 rows=480918 width=0) (actual time=0.144..0.144 rows=2 loops=1)
Index Cond: (("substring"(value, 1, 1024) ~>=~ '9780822342427'::text) AND ("substring"(value, 1, 1024) ~<~ '9780822342428'::text))
-> Bitmap Index Scan on metabib_full_rec_tag_subfield_idx (cost=0.00..20537.59 rows=807853 width=0) (actual time=1387.647..1387.647 rows=1654423 loops=1)
Index Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
Total runtime: 1394.579 ms
(9 rows)
everdev=# EXPLAIN ANALYZE SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('9780822342427%') AND fr.tag = '020' AND fr.subfield = 'a';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on real_full_rec (cost=1585.65..1649.81 rows=32 width=81) (actual time=87.958..87.958 rows=0 loops=1)
Recheck Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
Filter: ("substring"(value, 1, 1024) ~~ '9780822342427%'::text)
-> BitmapAnd (cost=1585.65..1585.65 rows=32 width=0) (actual time=87.951..87.951 rows=0 loops=1)
-> Bitmap Index Scan on metabib_full_rec_tag_subfield_idx (cost=0.00..79.19 rows=6446 width=0) (actual time=74.589..74.589 rows=194112 loops=1)
Index Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
-> Bitmap Index Scan on metabib_full_rec_value_idx (cost=0.00..1506.19 rows=125544 width=0) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: (("substring"(value, 1, 1024) >= '9780822342427'::text) AND ("substring"(value, 1, 1024) < '9780822342428'::text))
Total runtime: 89.464 ms
(9 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on real_full_rec (cost=20538.58..1929654.73 rows=4039 width=233) (actual time=318594.170..321547.882 rows=2 loops=1)
Recheck Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
Filter: ("substring"(value, 1, 1024) ~~ '9780822342427%'::text)
-> Bitmap Index Scan on metabib_full_rec_tag_subfield_idx (cost=0.00..20537.57 rows=807851 width=0) (actual time=1192.917..1192.917 rows=1654423 loops=1)
Index Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
Total runtime: 321556.532 ms
(6 rows)
comet=# explain SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('9780822342427%') AND fr.tag = '020' AND fr.subfield = 'a';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on real_full_rec (cost=20538.58..1929654.73 rows=4039 width=233)
Recheck Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
Filter: ("substring"(value, 1, 1024) ~~ '9780822342427%'::text)
-> Bitmap Index Scan on metabib_full_rec_tag_subfield_idx (cost=0.00..20537.57 rows=807851 width=0)
Index Cond: ((tag = '020'::bpchar) AND (subfield = 'a'::text))
(5 rows)