Re: design help for performance

Thanks David. That was my original solution and it began to bog down the
website so I resorted to demoralization 3 years ago.... This is an
extremely high volume website.

On Tue, Dec 20, 2011 at 4:27 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> 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(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Misa Simic
> *Sent:* Tuesday, December 20, 2011 7:13 PM
> *To:* Culley Harrelson; pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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****
>