Sample Header Ad - 728x90

Index on column with data type citext not used

4 votes
1 answer
6547 views
In PostgreSQL 9.4, with following schema: CREATE TABLE people ( id INTEGER PRIMARY KEY, name TEXT, junk CHAR(1000) ); INSERT INTO people(id, name) SELECT generate_series(1,100000), md5(random()::text); CREATE INDEX ON people (name text_pattern_ops); if I search by name, the index is used: test=# explain analyze select id, name from people where name like 'a%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on people (cost=248.59..1160.92 rows=6061 width=37) (actual time=2.412..8.340 rows=6271 loops=1) Filter: (name ~~ 'a%'::text) Heap Blocks: exact=834 -> Bitmap Index Scan on people_name_idx (cost=0.00..247.08 rows=6266 width=0) (actual time=2.123..2.123 rows=6271 loops=1) Index Cond: ((name ~>=~ 'a'::text) AND (name ~ Otherwise, it behaves almost exactly like text. How can I tell PostgreSQL to use the index?
Asked by José Luis (215 rep)
Jun 26, 2015, 02:37 PM
Last activity: Aug 1, 2023, 06:04 PM