Received: from smtp-relay1.noc.dsvr.net (virus-1.in.noc.dsvr.net [212.69.192.254]) by postgresql.org (Postfix) with ESMTP id B9B62475E0E for ; Mon, 25 Mar 2002 15:16:32 -0500 (EST) Received: from [212.69.206.2] (helo=fullerruss.dsvr.co.uk) by smtp-relay1.noc.dsvr.net with esmtp (Exim 3.34 #1) id 16patM-0001PP-00 for pgsql-sql@postgresql.org; Mon, 25 Mar 2002 20:16:32 +0000 Received: from there (host213-122-176-3.in-addr.btopenworld.com [213.122.176.3]) by fullerruss.dsvr.co.uk (8.11.3/8.11.1) with SMTP id g2PKGVg16516 for ; Mon, 25 Mar 2002 20:16:31 GMT Message-Id: <200203252016.g2PKGVg16516@fullerruss.dsvr.co.uk> Content-Type: text/plain; charset="iso-8859-1" From: Shane Wright Reply-To: me@shanewright.co.uk To: pgsql-sql@postgresql.org Subject: quickest query to check a table for existance of a value in a field Date: Mon, 25 Mar 2002 20:16:28 +0000 X-Mailer: KMail [version 1.3.2] MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/324 X-Sequence-Number: 7200 Hi I have a reasonable sized table (~2m rows), with an indexed field for which there are ~200 possible values. I want a quick way to find out if there are any rows matching a specified value for this indexed field. I dont care how many there are or what their properties are, just if there are any or not. Here's a quick example: CREATE TABLE mytable(c1 INT4 PRIMARY KEY, c2 INT2); CREATE INDEX myindex ON mytable(c2); SELECT COUNT(c2) AS num FROM mytable WHERE c2=1234; The time it takes to execute the above query appears to be of the order O(n) - is there an O(1) query to do it - given that I only want to know if num>0 or if num=0? (the function COUNT() doesn't have to be used). Any help appreciated, Thanks -- Shane