PostgreSQL Force Index Scan

Question

I have a table with below columns in PostgreSQL.

id,
location,
store,
flag1,
flag2

Here id, location, store are contributing to Primary Key. So by default postgreSQL creates an index on it.

I have created two more index apart from primary key index as below :

id
location, id

Now when I run the below query :

EXPLAIN ANALYZE
SELECT *
FROM my_table
WHERE location = '1' AND id = '1';

It should have used the index with location, id, but it uses primary key index.

Now when I run below query, it uses id index, which is expected.

EXPLAIN ANALYZE
SELECT *
FROM my_table
WHERE id = '1';

I am not sure on how can I force the indexing or the behaviour of PostgreSQL is correct & efficient one. On paper, it looks like location, id would be better choice for the query.

Can some help on the issue/doubt I have ? Also if this is the expected behaviour, can you provide some query which will use location,id index ?

Why do you want to force an index? That's the wrong idea. DB engines use them when it's good to use them, not when you think they should. Maybe the statistics are obsolete or their target is too small? — Jonas Metzler
– Jonas Metzler, Commented Jul 11 at 12:35
Yeah, thats fine, but do you have any scenario where it can use location,id index ? — Prashant Aghara
– Prashant Aghara, Commented Jul 11 at 12:40
Please don't describe a table, give the full and complete DDL for the table and indexes. For example, the order of the columns in the indexes / keys matters, as well as whether the columns are strings or integers. Giving the DDL leaves no ambiguity. — MatBailie
– MatBailie, Commented Jul 11 at 12:51
You should also be aware that WHERE WHERE location = '1' AND id = '1'; is identical to WHERE ID = '1' AND location = '1'; Which would explain why a primary key of id, location, store is given precedence. — MatBailie
– MatBailie, Commented Jul 11 at 12:57

Lajos Arpad · Accepted Answer · 2025-07-11 12:47:49Z

0

Let's first understand how indexes work. If you have an index of

(A, B, C)

then this index will index on A primarily and in case of ties index on B and when it's still a tie index on C.

So, if your primary key index is on

(id, location, store)

and you have a where clause like

WHERE location = '1' AND id = '1';

Then it will use the index even if you do not search by store. However, if the index is useless for your search, like in this case

WHERE location = '1'

because the first dimension is an id and not location and there is no criteria on id, then an index on

(location, store)

may come in handy.

So if you have an index on (A, B, C) then you do not need one on (A, B) or one on (A), it would be a waste of storage and performance.

However, if you have an index on (A, B, C) then anything not starting with A may be used.

If for some reason you want to index on location first, you might want to change the order of the fields inside your primary key constraint.

answered Jul 11 at 12:47

Lajos Arpad

80.5k42 gold badges124 silver badges235 bronze badges

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

2 Comments

Prashant Aghara Jul 11 at 17:46

Let me check. Thanks for suggestion

Lajos Arpad Jul 12 at 8:59

@PrashantAghara happy to help

Collectives™ on Stack Overflow

PostgreSQL Force Index Scan

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related