CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4
siv rj
siv rj

Reputation: 1511

Does normal indexing also work by creating unique index?

Hi Guys, i would like to know, if i create unique index of two columns on postgreSQL, does normal indexing for the both columns also work by same unique index or i have to create one unique index and two more index for both columns as shown in the code? I want to create unique index of talent_id, job_id, also both columns should separately indexed. I read many resources but does not get appropriate answer.

add_index :talent_actions, [:talent_id, :job_id], unique: true

Does above code also handles below indexing also or i have to add below indexing separately?

add_index :talent_actions, :talent_id
add_index :talent_actions, :job_id

Thank you.

Upvotes: 1

Views: 55

Answers (1)

IMSoP
IMSoP

Reputation: 97688

An index is an object in the database, which can be used to look up data faster, if the query planner decides it will be appropriate. So the trivial answer to your question is "no", creating one index will not result in the same structures in the database as creating three different indexes.

I think what you actually want to know is this:

Do I need all three indexes, or will the unique index already optimise all queries?

This, as with any database optimisation, depends on the queries you run, and the data you have.

Here are some considerations:

  • The order of columns in a multi-column index matters. If you have an index of people sorted by surname then first name, then you can use it to search for everybody with the same surname; but you probably can't use it to search for somebody when you only know their first name.
  • Data distribution matters. If everyone in your list has the surnames "Smith" and "Jones", then you can use a surname-first index to search for a first name fairly easily (just look up under Jones, then under Smith).
  • Index size matters. The fewer columns an index has, the more of it fits in memory at once, so the faster it will be to use.

Often, there are multiple indexes the query planner could use, and its job is to estimate the cost of the above factors for the query you've written.

Usually, it doesn't hurt to create multiple indexes which you think might help, but it does use up disk space, and occasionally can cause the query planner to pick a worse plan. So the best approach is always to populate a database with some real data, and look at the query plans for some real queries.

Upvotes: 1

Related Questions