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-ow...@postgresql.org
[mailto:pgsql-general-ow...@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
