Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 1AAB32E0038 for ; Wed, 16 Apr 2008 05:50:08 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84464-02 for ; Wed, 16 Apr 2008 05:50:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mx01.bfk.de (mx01.bfk.de [193.227.124.2]) by developer.postgresql.org (Postfix) with ESMTP id 73F882E0030 for ; Wed, 16 Apr 2008 05:50:02 -0300 (ADT) Received: from mx00.int.bfk.de ([10.119.110.2]) by mx01.bfk.de with esmtps (TLS-1.0:RSA_AES_256_CBC_SHA1:32) id 1Jm3L2-0000JI-VK for pgsql-sql@postgresql.org; Wed, 16 Apr 2008 10:49:56 +0200 Received: from fweimer by bfk.de with local id 1Jm3L5-0001fU-ID for pgsql-sql@postgresql.org; Wed, 16 Apr 2008 10:49:59 +0200 To: pgsql-sql@postgresql.org Subject: Index on elements of an array From: Florian Weimer Date: Wed, 16 Apr 2008 10:49:59 +0200 Message-ID: <82mynub2vc.fsf@mid.bfk.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/93 X-Sequence-Number: 30632 Is it possible to create an index on the elements of an array, or a functional index on a set-returning function? The index only needs to speed up queries for specific elements (using a simple membership test, position in the array does not matter) and perhaps range queries. The indexed types include integers and text strings, and possibly more (IP addresses, for instance). The number of elements per indexed array rarely exceeds 4, I think. I fear that the distribution of values makes the intarray module and full text search inappropriate choices (lots of values unique to a specific row). --=20 Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstra=DFe 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99