Re: SELECT syntax question - combining COUNT and DISTINCT

On 1 Oct 2003 at 9:55, Cath Lawrence <Cath.Lawrence@ wrote:

 >
> >
> > A secondary question (relating to my quality control problem but not
> > the subject heading) is why I can't do this:
> > "SELECT DISTINCT chain_id FROM chain WHERE pdb_id IN (SELECT pdb_code
> > FROM pdb_entry WHERE chain_count=1);"
> > This looks OK but takes apparently forever (or until I lose patience)
> > on my Mac G4. There are about 10,000 values in that subquery, so I
> > didn't expect it to be lightning fast, but it seems ridiculous.
 Try indexing the column in the where clause

> >
> >
> > Relevant snippets of table:
> > pdb_entry - 19,587 entries
> > ---------
> > pdb_code is KEY, CHAR(4), unique, indexed
> > chain_count is INTEGER
> >
> > chain - 40,844 entries
> > -----
> > pdb_id is CHAR(4) Foreign key, indexed
> > chain_id is CHAR(1)
> >
> >
> >
> > Cath Lawrence,                       Cath.Lawrence@anu.edu.au
> > Senior Scientific Programmer,  Centre for Bioinformation Science,
> > John Curtin School of Medical Research (room 4088)
> > Australian National University,  Canberra ACT 0200
> > ph: (02) 61257959   mobile: 0421-902694   fax: (02) 61252595
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>