Received: from kakapo.susa.net (IDENT:root@kakapo.susa.net [195.226.57.21]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA57949 for ; Wed, 4 Aug 1999 17:16:09 -0400 (EDT) (envelope-from kevin@susa.net) Received: from pckevin.prv.corpfin.co.uk (online.corpfin.co.uk [195.226.57.17]) by kakapo.susa.net (8.8.7/8.8.7) with SMTP id WAA25108 for ; Wed, 4 Aug 1999 22:13:59 GMT Message-Id: <199908042213.WAA25108@kakapo.susa.net> From: Kevin Sangalee To: pgsql-sql@postgresql.org Date: Wed, 4 Aug 1999 22:08:43 +0000 Subject: Why is this sub-query select so slow? Priority: Normal Organization: SusaNET Software X-mailer: Phoenix Mail 0.92.08 Standard Edition MIME-Version: 1.0 Content-type: multipart/mixed; boundary=-----Phoenix-Boundary-07081998- -------Phoenix-Boundary-07081998- Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: Quoted-printable I am querying across two tables, each of around 35000 records. My sql is select * from fieldcounts where prodnum in (select prodnum from products where value < 150); If I execute them individually, they return immediately. When I do an explain, the fieldcounts is always accessed sequentially. It's accessed via and Index Scan when I do... select * from fieldcounts where prodnum in (65,66,45); for example Why doesn't postgresql use an index scan when the subselect is a query=3F Any ideas gratefully appreciated. -------Phoenix-Boundary-07081998---