Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 478B265A426 for ; Tue, 2 Feb 2010 16:18:44 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 81126-08-3 for ; Tue, 2 Feb 2010 20:18:21 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 828EF63776B for ; Tue, 2 Feb 2010 15:19:27 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id o12JJPG1043547 for ; Tue, 2 Feb 2010 15:19:25 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id o12D6g55020466 for pgsql-performance@postgresql.org; Tue, 2 Feb 2010 09:06:42 -0400 (AST) (envelope-from news) From: Edgardo Portal X-Newsgroups: pgsql.performance Subject: Re: Slow-ish Query Needs Some Love Date: Tue, 2 Feb 2010 13:06:46 +0000 (UTC) Organization: A noiseless patient Spider Lines: 28 Message-ID: References: <1d4feebf-ff4d-4463-b1c8-d1a12d27400c@z10g2000prh.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Injection-Date: Tue, 2 Feb 2010 13:06:46 +0000 (UTC) Injection-Info: feeder.eternal-september.org; posting-host="EeshJbEvHaS09LkZI2lDxQ"; logging-data="27173"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19Ifv2Wm1pwtr+m5ls6iEWXZTPW57a32Vo=" User-Agent: slrn/0.9.9p1 (Linux) Cancel-Lock: sha1:ste4PAShULXyDWoy7JUqbRCCZSI= To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.298 tagged_above=-10 required=5 tests=BAYES_50=0.001, FORGED_YAHOO_RCVD=2.297 X-Spam-Level: ** X-Archive-Number: 201002/41 X-Sequence-Number: 37462 On 2010-02-02, Matt White wrote: > I have a relatively straightforward query that by itself isn't that > slow, but we have to run it up to 40 times on one webpage load, so it > needs to run much faster than it does. Here it is: > > SELECT COUNT(*) FROM users, user_groups > WHERE users.user_group_id = user_groups.id AND NOT users.deleted AND > user_groups.partner_id IN > (partner_id_1, partner_id_2); > > The structure is partners have user groups which have users. In the > test data there are over 200,000 user groups and users but only ~3000 > partners. Anyone have any bright ideas on how to speed this query up? Can you avoid running it 40 times, maybe by restructuring the query (or making a view) along the lines of the following and adding some logic to your page? SELECT p.partner_id, ug.user_group_id, u.id, count(*) FROM partners p LEFT JOIN user_groups ug ON ug.partner_id=p.partner_id LEFT JOIN users u ON u.user_group_id=ug.id WHERE NOT u.deleted GROUP BY 1,2,3 ;