Use a specific index from a table while running a query with multiple joins

Question

I have multiple indexes on a postgres table -

On primary id (notification_permission_on_user_id)
On three different columns (notification_channel_route_type)

I'm running a query with two joins and the second one caters to my requirement better. But when I analyze the query, it uses primary_id_index automatically, which I assume is decided by postgres itself based on the query.

Index Scan using notification_permission_on_user_id on notification_permission  (cost=0.28..0.30 rows=1 width=21) (never executed)
           Index Cond: (user_id = u.id)
           Filter: ((channel = 1) AND (type = 2))

How do I make sure that my query uses the second index which suits my need and would really speed up the process?

Why do you think the 2nd index suits your needs better? It is hard to be more selective than a primary key. Also, an index on (channel, route, type) is not all that well suited to a query that specifies "type" but not "route". — jjanes
– jjanes, Commented May 18, 2021 at 17:56
Actually in my query, I am using all three of the params: (channel, route, type), and with every query I need the results with a specific value of them. Hence I figure I should be using the second index because that'll filter my data based on my exact requirement and take lesser time as I'll be doing this for a larger set of data. — N.Rajal
– N.Rajal, Commented May 19, 2021 at 7:58
But your plan fragment doesn't show "route" being used in the filter. Is it applied somewhere else in the plan instead? — jjanes
– jjanes, Commented May 19, 2021 at 13:45

Laurenz Albe · Accepted Answer · 2021-05-18 16:24:40Z

1

Change the condition so that the index cannot be used:

... WHERE/ON notification_permission.user_id + 0 = u.id

answered May 18, 2021 at 16:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Use a specific index from a table while running a query with multiple joins

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