X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2F8EC52815
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 27 May 2005 12:18:55 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 92022-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 27 May 2005 15:18:50 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207])
	by svr1.postgresql.org (Postfix) with ESMTP id DF00C52819
	for <pgsql-sql@postgresql.org>; Fri, 27 May 2005 12:18:44 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id 68so1842526wra
	for <pgsql-sql@postgresql.org>; Fri, 27 May 2005 08:18:44 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=YegfQJ23ozdRasg9ZBg6TndjQ0hIrvw0DMJ9rSyIQDl6pIJtwGHQxcCtR8HdU24jm5/XRuFpPcjRmwnyx7hgGMVi38ikU15f0ImhGukXA3cAgnenVnQiPqMlCpgVq1vUCECPTLe85UUXLN1CrRQWR0iDv2Yvkixn7A/clbzPwck=
Received: by 10.54.46.37 with SMTP id t37mr2597463wrt;
	Fri, 27 May 2005 08:18:44 -0700 (PDT)
Received: by 10.54.65.20 with HTTP; Fri, 27 May 2005 08:18:44 -0700 (PDT)
Message-ID: <6d8daee3050527081834321a35@mail.gmail.com>
Date: Fri, 27 May 2005 08:18:44 -0700
From: Tony Wasson <ajwasson@gmail.com>
Reply-To: Tony Wasson <ajwasson@gmail.com>
To: ogjunk-pgjedan@yahoo.com
Subject: Re: 3 tables, slow count(*), order by Seq Scan in Query Plan
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20050527035634.49305.qmail@web31110.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <20050527035634.49305.qmail@web31110.mail.mud.yahoo.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.315 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_ABUSE, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200505/229
X-Sequence-Number: 21542

On 5/26/05, ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com> wrote:
> Hello,
>=20
> I have 3 tables (2 tables + 1 lookup table that ties them) and running
> a straight-forward aggregate count(*) query with a couple of joins
> takes about 10 seconds (and I need it to be sub-second or so).
> Also, I am wondering if this approach is scalable with my row-counts
> and my hardware (below).
>=20
> My slow query is this:
> ----------------------
> SELECT keyword.name, count(*)
> FROM user_data, user_data_keyword, keyword
> WHERE (user_data.user_id =3D 1)
>       AND (user_data.id =3D user_data_keyword.user_data_id)
>       AND (user_data_keyword.keyword_id =3D keyword.id)
>       GROUP BY keyword.name
>       ORDER BY COUNT(*) DESC LIMIT 10;

<SNIP>

> Is there any way of speeding up my query?
>=20
> Also, given the number of rows expected in those tables:
> user_data:         10M
> user_data_keyword: 40M
> keyword:            4M

This sounds like a perfect candidate for a summary table. You should
read Jonathan Gardner's writeup about materialized views. Depending on
your requirements, you'll either need to build triggers or a periodic
summarization you run. This sounds like a "top N" report so a periodic
update out to work.

http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

Tony Wasson