Postgresql – ny point with a combined index on all columns in a table

First off, don't mix "primary key" and "clustered index". One is "how to identify a row", the other is "on-disk structure"

Then, you don't need an index on Division by itself because it is the left hand column of the clustered index. However, it won't be covering (see links below)

And for each point in turn:

1. this table doesn't know that "Brcode is unique for all division". If it is, then brCode should be first and unique and the clustered index by itself
2. a search on TrDate won't benefit from the clustered index
3. it depends on selectivity, statistics, how many columns are required. It may seek with a key lookup or it may scan the clustered index (= a table scan) or scan/seek the index on TrDate

Finally, I'd suggest some background reading:

• SQL Server Index Basics
• Using Covering Indexes to Improve Query Performance

The comments on the question show that the issue is that the test database the OP was using to develop the query had radically different data characteristics than the production database. It had much fewer rows and the field being used for filtering wasn't selective enough.

When the number of distinct values in a column is too small the index may not be sufficiently selective. In this case a sequential table scan is cheaper than an index seek/row lookup operation. Typically a table scan makes exensive use of sequential I/O, which is much faster than random access reads.

Often, if a query would return more than just a few percent of rows it will be cheaper just to do a table scan than an index seek/row lookup or similar operation that makes heavy use of random I/O.