Question 247025 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – The index that I have created for a table does not work

I have a table

CREATE TABLE cities 
(
    id serial, 
    name character varying(30)
);

Then, some rows were added. In order to make the queries faster, an index was defined:

CREATE INDEX cities_name_idx ON cities (name);
SET enable_seqscan TO off;

However, after running EXPLAIN for this query:

SELECT * 
FROM cities 
WHERE name ILIKE 'Lond%'

The result was: it turned out that the index isn’t used – the database performs a sequential scan instead.

Why is it happening and how to fix it?

Tags:

2

Answers


  1. 0

    You can use a functional index. For example:

    create index ix1 on cities ((lower(name)));

select * 
from cities 
where lower(name) >= lower('Lond') and lower(name) < lower('Lone')

    Or, alternatively, sometimes it’s possible to change a case insensitive collation for the column.

    Login or Signup to reply.
  2. 0

    If you want to support ILIKE, there is only a GIN trigram index:

    CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ON cities USING gin (name gin_trgm_ops);

    However, such an index tends to be big and costs a lot of performance during data modifications.

    It would be better to change your query to

    SELECT * FROM cities WHERE lower(name) LIKE lower('Lond%');

    Then you could do with a regular b-tree index:

    CREATE INDEX ON cities (name text_pattern_ops);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search