Thread: determining how many products are in how many categories
# 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 referencespc(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
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" <lists@zara.6.isreserved.com> wrote in message news:40266D61.3070304@zara.6.isreserved.com... > # 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 >
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" <lists@zara.6.isreserved.com> wrote in message news:40266D61.3070304@zara.6.isreserved.com... > # 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 >