2 ms·
The simple query: explain (analyze, timing) SELECT id FROM a WHERE a_val LIKE 'something%' OR a_val LIKE 'other%';
by wolf550e 7y ago
The simple query:
explain (analyze, timing) SELECT id FROM a
WHERE a_val LIKE 'something%'
OR a_val LIKE 'other%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on a (cost=8.87..12.88 rows=20 width=4) (actual time=0.012..0.012 rows=0 loops=1)
Recheck Cond: ((a_val ~~ 'something%'::text) OR (a_val ~~ 'other%'::text))
Filter: ((a_val ~~ 'something%'::text) OR (a_val ~~ 'other%'::text))
-> BitmapOr (cost=8.87..8.87 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=1)
-> Bitmap Index Scan on a_val_idx (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((a_val ~>=~ 'something'::text) AND (a_val ~<~ 'somethinh'::text))
-> Bitmap Index Scan on a_val_idx (cost=0.00..4.43 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((a_val ~>=~ 'other'::text) AND (a_val ~<~ 'othes'::text))
Planning Time: 0.088 ms
Execution Time: 0.033 ms
(10 rows)
using union:
explain (analyze, timing)
select id from a
where a_val like 'something%'
union
select id from a
where a_val like 'other%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=17.23..17.43 rows=20 width=4) (actual time=0.135..0.135 rows=0 loops=1)
Group Key: a.id
-> Append (cost=0.42..17.18 rows=20 width=4) (actual time=0.133..0.133 rows=0 loops=1)
-> Index Scan using a_val_idx on a (cost=0.42..8.44 rows=10 width=4) (actual time=0.128..0.128 rows=0 loops=1)
Index Cond: ((a_val ~>=~ 'something'::text) AND (a_val ~<~ 'somethinh'::text))
Filter: (a_val ~~ 'something%'::text)
-> Index Scan using a_val_idx on a a_1 (cost=0.42..8.44 rows=10 width=4) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((a_val ~>=~ 'other'::text) AND (a_val ~<~ 'othes'::text))
Filter: (a_val ~~ 'other%'::text)
Planning Time: 0.124 ms
Execution Time: 0.164 ms
(11 rows)
using gin index:
EXPLAIN (analyze, timing)
SELECT id FROM a
WHERE a_val LIKE ANY (ARRAY['something%', 'other%']);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on a (cost=176.15..247.11 rows=20 width=4) (actual time=0.036..0.036 rows=0 loops=1)
Recheck Cond: (a_val ~~ ANY ('{something%,other%}'::text[]))
-> Bitmap Index Scan on a_val_trgm_idx (cost=0.00..176.15 rows=20 width=0) (actual time=0.034..0.034 rows=0 loops=1)
Index Cond: (a_val ~~ ANY ('{something%,other%}'::text[]))
Planning Time: 0.091 ms
Execution Time: 0.193 ms
(6 rows)
Doesn't the simple query win?