text_pattern_ops and complex regexps
От | Stephen Frost |
---|---|
Тема | text_pattern_ops and complex regexps |
Дата | |
Msg-id | 20090506144544.GU8123@tamriel.snowman.net обсуждение исходный текст |
Ответы |
Re: text_pattern_ops and complex regexps
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: