Hi
The database is initialized with utf8, so in order for LIKE to use the index
on a text field, I used text_pattern_ops when I created it. So far so good.
It's in the documentation, but there's no explanation of why this index will
only work for LIKE searches. How come that I have to have two different
indexes if I want to give Postgres the ability to choose index scan over seq
scan on LIKE and non-LIKE searches?
Is it a performance issue?
Also, when I tried to create the index as a partial one (avoiding the 95%
entries with empty strings), Postgresql chooses to use seq scan. This sounds
counter intuitive to me.
CREATE INDEX new_index ON a (b text_pattern_ops) WHERE b <> '';
This is 8.2.6.