Skip site navigation (1) Skip section navigation (2)

Re: 3 tables, slow count(*), order by Seq Scan in Query Plan

From: Tony Wasson <ajwasson(at)gmail(dot)com>
To: ogjunk-pgjedan(at)yahoo(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: 3 tables, slow count(*), order by Seq Scan in Query Plan
Date: 2005-05-27 15:18:44
Message-ID: 6d8daee3050527081834321a35@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On 5/26/05, ogjunk-pgjedan(at)yahoo(dot)com <ogjunk-pgjedan(at)yahoo(dot)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

In response to

pgsql-sql by date

Next:From: Ying LuDate: 2005-05-27 15:48:39
Subject: SQL command Error: "create table ... Like parentTable including defaults"
Previous:From: Mariam AbachaDate: 2005-05-27 11:31:05
Subject: Assistance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group