Here is some actionable advice for speeding up a join with indexes. You'll need one in each table with the join columns. For example:
SELECT ...
FROM table_A
JOIN table_B ON table_A.column_A1 = table_B.column_B1
AND table_A.column_A2 = table_B.column_B2
You can add indexes like this:
- table_A(column_A1, column_A2)
- table_B(column_B1, column_B2)
If both tables are large enough, this query can probably take advantage of those indexes to perform a merge join.
Postgres tip: you can also add columns to the include part of the index to speed up filters in the WHERE conditions. You might even get an index-only scan! Look into covering indexes to learn more about it.
SELECT ... FROM table_A JOIN table_B ON table_A.column_A1 = table_B.column_B1 AND table_A.column_A2 = table_B.column_B2
You can add indexes like this: - table_A(column_A1, column_A2) - table_B(column_B1, column_B2)
If both tables are large enough, this query can probably take advantage of those indexes to perform a merge join.
Postgres tip: you can also add columns to the include part of the index to speed up filters in the WHERE conditions. You might even get an index-only scan! Look into covering indexes to learn more about it.