Postgresql not allowing index scan although indexing is there

Question

I have table users where I make

CREATE INDEX user_status_index
  ON public.users
  USING btree
  (status COLLATE pg_catalog."default", "keep_id" COLLATE pg_catalog."default");

When I did

EXPLAIN ANALYZE select * from users where keep_id = 'pop90'

It gives me

"Seq Scan on users (cost=0.00..47284.38 rows=2 width=16) (actual time=960.463..3451.731 rows=2 loops=1)"
"  Filter: (("keep_id")::text = 'pop90'::text)"
"  Rows Removed by Filter: 1271935"
"Planning time: 0.075 ms"
"Execution time: 3451.773 ms"

Why it is not taking index scan ??

How can I make it index scan??

Any help will really be appreciable

Chris · Accepted Answer · 2017-07-21 13:21:24Z

1

You are using a multi column index.

There are some limitations when using multi column indexes.

You might be better off creating two single column indexes.

You could read this article: Multi-column Indexes

Multi-column Indexes

While Postgres has the ability to create multi-column indexes, it’s important to understand when it makes sense to do so. The Postgres query planner has the ability to combine and use multiple single-column indexes in a multi-column query by performing a bitmap index scan. In general, you can create an index on every column that covers query conditions and in most cases Postgres will use them, so make sure to benchmark and justify the creation of a multi-column index before you create them. As always, indexes come with a cost, and multi-column indexes can only optimize the queries that reference the columns in the index in the same order, while multiple single column indexes provide performance improvements to a larger number of queries.

However there are cases where a multi-column index clearly makes sense. An index on columns (a, b) can be used by queries containing WHERE a = x AND b = y, or queries using WHERE a = x only, but will not be used by a query using WHERE b = y. So if this matches the query patterns of your application, the multi-column index approach is worth considering. Also note that in this case creating an index on a alone would be redundant.

answered Jul 21, 2017 at 13:21

Chris

1,10611 silver badges20 bronze badges

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

3 Comments

Rajarshi Das Over a year ago

Yes In my case some time we use status = 't' and keep_id = 'pop90' but still it take seq scan instead bitmap index scan

Chris Over a year ago

Do you have lots of tuples with the same keep_id? The planner might decide it's better off with a seq scan. Have you tried creating a single column index on keep_id?

Chris Over a year ago

Could be statistics are outdated please run: vacuum analyze verbose users;

Collectives™ on Stack Overflow

Postgresql not allowing index scan although indexing is there

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