You can index on expressions, and these will be recognised by the query
generator.
drop schema tmp CASCADE;
create schema tmp;
set search_path = tmp;
CREATE TABLE bagger
( eight CHAR(8) NOT NULL PRIMARY KEY
, more text
);
CREATE INDEX bagger_idx_12 ON bagger (substr(eight,1,2));
CREATE INDEX bagger_idx_34 ON bagger (substr(eight,3,2));
CREATE INDEX bagger_idx_58 ON bagger (substr(eight,5,4));
INSERT INTO bagger(eight, more)
SELECT translate(to_hex( gs), ' ' , '0')
, gs::text
FROM generate_series(0,4000000000, 64999) gs
;
VACUUM ANALYZE bagger;
EXPLAIN ANALYZE
SELECT * FROM bagger
WHERE 1=1
-- AND eight >= '00' AND eight < '05'
AND substr(eight, 1,2) >= '30' AND substr(eight,1,2) < '05'
AND substr(eight, 3,2) >= '90' AND substr(eight,3,2) < 'A5'
AND substr(eight, 5,4) >= '3333' AND substr(eight,5,4) < '4444'
;
/*** The optimiser is smart enough to ignore one of the indexes. */
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on bagger (cost=41.46..59.62 rows=1 width=19)
(actual time=0.049..0.056 rows=0 loops=1)
Recheck Cond: ((substr((eight)::text, 1, 2) >= '30'::text) AND
(substr((eight)::text, 1, 2) < '05'::text) AND (substr((eight)::text, 3,
2) >= '90'::text) AND (substr((eight)::text, 3, 2) < 'A5'::text))
Filter: ((substr((eight)::text, 5, 4) >= '3333'::text) AND
(substr((eight)::text, 5, 4) < '4444'::text))
-> BitmapAnd (cost=41.46..41.46 rows=16 width=0) (actual
time=0.042..0.047 rows=0 loops=1)
-> Bitmap Index Scan on bagger_idx_12 (cost=0.00..4.47
rows=308 width=0) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: ((substr((eight)::text, 1, 2) >= '30'::text)
AND (substr((eight)::text, 1, 2) < '05'::text))
-> Bitmap Index Scan on bagger_idx_34 (cost=0.00..36.74
rows=3205 width=0) (never executed)
Index Cond: ((substr((eight)::text, 3, 2) >= '90'::text)
AND (substr((eight)::text, 3, 2) < 'A5'::text))
Planning Time: 5.487 ms
Execution Time: 0.310 ms
(10 rows)
HTH,
AvK
