The database to be used is postgres.
The database might contain upto 2 million
AccountHoldertable is accessed and updated frequently
AccountHoldertable 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
LastModificationTimestampfield of the
While joining tables with 1 to 1 relationship
INNER JOINis used.
While joining tables with 1 to n relationship
LEFT OUTER JOINis used.
AccountHoldertable is the
PRIMARY KEYand 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?