Do I need to explicitly index a foreign key if I index it with another column?

Question

I was going through this example:

class CreateArtworkShares < ActiveRecord::Migration[5.2]
  def change
    create_table :artwork_shares do |t|
      t.integer :artwork_id, null: false
      t.integer :viewer_id, null: false
      t.timestamps
    end
    add_index :artwork_shares, :artwork_id
    add_index :artwork_shares, :viewer_id
    add_index :artwork_shares, [:artwork_id, :viewer_id], unique: true
  end
end

And I was referring to this Stack Overflow post: Index on multiple columns in Ruby on Rails

I'm a bit confused as to why the solution in this example explicitly indexes the :artwork_id. Doesn't the indexing with :artwork_id and :viewer_id (the last line of indexing in the code) already make an index for the :artwork_id? And I was also wondering if there was an easy way to decide on the ordering of the columns in the last indexing line. I read through the Stack Overflow answer posted above, but the explanation on the ordering of these columns was a little too general for me to grasp the concept.

max · Accepted Answer · 2019-12-10T21:35:33.843

Doesn't the indexing with :artwork_id and :viewer_id (the last line of indexing in the code) already make an index for the :artwork_id?

No. A compound index is only used when you query on both columns - thats the whole point. Its a an index of the combinations.

irb(main):001:0> ArtworkShare.where(viewer_id: 1).explain
  ArtworkShare Load (0.8ms)  SELECT "artwork_shares".* FROM "artwork_shares" WHERE "artwork_shares"."viewer_id" = $1  [["viewer_id", 1]]
=> EXPLAIN for: SELECT "artwork_shares".* FROM "artwork_shares" WHERE "artwork_shares"."viewer_id" = $1 [["viewer_id", 1]]
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on artwork_shares  (cost=4.20..13.67 rows=6 width=40)
   Recheck Cond: (viewer_id = '1'::bigint)
   ->  Bitmap Index Scan on index_artwork_shares_on_viewer_id  (cost=0.00..4.20 rows=6 width=0)
         Index Cond: (viewer_id = '1'::bigint)
(4 rows)

irb(main):002:0> ArtworkShare.where(viewer_id: 1, artwork_id: 1).explain
  ArtworkShare Load (1.7ms)  SELECT "artwork_shares".* FROM "artwork_shares" WHERE "artwork_shares"."viewer_id" = $1 AND "artwork_shares"."artwork_id" = $2  [["viewer_id", 1], ["artwork_id", 1]]
=> EXPLAIN for: SELECT "artwork_shares".* FROM "artwork_shares" WHERE "artwork_shares"."viewer_id" = $1 AND "artwork_shares"."artwork_id" = $2 [["viewer_id", 1], ["artwork_id", 1]]
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_artwork_shares_on_artwork_id_and_viewer_id on artwork_shares  (cost=0.15..8.17 rows=1 width=40)
   Index Cond: ((artwork_id = '1'::bigint) AND (viewer_id = '1'::bigint))
(2 rows)

And that migration is well... pretty bad.

class CreateArtworkShares < ActiveRecord::Migration[6.0]
  def change
    create_table :artwork_shares do |t|
      t.belongs_to :artwork, null: false, foreign_key: true
      t.belongs_to :viewer, null: false, foreign_key: true
      t.timestamps
    end

    add_index :artwork_shares, [:artwork_id, :viewer_id], unique: true
  end
end

This adds foreign key constraints and indexes on artwork_id and viewer_id. It also creates bigint instead of regular integer columns.

I was also wondering if there was an easy way to decide on the ordering of the columns in the last indexing line.

No. There is no easy way. It depends on the access paths in the application.

So for deciding on the ordering, what did the post mean by putting the one that narrows down the rows fastest first? For example, if an artist can't have more than one artwork with the same title, would the correct ordering be: the artwork title and then the artist? Correct me if I'm wrong, but if I were to put the artist first, then it would have to look through all of the artist's artworks. But if I were to put the title first, it will only grab a small portion (the repeated titles) of the artist's artworks. — , Dec 10 '19 at 22:47
Yeah, that sounds about right. Its not something I even think I really truly understand myself. https://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys — max, Dec 11 '19 at 12:57

Do I need to explicitly index a foreign key if I index it with another column?

1 Answers1