Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M38011=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XwWrz-0007rK-Fi
 for pgsql-sql@arkaria.postgresql.org; Thu, 04 Dec 2014 13:54:59 +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+M38011=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XwWrz-0000mr-0V
 for pgsql-sql@arkaria.postgresql.org; Thu, 04 Dec 2014 13:54:59 +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 <achill@matrix.gatewaynet.com>)
 id 1XwWrx-0000mc-Uh
 for pgsql-sql@postgresql.org; Thu, 04 Dec 2014 13:54:58 +0000
Received: from adsltrust.ath.forthnet.gr ([194.219.204.174]
 helo=smadev.internal.net)
 by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <achill@matrix.gatewaynet.com>)
 id 1XwWrt-0000iL-Rx
 for pgsql-sql@postgresql.org; Thu, 04 Dec 2014 13:54:56 +0000
Received: from smadev.internal.net (smadev [10.9.200.131])
 by smadev.internal.net (8.14.7/8.14.7) with ESMTP id sB4Dsnok008065
 for <pgsql-sql@postgresql.org>; Thu, 4 Dec 2014 15:54:49 +0200 (EET)
 (envelope-from achill@matrix.gatewaynet.com)
Message-ID: <548067A9.3060405@matrix.gatewaynet.com>
Date: Thu, 04 Dec 2014 15:54:49 +0200
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64;
 rv:24.0) Gecko/20100101 Thunderbird/24.3.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Querying with arrays
References: <547722A7.4040702@gmail.com> <3676.1417100070@sss.pgh.pa.us>
 <548064AE.4020407@gmail.com>
In-Reply-To: <548064AE.4020407@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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

On 04/12/2014 15:42, Tim Dudgeon wrote:
> Looking into this further I don't seem able to get the index used.
> I created this simple example:
>
> create table lists (
>   id SERIAL PRIMARY KEY,
>   name VARCHAR(32) NOT NULL,
>   hits INTEGER[] NOT NULL
> );
>
> CREATE INDEX idx_lists_hits ON lists USING gin (hits);
>
> INSERT INTO lists (name, hits) VALUES ('list1-10', ARRAY[1,2,3,4,5,6,7,8,9,10]);
>
> explain analyze SELECT id, name FROM lists
> WHERE hits @> array[7];
>
>
> The plan for the query is this:
>
> "Seq Scan on lists  (cost=0.00..16.88 rows=3 width=86) (actual time=0.006..0.008 rows=1 loops=1)"
> "  Filter: (hits @> '{7}'::integer[])"
> "Planning time: 0.058 ms"
> "Execution time: 0.025 ms"
>
> What am I doing wrong?
>

Maybe your test table is tiny?

> Tim
>
>
>
>
> On 27/11/2014 11:54, Tom Lane wrote:
>> 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
>
>
>


-- 
Achilleas Mantzios
Head of IT DEV
IT DEPT
Dynacom Tankers Mgmt



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