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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Misa Simic Hi Culley, From: Culley Harrelson I am bumping into some performance issues and am seeking help. |
