Re: Querying with arrays
От | Achilleas Mantzios |
---|---|
Тема | Re: Querying with arrays |
Дата | |
Msg-id | 548067A9.3060405@matrix.gatewaynet.com обсуждение исходный текст |
Ответ на | Re: Querying with arrays (Tim Dudgeon <tdudgeon.ml@gmail.com>) |
Список | pgsql-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
В списке pgsql-sql по дате отправления: