PostgreSQL index in multi-column WHERE clause

Question

I'm trying to optimize queries modelled on the following example:

UPDATE posts
SET title = "Example", lock_version = 1
WHERE (
    posts.id = 123
    AND
    posts.lock_version = 0
)

I have a unique index on posts.id, which ensures that positive hits on WHERE posts.id = 123 will never return more than one result.

Do I also need an index on posts.lock_version?

I imagine I would need it if the first part of the WHERE returned more than one result, because the index would make it faster to narrow down the filter.

What about the situation I described, though?

If posts.id is a primary key (or otherwise unique: a candidate key) then alll other columns are functionally dependant on it , and an extra index would not be needed, given that you always use the PK to address a row, — joop
– joop, Commented Dec 1, 2014 at 18:20

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

No. If posts.id is unique, you don't need more than the simple index on that column.

It won't get any faster - on the contrary: since you update the row, you would add maintenance cost to update the additional index as well.

Aside: you don't need the parentheses in your WHERE clause:

...
WHERE id = 123
AND   lock_version = 0

And if you double-quote "Example" it refers to a column of that name. You probably want the value: 'Example'. Single quotes for a string literal.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 1, 2014 at 18:19

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

tompave Over a year ago

Thank you. Yes, you're also right about the format.

Collectives™ on Stack Overflow

PostgreSQL index in multi-column WHERE clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related