From: Tony Wasson <ajwasson@gmail.com>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: 3 tables, slow count(*), order by Seq Scan in Query Plan
Date: Fri, 27 May 2005 08:18:44 -0700
Message-ID: <6d8daee3050527081834321a35@mail.gmail.com> (raw)
In-Reply-To: <20050527035634.49305.qmail@web31110.mail.mud.yahoo.com>
References: <20050527035634.49305.qmail@web31110.mail.mud.yahoo.com>
On 5/26/05, ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com> wrote:
> Hello,
>
> 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).
>
> My slow query is this:
> ----------------------
> SELECT keyword.name, count(*)
> FROM user_data, user_data_keyword, keyword
> WHERE (user_data.user_id = 1)
> AND (user_data.id = user_data_keyword.user_data_id)
> AND (user_data_keyword.keyword_id = keyword.id)
> GROUP BY keyword.name
> ORDER BY COUNT(*) DESC LIMIT 10;
<SNIP>
> Is there any way of speeding up my query?
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajwasson@gmail.com, ogjunk-pgjedan@yahoo.com
Subject: Re: 3 tables, slow count(*), order by Seq Scan in Query Plan
In-Reply-To: <6d8daee3050527081834321a35@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox