Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B22B5633A6C for ; Wed, 3 Feb 2010 14:51:08 -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 78706-04 for ; Wed, 3 Feb 2010 18:50:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from marajade.localdomain (vcs5.camavision.com [63.228.164.252]) by mail.postgresql.org (Postfix) with ESMTP id 7659263396F for ; Wed, 3 Feb 2010 14:50:57 -0400 (AST) Received: from [192.168.10.85] (unknown [192.168.10.85]) (Authenticated sender: andy) by marajade.localdomain (Postfix) with ESMTPA id 4411440C33; Wed, 3 Feb 2010 12:50:56 -0600 (CST) Message-ID: <4B69C590.2070709@squeakycode.net> Date: Wed, 03 Feb 2010 12:50:56 -0600 From: Andy Colson User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.7) Gecko/20100111 Thunderbird/3.0.1 MIME-Version: 1.0 To: Matt White , pgsql-performance@postgresql.org Subject: Re: Slow-ish Query Needs Some Love References: <1d4feebf-ff4d-4463-b1c8-d1a12d27400c@z10g2000prh.googlegroups.com> <250b74b4-f38f-4dab-a8bc-91d6d152d46c@h9g2000prn.googlegroups.com> <4B6886F6.4080402@squeakycode.net> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.849 tagged_above=-10 required=5 tests=AWL=0.750, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/65 X-Sequence-Number: 37486 On 2/3/2010 11:17 AM, Matt White wrote: > On Feb 2, 1:11 pm, a...@squeakycode.net (Andy Colson) wrote: >> On 2/2/2010 1:03 PM, Matt White wrote: >> >> >> >> >> >>> On Feb 2, 6:06 am, Edgardo Portal wrote: >>>> 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 >>>> ; >> >>> Thanks for the suggestion. The view didn't seem to speed things up. >>> Perhaps we can reduce the number of times it's called, we'll see. Any >>> additional ideas would be helpful. Thanks. >> >> I agree with Edgardo, I think the biggest time saver will be reducing >> trips to the database. >> >> But... do you have an index on users.user_group_id? >> >> Does rewriting it change the plan any? >> >> SELECT COUNT(*) FROM users >> inner join user_groups on (users.user_group_id = user_groups.id) >> where NOT users.deleted >> AND user_groups.partner_id IN (partner_id_1, partner_id_2); >> >> And... it looks like the row guestimate is off a litte: >> >> Index Scan using user_groups_partner_id_idx >> on user_groups >> (cost=0.00..133.86 rows=3346 width=8) >> (actual time=0.049..96.992 rows=100001 loops=2) >> >> It guessed 3,346 rows, but actually got 100,001. Have you run an >> analyze on it? If so, maybe bumping up the stats might help? >> >> -Andy >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org) >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance > > Andy, > > I have run analyze, see my query plan in my original post. You'll have > to forgive me for being a bit of a Postgres noob but what do you mean > by "bumping up the stats"? Thats not what I mean. "explain analyze select..." is what you did, and correct. What I meant was "analyze user_groups". see: http://www.postgresql.org/docs/8.4/interactive/sql-analyze.html an analyze will make PG look at a table, and calc stats on it, so it can make better guesses. By default analyze only looks at a few rows (well a small percent of rows) and makes guesses about the entire table based on those rows. If it guesses wrong, sometimes you need to tell it to analyze more rows (ie. a bigger percentage of the table). By "bumping the stats" I was referring to this: http://wiki.postgresql.org/wiki/Planner_Statistics I have never had to do it, so dont know much about it. It may or may not help. Just thought it was something you could try. -Andy