3

Various references, such as join-strategies-and-performance-in-postgresql, say that an index on the join predicate does not improve the performance of a hash join.

However, couldn't a B+ tree index reduce the I/O cost of the table which is probed against the pre-calculated hash table by just loading the leaves of the index into main memory and do a sequential scan on them (instead of the entire table) given that the index contains all attributes of the indexed table which are specified in the SELECT clause?

5
  • 1
    If you don't need any columns from the joined table, other than those participating in a join condition, why would you use a join in the first place?
    – mustaccio
    Mar 12, 2022 at 21:17
  • Yes, an index-only index leaf scan can be used in this situation.
    – jjanes
    Mar 12, 2022 at 21:28
  • @mustaccio i updated the question. i am referring to the attributes of the indexed table.
    – keezar
    Mar 12, 2022 at 22:21
  • 1
    Of course it is possible that an index-only scan is used instead of a sequential scan. An index can also be used if one of the relations joined is not a base table, but the result of a filter operation on a base table. But that is not relevant to the performance of the join itself, and my article describes where indexes can speed up the join (not the overall query). Mar 13, 2022 at 14:57
  • @LaurenzAlbe i see. thank you for the clarification, i really appreciate that.
    – keezar
    Mar 14, 2022 at 13:00

1 Answer 1

1

I think what it means is that the index itself is never directly used as the mechanism for identifying the rows to which the other data set joins.

They might be used as a covering index, or for limiting the rows to be joined in other ways, though. So indexing might still be beneficial in some ways.

3
  • I am just a bit confused since statements like "[...] Only indexes for independent where predicates improve hash join performance. [...] " (Use the index luke) and "[...] However, indexing on the join predicates themselves (the join key) is NOT going to bypass the full scan access against the tables. [...] " (BC) clearly claim the opposite.
    – keezar
    Mar 13, 2022 at 9:40
  • Well BC is talking about Oracle, not postgresql,and is wrong about a lot of things even then. Mar 13, 2022 at 11:01
  • 1
    @DavidAldridge it mentioning Oracle is irrelevant, Oracle can handle index only reads like every other RDBMS. The author is presumably just trying not to convolute a generally correct statement with exceptions. Mar 13, 2022 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.