DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=jIebjIDrY9CSkuCUJwHQQSxmrhI8Tx1aps0Ti6n9XxVrOS/AVCJavWSLBsCRp+01qr
	SEdqr7Qenv17NVVd0Pag+MnuL8OrWu5IBfloelGcJ9kPlCAiAmAdoa3Hbdmq0WVZLCPA
	7Yk1a44wniR0nJHGxwoOI/6+jj3ZEFw22ySak=
MIME-Version: 1.0
In-Reply-To: 
 <1d4feebf-ff4d-4463-b1c8-d1a12d27400c@z10g2000prh.googlegroups.com>
References: 
 <1d4feebf-ff4d-4463-b1c8-d1a12d27400c@z10g2000prh.googlegroups.com>
Date: Thu, 4 Feb 2010 20:49:26 -0500
Message-ID: <603c8f071002041749x14ff4414g1ed2bb26be3a0bce@mail.gmail.com>
Subject: Re: Slow-ish Query Needs Some Love
From: Robert Haas <robertmhaas@gmail.com>
To: Matt White <mattw922@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Feb 1, 2010 at 7:53 PM, Matt White <mattw922@gmail.com> 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
> =A0WHERE users.user_group_id =3D user_groups.id AND NOT users.deleted AND
> user_groups.partner_id IN
> =A0(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?
>
> Here's the query plan:
>
> =A0Aggregate =A0(cost=3D12574.53..12574.54 rows=3D1 width=3D0) (actual
> time=3D2909.298..2909.299 rows=3D1 loops=3D1)
> =A0 -> =A0Hash Join =A0(cost=3D217.79..12566.08 rows=3D3378 width=3D0) (a=
ctual
> time=3D2909.284..2909.284 rows=3D0 loops=3D1)
> =A0 =A0 =A0 =A0 Hash Cond: (users.user_group_id =3D user_groups.id)
> =A0 =A0 =A0 =A0 -> =A0Seq Scan on users =A0(cost=3D0.00..11026.11 rows=3D=
206144
> width=3D4) (actual time=3D0.054..517.811 rows=3D205350 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (NOT deleted)
> =A0 =A0 =A0 =A0 -> =A0Hash =A0(cost=3D175.97..175.97 rows=3D3346 width=3D=
4) (actual
> time=3D655.054..655.054 rows=3D200002 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Nested Loop =A0(cost=3D0.27..175.97 row=
s=3D3346 width=3D4)
> (actual time=3D1.327..428.406 rows=3D200002 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0HashAggregate =A0(cost=3D0.=
27..0.28 rows=3D1
> width=3D4) (actual time=3D1.259..1.264 rows=3D2 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Result =A0(cost=
=3D0.00..0.26 rows=3D1
> width=3D0) (actual time=3D1.181..1.240 rows=3D2 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using user_group=
s_partner_id_idx
> on user_groups =A0(cost=3D0.00..133.86 rows=3D3346 width=3D8) (actual
> time=3D0.049..96.992 rows=3D100001 loops=3D2)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: (user_gro=
ups.partner_id =3D
> (partner_all_subpartners(3494)))
>
>
> The one obvious thing that everyone will point out is the sequential
> scan on users, but there actually is an index on users.deleted. When I
> forced sequential scanning off, it ran slower, so the planner wins
> again.

Yeah, I don't think the sequential scan is hurting you.  What is
bugging me is that it doesn't look like the plan you've posted is for
the query you've posted.  The plan shows an index condition that
references partner_all_subpartners(3494), which doesn't appear in your
original query, and also has two aggregates in it, where your posted
query only has one.

...Robert