Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M38003=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1Xtypr-0005kV-Dt
 for pgsql-sql@arkaria.postgresql.org; Thu, 27 Nov 2014 13:10:15 +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+M38003=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1Xtypq-0005dk-R0
 for pgsql-sql@arkaria.postgresql.org; Thu, 27 Nov 2014 13:10:14 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <tdudgeon.ml@gmail.com>)
 id 1Xtypp-0005cV-Gj
 for pgsql-sql@postgresql.org; Thu, 27 Nov 2014 13:10:13 +0000
Received: from mail-wi0-x22b.google.com ([2a00:1450:400c:c05::22b])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <tdudgeon.ml@gmail.com>)
 id 1Xtyph-0005c0-4q
 for pgsql-sql@postgresql.org; Thu, 27 Nov 2014 13:10:11 +0000
Received: by mail-wi0-f171.google.com with SMTP id bs8so15671783wib.16
 for <pgsql-sql@postgresql.org>; Thu, 27 Nov 2014 05:10:02 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject
 :content-type; bh=kxAt4jC8Trv1F25HGAXUc9od8p6ETkowmUgzkYxXsGQ=;
 b=ILOtCn5nqcvfko9Rwd3U36R2jAaIwfkeDoBydJIXKWD+IBJonFK2WdxjKBdv8ODD5d
 QVwiiaCUjQBnlQ7JFdBQ+61mjBouNxbON2hd2ln+qevarug4VDLA21x5vyWNZfz0Ss+l
 MTvZ73RWhT2d+nZhJwAKC10bPj7rxB93TNKOxbuA5YNcLEGLjh/Pv3/zddnlVqrMUnsF
 AeZtMyXQqrPDDYBlqJu7DJaHmCzJwjNtekJgF9tD5zMEpzExYDPL+N1FwzaEjR8VmxTU
 y9Nk6cNX7DrLECds9dXI3eimnkGuGz+Bkydx81uJ9jHJ2ryJX863TIBM0GKLtVkyhLQ6
 RPUg==
X-Received: by 10.194.185.68 with SMTP id fa4mr57448703wjc.83.1417093802201;
 Thu, 27 Nov 2014 05:10:02 -0800 (PST)
Received: from timbomac.home
 (host86-158-181-242.range86-158.btcentralplus.com. [86.158.181.242])
 by mx.google.com with ESMTPSA id c5sm11576232wik.3.2014.11.27.05.10.00
 for <pgsql-sql@postgresql.org>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 27 Nov 2014 05:10:01 -0800 (PST)
Message-ID: <547722A7.4040702@gmail.com>
Date: Thu, 27 Nov 2014 13:09:59 +0000
From: Tim Dudgeon <tdudgeon.ml@gmail.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Querying with arrays
Content-Type: multipart/alternative;
 boundary="------------000003010803060600070100"
X-Pg-Spam-Score: -2.7 (--)
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

This is a multi-part message in MIME format.
--------------000003010803060600070100
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

Thanks
Tim







--------------000003010803060600070100
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    I'm considering using arrays to handle managing "lists" of rows (I
    know this may not be the best approach, but bear with me).<br>
    <br>
    I create a table for my lists like this:<b
      style="font-weight:normal;"
      id="docs-internal-guid-b52591dd-f159-1b1d-dee1-e0c932294de6"><span
style="font-size:15px;font-family:Arial;color:#000000;background-color:transparent;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;"></span></b><br>
    <br>
    create table lists (<br>
    &nbsp;id SERIAL PRIMARY KEY,<br>
    &nbsp;hits INTEGER[] NOT NULL<br>
    );<br>
    <br>
    Then I can insert the results of a query into that table as a new
    list of hits<br>
    <br>
    INSERT INTO lists (hits)<br>
    SELECT array_agg(id)<br>
    FROM some_table<br>
    WHERE ...;<br>
    <br>
    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:<br>
    <br>
    SELECT * from some_table <br>
    WHERE id &lt;is in the list of ids in the array in the lists
    table&gt;;<br>
    <br>
    These both work by are really slow:<br>
    <br>
    <meta charset="utf-8">
    <meta charset="utf-8">
    SELECT t1.*<br>
    FROM some_table t1<br>
    WHERE t1.id IN (SELECT unnest(hits) from lists WHERE id = 2);<br>
    <br>
    SELECT t1.*<br>
    FROM some_table t1<br>
    JOIN lists l ON t1.id = any(l.hits)<br>
    WHERE l.id = 2;<br>
    <br>
    Is there an efficient way to do this, or is this a dead end?<br>
    <br>
    Thanks<br>
    Tim<br>
    <br>
    <br>
    <br>
    <br>
    <br>
    <br>
  </body>
</html>

--------------000003010803060600070100--