Styjun

How can solar sailed ships be protected from space debris?

Why are symbols not written in words?

Cannot overlay, because ListPlot does not draw same X range despite the same PlotRange

What verb goes with "coup"?

Can I deep fry food in butter instead of vegetable oil?

Is my background sufficient to start Quantum Computing

What was the ASCII end of medium (EM) character intended to be used for?

Lenovo Legion PXI-E61 Media Test Failure, Check Cable. Exiting PXE ROM. Then restarts and works fine

Enterprise Layers and Naming Conventions

What could a Medieval society do with excess animal blood?

Other homotopy invariants?

What happened to the Apollo 1 rocket?

What prevents a US state from colonizing a smaller state?

How to idiomatically express the idea "if you can cheat without being caught, do it"

Does Dhp 256-257 condone judging others?

Are there advantages in writing by hand over typing out a story?

Why is quantum gravity non-renormalizable?

Tricky riddle from sister

GFCI versus circuit breaker

Is a ccH, ccX and ccH equivalent to a cH, ccX and cH sequence?

Disk usage confusion: 10G missing on Linux home partition on SSD

2019 2-letters 33-length list

Russian equivalents of 能骗就骗 (if you can cheat, then cheat)

Could citing a database like libgen get one into trouble?

Single-column vs. multi-column index for separate but always together joins

Multiple Indexes vs Multi-Column IndexesSQL select join: is it possible to prefix all columns as 'prefix.*'?What are the options for storing hierarchical data in a relational database?Data structure which supports table lookupsOracle SQL : Updating a column with SUM query of another tableZF2 Select Statement: Change columns order with joined tablesTables join using mapping tableMerging two tables which have same and new columnsPostgtres query is not using index for column which is indexedPgsql Delete rows with some columns (not all) duplicateMulti-column index

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)?

sql postgresql database-design outer-join database-indexes

share|improve this question

edited Mar 25 at 20:56

Laurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

asked Mar 25 at 16:43

atkaylaatkayla

2,16244 gold badges2727 silver badges6969 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I would go for the first version. Do note that if the index is used, it will likely define the ordering of the JOINs.
  
  – Gordon Linoff
  Mar 25 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Time to read about using indexes. What references said what that is relevant here, including Stack Overflow? How to Ask A justified answer to this is just rewriting such a reference.
  
  – philipxy
  Mar 25 at 18:22
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of Multiple Indexes vs Multi-Column Indexes
  
  – philipxy
  Mar 25 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 JOINs act similarly.
  
  – atkayla
  Mar 25 at 18:35
  
  

  
  
  
  

add a comment | 

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)?

sql postgresql database-design outer-join database-indexes

share|improve this question

edited Mar 25 at 20:56

Laurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

asked Mar 25 at 16:43

atkaylaatkayla

2,16244 gold badges2727 silver badges6969 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I would go for the first version. Do note that if the index is used, it will likely define the ordering of the JOINs.
  
  – Gordon Linoff
  Mar 25 at 16:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Time to read about using indexes. What references said what that is relevant here, including Stack Overflow? How to Ask A justified answer to this is just rewriting such a reference.
  
  – philipxy
  Mar 25 at 18:22
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of Multiple Indexes vs Multi-Column Indexes
  
  – philipxy
  Mar 25 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 JOINs act similarly.
  
  – atkayla
  Mar 25 at 18:35
  
  

  
  
  
  

add a comment | 

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)?

sql postgresql database-design outer-join database-indexes

share|improve this question

edited Mar 25 at 20:56

Laurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

asked Mar 25 at 16:43

atkaylaatkayla

2,16244 gold badges2727 silver badges6969 bronze badges

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

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"

share|improve this question

edited Mar 25 at 20:56

Laurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

asked Mar 25 at 16:43

atkaylaatkayla

2,16244 gold badges2727 silver badges6969 bronze badges

share|improve this question

edited Mar 25 at 20:56

Laurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

asked Mar 25 at 16:43

atkaylaatkayla

2,16244 gold badges2727 silver badges6969 bronze badges

edited Mar 25 at 20:56

Laurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

edited Mar 25 at 20:56

Laurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

asked Mar 25 at 16:43

atkaylaatkayla

2,16244 gold badges2727 silver badges6969 bronze badges

asked Mar 25 at 16:43

atkaylaatkayla

2,16244 gold badges2727 silver badges6969 bronze badges

1 Answer
1

active

oldest

votes

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.

share|improve this answer

answered Mar 25 at 20:55

Laurenz AlbeLaurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 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 at 6:57
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55342637%2fsingle-column-vs-multi-column-index-for-separate-but-always-together-joins%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered Mar 25 at 20:55

Laurenz AlbeLaurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 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 at 6:57
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Mar 25 at 20:55

Laurenz AlbeLaurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 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 at 6:57
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Mar 25 at 20:55

Laurenz AlbeLaurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

share|improve this answer

answered Mar 25 at 20:55

Laurenz AlbeLaurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

answered Mar 25 at 20:55

Laurenz AlbeLaurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges

answered Mar 25 at 20:55

Laurenz AlbeLaurenz Albe

58.5k1111 gold badges4242 silver badges6363 bronze badges