Indexing on a text column:
create index index_documents_on_id_with_pattern_ops (id text_pattern_ops);
This works fine:
> explain select id from documents where id like 'dingbat%';
Index Only Scan using index_documents_on_id_with_pattern_ops on documents
(cost=0.56..8.58 rows=736 width=19)
Index Cond: ((id >= 'dingbat'::text) AND (id < 'dingbau'::text))
Filter: (id ~~ 'dingbat%'::text)
But for some reason, if an underscore character appears in my search string, it
falls back to a disasterously slow seqscan:
> explain select id from documents where id like '_dingbat%';
Seq Scan on documents (cost=0.00..779238.28 rows=736 width=19)
Filter: (id ~~ '_dingbat%'::text)
Is this because of PostgreSQL’s collation system? Using “C” doesn’t work either.
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
