Index on column with data type citext not used
Index usage with text_pattern_ops (as well as with the default operator class when using the C locale) depends on the binary representation of character data. citext stores original values with the case preserved, so there must be a problem with that ...

Like you commented, the actual reason is burried in collation support.

Either way, citext or text, you can make it work with an expression index:

CREATE INDEX people_name_idx ON people (lower(name) text_pattern_ops);


And a corresponding query:

SELECT id, name FROM people WHERE lower(name) LIKE 'abc%';


Note that lower(name) returns data type text, even when feeding it citext.

Alternatively, you could use a trigram index, which is more expensive to maintain, but also offers more capabilities:


How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


Aside: your test case is suboptimal because your dummy values are all lower case to begin with and the pattern 'a%' is often not selective enough to use an index at all. And char(1000) doesn't make sense (even though irrelevant to the test).