You might be better off with something more like this: SELECT COUNT(P.*) AS p_count, P.p_product_category_id FROM p_product P WHERE P.p_product_category_id IN (SELECT DISTINCT id FROM p_product_category) GROUP BY P.p_product_category_id;
obviously tailored to YOUR schema... not mine... -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762 "David Garamond" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > # 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
