0

If I have a schema like this:

profile_network
---
id profile_id(fk) school_type_id(fk) school_major_type_id(fk) school_degree_type_id(fk)

And a query that will always be used to join all of those to their respective lookup tables:

LEFT JOIN "profile_network" ON "profile_network"."profile_id" = "profile"."id"
LEFT JOIN "profile_network_school_type" ON "profile_network:school_type"."id" = "profile_network"."school_type_id"
LEFT JOIN "profile_network_school_major_type" ON "profile_network:school_major_type"."id" = "profile_network"."school_major_type_id"
LEFT JOIN "profile_network_school_degree_type" ON "profile_network:school_degree_type"."id" = "profile_network"."school_degree_type_id"

Should I create 4 indexes on each individual column (col1)(col2)(col3)(col4), or 1 index on all of the columns (col1, col2, col3, col4)?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
atkayla
  • 8,143
  • 17
  • 72
  • 132
  • I would go for the first version. Do note that if the index is used, it will likely define the ordering of the `JOIN`s. – Gordon Linoff Mar 25 '19 at 16:47
  • Time to read about using indexes. What references said what that is relevant here, including [so]? [ask] A justified answer to this is just rewriting such a reference. – philipxy Mar 25 '19 at 18:22
  • Possible duplicate of [Multiple Indexes vs Multi-Column Indexes](https://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes) – philipxy Mar 25 '19 at 18:23
  • I am wondering if indexing for `LEFT JOIN A LEFT JOIN B` acts similarly to indexing for `WHERE A and B`. For `WHERE A and B`, I usually prefer to index separately `(A)(B)` or a concatenated reverse index `(B, A)` instead of `(A, B)` because it wouldn't use the index for `B` alone, but I am not sure if `JOIN`s act similarly. – atkayla Mar 25 '19 at 18:35

1 Answers1

1

It depends on the join type chosen:

  • With a nested loop join, an index on the join condition of the lookup tables would help.

  • For a hash join, no index helps.

  • For a merge join, an index on the join condition of thr lookup table may help.

It all depends on the cardinalities.

A multi-column index is definitely the wrong thing.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • That query with no indexes does hash join. So it sounds like even if I added an index or 4 to it, it's already doing the best it can? I assume this school-related stuff would be considered low to medium cardinality, in this database especially because there are only 10 profile rows. :) – atkayla Mar 25 '19 at 23:39
  • You'll have to experiment using `EXPLAIN`. That way you can see if an index is used, and you know which join strategy the database uses. – Laurenz Albe Mar 26 '19 at 06:57