Indexes in Postgresql

Question

We have a table users (userid, username, category, pincode) in postgresql

If we create indexes like:

create index category_idx(category) on users;
create index category_pincode_idx(category, pincode) on users;

and query the table users as:

select * from users where category = somevalue;

which index will be applied?

The first index is redundant. Any query that can use the first index also can use the second. You might also want to read this: postgresql.org/docs/current/static/indexes-examine.html — user330315
– user330315, Commented May 3, 2013 at 8:22
One more thing i would like to ask: If there is a table users (userid, username, category, pincode) whose primary key is (userid, category, pincode), then it is required/not to create an index on those primary key columns separately/as a whole. — user2346047
– user2346047, Commented May 3, 2013 at 9:18
When you define a primary key, Postgres automatically creates an unique index for that: postgresql.org/docs/current/static/indexes-unique.html — user330315
– user330315, Commented May 3, 2013 at 9:19
So, it means there is no need to create an index explicitly on column pincode separately if its a part of primary key. — user2346047
– user2346047, Commented May 3, 2013 at 9:23
That's not what I said. If you have a query that has a condition only on pincode, it might make sense. Please do read the chapter about indexes in the manual — user330315
– user330315, Commented May 3, 2013 at 9:24

Oliver Weichhold · Accepted Answer · 2013-05-03 08:22:27Z

3

Executing the following statement will reveal which index the execution planner will pick depending on the current data set:

explain select * from users where category = somevalue;

Please be advised that the planner might even chose to not use the index if the amount of data does not warrant the use of an index because a full table scan over very few rows might be quicker.

answered May 3, 2013 at 8:22

Oliver Weichhold

10.3k5 gold badges48 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

Indexes in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related