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
Last activity: Aug 1, 2023, 06:04 PM