2 edited body
source | link

Question2: Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

This is your index, now:

Index3 (FooId, BarId, Id, QuxId)

Because Id is unique (as it is the primary key), there can be no two rows with same Id and different QuxId. Therefore, your index is equivalent to this one (which will use slightly less space):

Index3b (FooId, BarId, Id) INCLUDES (QuxId)

You can even remove the Id column and have the:

Index3bIndex3c (FooId, BarId) INCLUDES (QuxId)

An query like the following can use equally well Index 3, 3b or 3c:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 ;

If however you have a query with a range scan or a sorting on QuxID:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
  AND QuxID BETWEEN 123 AND 314 ;

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
ORDER BY QuxID ;

the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the QuxID values in the needed order:

Index3d (FooId, BarId, QuxId)

In short, the indexes (FooId, BarId, Id, QuxId) and (FooId, BarId, QuxId) can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other.

Question2: Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

This is your index, now:

Index3 (FooId, BarId, Id, QuxId)

Because Id is unique (as it is the primary key), there can be no two rows with same Id and different QuxId. Therefore, your index is equivalent to this one (which will use slightly less space):

Index3b (FooId, BarId, Id) INCLUDES (QuxId)

You can even remove the Id column and have the:

Index3b (FooId, BarId) INCLUDES (QuxId)

An query like the following can use equally well Index 3, 3b or 3c:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 ;

If however you have a query with a range scan or a sorting on QuxID:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
  AND QuxID BETWEEN 123 AND 314 ;

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
ORDER BY QuxID ;

the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the QuxID values in the needed order:

Index3d (FooId, BarId, QuxId)

In short, the indexes (FooId, BarId, Id, QuxId) and (FooId, BarId, QuxId) can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other.

Question2: Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

This is your index, now:

Index3 (FooId, BarId, Id, QuxId)

Because Id is unique (as it is the primary key), there can be no two rows with same Id and different QuxId. Therefore, your index is equivalent to this one (which will use slightly less space):

Index3b (FooId, BarId, Id) INCLUDES (QuxId)

You can even remove the Id column and have the:

Index3c (FooId, BarId) INCLUDES (QuxId)

An query like the following can use equally well Index 3, 3b or 3c:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 ;

If however you have a query with a range scan or a sorting on QuxID:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
  AND QuxID BETWEEN 123 AND 314 ;

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
ORDER BY QuxID ;

the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the QuxID values in the needed order:

Index3d (FooId, BarId, QuxId)

In short, the indexes (FooId, BarId, Id, QuxId) and (FooId, BarId, QuxId) can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other.

1
source | link

Question2: Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

This is your index, now:

Index3 (FooId, BarId, Id, QuxId)

Because Id is unique (as it is the primary key), there can be no two rows with same Id and different QuxId. Therefore, your index is equivalent to this one (which will use slightly less space):

Index3b (FooId, BarId, Id) INCLUDES (QuxId)

You can even remove the Id column and have the:

Index3b (FooId, BarId) INCLUDES (QuxId)

An query like the following can use equally well Index 3, 3b or 3c:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 ;

If however you have a query with a range scan or a sorting on QuxID:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
  AND QuxID BETWEEN 123 AND 314 ;

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
ORDER BY QuxID ;

the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the QuxID values in the needed order:

Index3d (FooId, BarId, QuxId)

In short, the indexes (FooId, BarId, Id, QuxId) and (FooId, BarId, QuxId) can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other.