Query optimization for multiple joins postgres

Question

ER Diagram

Brief description

• The database to be used is postgres.

• The database might contain upto 2 million AccountHolder records.

• Since the AccountHolder table is accessed and updated frequently autovacuum_vacuum_scale_factor for AccountHolder table is set to 0.01

• The application only loads latest 5000 records during startup.

• As specified in the ER diagram, an account holder record can have multiple addresses,file attachments,tags and versions(1 to n)

• An account holder record is associated with a group and for a given account holder record, there are balance and credit fields (1 to 1)

• Since latest records are to be fetched, an index is created on LastModificationTimestamp field of the AccountHolder table.

• While joining tables with 1 to 1 relationship INNER JOIN is used.

• While joining tables with 1 to n relationship LEFT OUTER JOIN is used.

• AccountHolderId in AccountHolder table is the PRIMARY KEY and this is used as foreign key in other tables.

Keeping the above things in mind, I have designed this query to fetch the latest 5000 account holder records as follows.

SELECT *
FROM 
     ( SELECT * 
       FROM AccountHolder
       ORDER BY LastModificationTimestamp DESC
       LIMIT 5000 ) AS AccountHolderRecord

INNER JOIN        
     Groups ON AccountHolderRecord.AccountHolderId = Groups.AccountHolderId
INNER JOIN
      BalanceAndCredit ON AccountHolderRecord.AccountHolderId = BalanceAndCredit.HolderId
LEFT OUTER JOIN
      FileAttachments ON AccountHolderRecord.AccountHolderId = FileAttachments.HolderId 
LEFT OUTER JOIN
      Addresses ON AccountHolderRecord.AccountHolderId = Addresses.HolderId        
LEFT OUTER JOIN
      RevisionHistory ON AccountHolderRecord.AccountHolderId = RevisionHistory.AccountHolderId
LEFT OUTER JOIN
      Tags ON AccountHolderRecord.AccountHolderId = Tags.HolderId

Question I am new to Postgres and SQL joins. I want to know whether this query can be further improved. Are there any additional things that I need to take care of to improve the performance?

Answer 1

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order
CREATE INDEX accountholder_LastModificationTimestamp_idx ON acountholder(LastModificationTimestamp DESC NULLS LAST);
Since you are using top 5000 entries indexing is not actually good thing. And index maintenance is expensive. You will be faster by doing sequential scan. ;) If you use timestamps in your search queries that use index, otherwise not.

Useful article: PostgreSQL Indexes

And if you often hit the same query without the data being updated you can have the materialized view:
Materialized views in PostgreSQL docs

I would put not changing things into materialized view (you can refresh it when need it). So you will have less joins, and when playing with it use explain (planning) or explain analyize (planning and executing). Use analyze so you update your statistics.

After looking at the diagram I would do the following:

Extract the changing part of the AcountHolder into separate table with 1:1 relationship, and leave Revision history into separate table (because there is a last modification timestamp and refreshing materialized view is very expensive) and put everything else into materialized view.