ruby-on-railspostgresqlrails-migrationscompound-index

Do I need single column indexing if have compound indexing in rails?


I have created a table in rails with PostgreSQL, it's a join table between account and program, what the best way to add the index? Do I need the single indexing index: true for each reference when I have both order of compound indexing?

create_table :custom_library_programs do |t|
  t.references :account, index: true, foreign_key: true
  t.references :program, index: true, foreign_key: true
  t.boolean :submitted, default: false

  t.timestamps null: false

  t.index [:account_id, :program_id]
  t.index [:program_id, :account_id]
end 

Solution

  • Nope, the indexing you have will cover you. The way compound indexing works is it will only be used sequentially in the order the columns were defined by your index. For example your first index:

    t.index [:account_id, :program_id] ##Speeds up queries for accounts or 'accounts & programs'
    

    Will work the same as

    t.index :account_id ##Speeds up queries for accounts

    if you're only looking up accounts. This however will not speed up queries soley looking for programs (only queries for 'accounts & programs' will be sped up) but since you made a compound index in the reverse way ( t.index [:program_id, :account_id] ) also you'll have fast lookups both directions.