ER Diagram 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 AccountHolder
       ORDER BY LastModificationTimestamp DESC
       LIMIT 5000 ) AS AccountHolderRecord

INNER JOIN        
     Groups ON AccountHolderRecord.AccountHolderId = Groups.AccountHolderId
      BalanceAndCredit ON AccountHolderRecord.AccountHolderId = BalanceAndCredit.HolderId
      FileAttachments ON AccountHolderRecord.AccountHolderId = FileAttachments.HolderId 
      Addresses ON AccountHolderRecord.AccountHolderId = Addresses.HolderId        
      RevisionHistory ON AccountHolderRecord.AccountHolderId = RevisionHistory.AccountHolderId
      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?


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.

  • Thanks for answering my question. I will create an index on AccountHolderId key wherever its used as foreign key. It makes sense. The user can also load the next 5000 records via app(records 5001 to 10000,10001 to 15000 and so on). At any point of time 5K records can be loaded(different LIMIT and OFFSET values). That's why I thought of creating an index on LastModificationTimeStamp. I need to learn more about Materialized views to be able to comment on it. I will look into it. Thanks again. – Programmer Nov 8 '14 at 1:40
  • From postgres docs postgresql.org/docs/9.3/static/indexes-ordering.html Indexes are more useful when only a few rows need be fetched. An important special case is ORDER BY in combination with LIMIT n: an explicit sort will have to process all the data to identify the first n rows, but if there is an index matching the ORDER BY, the first n rows can be retrieved directly, without scanning the remainder at all. – Programmer Nov 8 '14 at 1:52
  • I guess when there are around 2 million rows, using order by with index to retrieve 5K records is faster. – Programmer Nov 8 '14 at 1:55
  • 1
    The bad thing about Materialized view that they copy the data into new table. Be careful about that. You can always run \d+ command in psql so you can see the size of tables and views and other postgres "relations". You can also do cluster on index (phisically rearrange data). You might look into pg_repack extension. ;) The pg_repack only works on linux or it is currently supported. And then you have various tweaking on postgresql hardware setup. ;) – Mladen Uzelac Nov 8 '14 at 2:04

Your Answer

