Hi Culley,
Have you tried to create fk together with index on fk column on table B?
What are results? Would be good if you could send the query and explain analyze...
Sent from my Windows Phone
Have you tried to create fk together with index on fk column on table B?
What are results? Would be good if you could send the query and explain analyze...
Sent from my Windows Phone
From: Culley Harrelson
Sent: 21 December 2011 00:57
To: pgsql-general@xxxxxxxxxxxxxx
Subject: design help for performance
I am bumping into some performance issues and am seeking help.
I have two tables A and B in a one (A) to many (B) relationship. There are 1.4 million records in table A and 44 million records in table B. In my web application any request for a record from table A is also going to need a count of associated records in table B. Several years ago I added table_b_rowcount to table A in order to minimize queries on table B. And now, as the application has grown, I am starting to having locking problems on table A. Any change to table B requires the that table_b_rowcount be updated on table A... The application has outgrown this solution.
So... is there a common solution to this problem?
culley
