agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Querying with arrays
Date: Thu, 04 Dec 2014 15:54:49 +0200
Message-ID: <548067A9.3060405@matrix.gatewaynet.com> (raw)
In-Reply-To: <548064AE.4020407@gmail.com>
References: <547722A7.4040702@gmail.com>
	<3676.1417100070@sss.pgh.pa.us>
	<548064AE.4020407@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (6+ messages)  latest in thread

Message-ID: <548067A9.3060405@matrix.gatewaynet.com>
Permalink:  ../548067A9.3060405@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/548067A9.3060405@matrix.gatewaynet.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com
  Subject: Re: Querying with arrays
  In-Reply-To: <548067A9.3060405@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox