Message-ID: <40266D61.3070304@zara.6.isreserved.com>
Date: Mon, 09 Feb 2004 00:09:53 +0700
From: David Garamond <lists@zara.6.isreserved.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6b) Gecko/20031208
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: determining how many products are in how many categories
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Spam: No ; 0.0 / 5.0

# product table (simplified):
create table p (
   id char(22) not null primary key,
   name text,
   desc text
);

# product category table (simpl.):
create table pc (
   id char(22) not null primary key,
   name text,
   desc text
);

# table that maps products into categories:
create table p_pc (
   id char(22) not null primary key,
   pid char(22) not null references p(id),
   pcid char(22) not null references pc(id)
);
create index i_ppc_pid on p_pc(pid);
create index i_ppc_pcid on p_pc(pcid);
create unique index i_ppc_pid_pcid on p_pc(pid, pcid);

There are about 50k rows in p, 40k in pc, and 140k in p_pc. Most 
products are only placed in 1 category, some in 2, fewer in 3, and fewer 
stills in 4, 5, 6 categories.

I want to know how many products are placed in 1 category, how many in 
2, and so on. Here's my query:

select count(pid),num_cat from (
   select pid,count(cid) as num_cat
   from ppc group by pid) as f
group by num_cat;

A sample output (4000 products are categorized in 5 different places, 
4998 in 4, and so on):

  count | num_cat
-------+---------
   4000 |       5
   4998 |       4
   7502 |       3
  10001 |       2
  17499 |       1
(5 rows)

However, this query is taking about 2.5 minutes. Any idea on how to make 
it faster?

-- 
dave