Re: design help for performance

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: 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