Does anyone have any trickery that will allow me to index the following? 'route' is always
left anchored and variable length.
Column | Type | Modifiers
--------+-------------------+-----------
route | character varying |
route
----------
1
1234
1234567
(3 rows)
SELECT route FROM test WHERE '12345678901' LIKE route || '%';
Seq Scan on test (cost=0.00..28.45 rows=6 width=32)
Filter: ('12345678901'::text ~~ ((route)::text || '%'::text))
SELECT route FROM test WHERE route = SUBSTR('12345678901', 1, LENGTH(route));
Seq Scan on test (cost=0.00..31.53 rows=6 width=32)
Filter: ((route)::text = substr('12345678901'::text, 1, length((route)::text)))
The lookup table normally consists of a few hundred thousand entries, thus my interest in
indexing.
Full Text Search / Inverted Indexes have been suggested but I cannot seem to find out how
to implement, if anyone has a few tips to give me in this area, by all means. :)
jsaunders