Postgres Index Approach Base on Cardinality

Question

GCP postgres 12.8

Table A (21 Million + rows)
Join with Table B (3 Million + rows)
On A.col_x=B.Col_x
Where B.Col_y=123;

Both A.Col_x and B.Col_x are indexed (but not unique thousands of rows for same value(s) of Col_x in both tables) and join performance is fine 'Index Scan'.

Filter 'B.Col_y=123' (not indexed so far) is causing 'parallel Seq Scan'. I want to create index on B.Col_y. B.Col_y values are unique in table B.

Experts please consider the cardinality of column B.Col_x (Thousands per value) and B.Col_y (unique) and advise me what new index will perform best in my case.

1. Standalone index on B.Col_y
2. Dual column index on (B.Col_y,B.Col_x)
3. Dual column index on (B.Col_x,B.Col_y)
4. A container index of B.Col_x contain(Col_y)
5. any other option

Moreover wish to understand how multicolumn index works in Postures for performance, How it differ from Oracle and SQL Server. How to place low and hi cardinality columns from left to right and so on.

Wishes

Answer 1

From what you describe, the ideal access path would be a nested loop join, with B as the outer table.

So you should create an index on B(Col_y). Together with the existing index om A(Col_x), that should do the trick.