Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M38004=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1Xu0Ss-00028V-8R
 for pgsql-sql@arkaria.postgresql.org; Thu, 27 Nov 2014 14:54:38 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M38004=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1Xu0Sr-0004Fr-PZ
 for pgsql-sql@arkaria.postgresql.org; Thu, 27 Nov 2014 14:54:37 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <tgl@sss.pgh.pa.us>) id 1Xu0Sq-0004Fj-Qd
 for pgsql-sql@postgresql.org; Thu, 27 Nov 2014 14:54:36 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
 by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <tgl@sss.pgh.pa.us>) id 1Xu0Sn-000137-M7
 for pgsql-sql@postgresql.org; Thu, 27 Nov 2014 14:54:35 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
 by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id sAREsU1B003677;
 Thu, 27 Nov 2014 09:54:30 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tim Dudgeon <tdudgeon.ml@gmail.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Querying with arrays
In-reply-to: <547722A7.4040702@gmail.com>
References: <547722A7.4040702@gmail.com>
Comments: In-reply-to Tim Dudgeon <tdudgeon.ml@gmail.com>
 message dated "Thu, 27 Nov 2014 13:09:59 +0000"
Date: Thu, 27 Nov 2014 09:54:30 -0500
Message-ID: <3676.1417100070@sss.pgh.pa.us>
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Tim Dudgeon <tdudgeon.ml@gmail.com> writes:
> I'm considering using arrays to handle managing "lists" of rows (I know 
> this may not be the best approach, but bear with me).

> I create a table for my lists like this:**

> create table lists (
>   id SERIAL PRIMARY KEY,
>   hits INTEGER[] NOT NULL
> );

> Then I can insert the results of a query into that table as a new list 
> of hits

> INSERT INTO lists (hits)
> SELECT array_agg(id)
> FROM some_table
> WHERE ...;

> Now the problem part. How to best use that array of primary key values 
> to restore the data at a later stage. Conceptually I'm wanting this:

> SELECT * from some_table
> WHERE id <is in the list of ids in the array in the lists table>;

> These both work by are really slow:

> SELECT t1.*
> FROM some_table t1
> WHERE t1.id IN (SELECT unnest(hits) from lists WHERE id = 2);

> SELECT t1.*
> FROM some_table t1
> JOIN lists l ON t1.id = any(l.hits)
> WHERE l.id = 2;

> Is there an efficient way to do this, or is this a dead end?

You could create a GIN index on lists.hits and then do

SELECT t1.*
FROM some_table t1
JOIN lists l ON array[t1.id] <@ l.hits
WHERE l.id = 2;

How efficient that will be remains to be determined though;
if the l.id condition will eliminate a lot of matches it
could still be kind of slow.

(ISTR some talk of teaching the planner to convert =ANY(array)
conditions to this form automatically when there's a suitable
index, but for now you'd have to write it out like this.)

			regards, tom lane


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql