Continued top-posting to remain consistent….
It isn’t that the application has outgrown the solution but rather the solution was never correct in the first place. You attempted pre-mature optimization and are getting burned because of it. The reference solution is simply:
SELECT a.*, COUNT(*) AS b_count
FROM a
JOIN b USING (a_id)
GROUP BY a.* {expanded * as needed)
Make sure table b has an index on the a.id column.
This is reference because you never want to introduce computed fields that keep track of other tables WITHOUT some kind of proof that the maintenance nightmare/overhead you are incurring is more than offset by the savings during usage.
Any further optimization requires two things:
Knowledge of the usage patterns of the affected data
Testing to prove that the alternative solutions out-perform the reference solution
Since you already have an existing query you should implement the reference solution above and then test and see whether it performs better or worse than you current solution. If it indeed performs better than move to it; and if it is still not good enough then you need to provide more information about what kinds of queries are hitting A and B as well as Insert/Delete patterns on Table B.
David J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Misa Simic
Sent: Tuesday, December 20, 2011 7:13 PM
To: Culley Harrelson; pgsql-general@postgresql.org
Subject: Re: [GENERAL] design help for performance
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
From: Culley Harrelson
Sent: 21 December 2011 00:57
To: pgsql-general@postgresql.org
Subject: [GENERAL] 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
