Postgresql not using my index

Question

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 :)

See here: dba.stackexchange.com/q/144899/1822

user330315
– user330315

2016-07-26 17:05:05 +00:00
Commented Jul 26, 2016 at 17:05 — user330315
– user330315, Commented Jul 26, 2016 at 17:05

Rahul garg · Accepted Answer · 2016-07-26 16:52:33Z

2

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)

edited Jul 26, 2016 at 16:52

answered Jul 26, 2016 at 16:39

Rahul garg

9,3925 gold badges37 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chicken Sandwich No Pickles Over a year ago

Can you edit your post and be more specific when you say do a separate condition on both columns?

Rahul garg Over a year ago

Can you modify your where condition to do a 'like' condition as I mentioned above..

user330315 Over a year ago

A trigram index can speed up ilike '%xyz%': dba.stackexchange.com/q/144899/1822

Collectives™ on Stack Overflow

Postgresql not using my index

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related