CODEWITHSUNDEEP
databasepostgresqlindexing
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

Postgresql not using my index

I can't understand why my query is doing a sequential scan.

select columns
from table
where (column_1 ilike '%whatever%' or column_2 ilike '%whatever%')

I have an index on both column_1 and column_2.

The cardinality on both columns is very high.

My table is roughly 25 million rows.

What do you think I might be doing wrong? No matter what I do, it always does a sequential scan.

Edit #1: My index looks like this:

Create index xxx on table  (column_1, column_2);

Edit #2: Changing my sql query to

select columns
from table
where (column_1 ilike 'whatever%' and column_2 ilike 'whatever%')

still made my query use a sequential scan. I got the same result when I just used like instead of ilike. But this query:

select columns
from table
where (column_1 = 'whatever' and column_2 = whatever)

made my query use an index scan and my query went much faster :)

Upvotes: 0

Views: 65

Answers (1)

Rahul garg
Rahul garg

Reputation: 9362

Two Reasons:

  • Your query does a OR condition in which index can't be used.
  • You are doing a ilike on "%xyz%". This can't use any help of sorted(i.e. indexed) data.

--

Edit: See if you can have like on "xyz%". Then index can be used if you do a separate condition on both columns (and separate index on both)

Edit2: By the query, the thing you are trying to do looks like Full Text Search. For that you would need search indexing techniques (Read Elasticsearch, Sphinx, Solr)

Upvotes: 2

Related Questions