Greetings,
I've run into an annoying issue which I would think could be handled better. Basically, indexes using
text_pattern_opsdon't work with some complex regexps even when they (imv anyway) could. I'm willing to believe I'm
wrongabout the potential to use them, or that my regexp is wrong, but I don't see it.
Test case:
create table text_test (name text); insert into text_test values ('North'); insert into text_test values ('North
West');create index text_test_name_idx on text_test using btree (name text_pattern_ops); set enable_seqscan = false; --
justto show the test -- works fine explain analyze select * from text_test where name ~ '^(North)'; -- works fine
explainanalyze select * from text_test where name ~ '^(North)( West)'; -- doesn't work explain analyze select * from
text_testwhere name ~ '^(North)(| West)';
Results:
CREATE TABLE
INSERT 0 1
INSERT 0 1
CREATE INDEX
SET QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------Index
Scanusing text_test_name_idx on text_test (cost=0.00..8.27 rows=1 width=32) (actual time=0.071..0.077 rows=2 loops=1)
IndexCond: ((name ~>=~ 'North'::text) AND (name ~<~ 'Norti'::text)) Filter: (name ~ '^(North)'::text)Total runtime:
0.121ms
(4 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------Index
Scanusing text_test_name_idx on text_test (cost=0.00..8.27 rows=1 width=32) (actual time=0.176..0.178 rows=1 loops=1)
IndexCond: ((name ~>=~ 'North'::text) AND (name ~<~ 'Norti'::text)) Filter: (name ~ '^(North)( West)'::text)Total
runtime:0.209 ms
(4 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------Seq
Scanon text_test (cost=100000000.00..100000001.03 rows=1 width=32) (actual time=0.013..0.019 rows=2 loops=1) Filter:
(name~ '^(North)(| West)'::text)Total runtime: 0.045 ms
(3 rows)
I don't see why the last case can't use the index. Obviously, for this example case, doing a Seq Scan is fine but
withthe real data set there are cases where an index could help.
Any help would be greatly appreciated.
Thanks,
Stephen
