Обсуждение: Вопрос по intarrays и GIN/GIST индексам

Собственно первый вопрос почему GIN индексы по intarray на postgres 8.3 просто не работают :(

vision=# \d vision_video
                                            Таблица "public.vision_video"
    Колонка   |             Тип             |                             Модификаторы
-------------+-----------------------------+-----------------------------------------------------------------------
  id          | integer                     | not null default nextval(('public.documents_id_seq'::text)::regclass)
  class       | character varying(48)       | not null
  topic       | integer[]                   |
...

vision=# SELECT count(*) from vision_video;
  count
-------
  90384
(1 запись)

vision=# CREATE INDEX CONCURRENTLY vision_video_topic_idx ON vision_video USING GIN(topic);
CREATE INDEX
vision=# EXPLAIN ANALYZE SELECT  * FROM vision_video WHERE topic && ARRAY['112']::integer[];
                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------
  Seq Scan on vision_video  (cost=0.00..16569.67 rows=90 width=1248) (actual time=0.089..142.912 rows=990 loops=1)
    Filter: (topic && ('{112}'::text[])::integer[])
  Total runtime: 147.815 ms
(3 rows)

там же через минуту:
vision=# CREATE INDEX CONCURRENTLY vision_video_topic1_idx ON vision_video USING GIST(topic);
CREATE INDEX
vision=# EXPLAIN ANALYZE SELECT  * FROM vision_video WHERE topic && ARRAY['112']::integer[];
                                                                   QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using vision_video_topic1_idx on vision_video  (cost=0.00..112.24 rows=90 width=1248) (actual
time=0.084..7.374rows=990 loops=1) 
    Index Cond: (topic && ('{112}'::text[])::integer[])
  Total runtime: 12.186 ms
(3 rows)

Это первое... по логике GIN индекс должен быть быстрее но этого не наблюдается и он вообще не желает использоваться.
(analyze/reindex ситуацию не меняют)






Второе:
На аналогичной таблице имеем еще один intarray со следующими данными:

Опять же:
vision=# SELECT count(*) from vision_video;
  count
-------
  90384
(1 запись)

vision=# SELECT count(*) from vision_video where tags is not null and tags <> '{}';
  count
-------
  18115
(1 запись)
(тоесть совсем не много реально заполненных данных)

Средняя длинна тагов тоже маленькая:

vision=# SELECT avg(array_upper(tags,1)) from vision_video where tags is not null and tags <> '{}';
         avg
--------------------
  2.6531603643389456
(1 запись)

Совсем уникальных записей
vision=# SELECT count(distinct tags) from vision_video where tags is not null and tags <> '{}';
  count
-------
  10398
(1 запись)

GIN индекс по этому полю делается за 1-10 секунд... но не работает... см п1.

Создание же GIST индекса занимает черт знает сколько времени (сейчас уже вот 4 часа на серьезном сервере работает и
грузитодно ядро на 100%). 
Что делать и как вылечить ситуацию (записей смешное количество)?



--
Maxim Boguk

On Thu, 20 Mar 2008, Maxim Boguk wrote:

> Собственно первый вопрос почему GIN индексы по intarray на postgres 8.3
> просто не работают :(

а зачем тебе intarray ?

В 8.3 это тебе не нужно.
postgres=# \d tt
        Table "public.tt"
  Column |   Type    | Modifiers
--------+-----------+-----------
  a      | integer[] |
Indexes:
     "gin_idx" gin (a)

postgres=# explain analyze select * from tt where a && '{112}';
                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on tt  (cost=88.29..1417.98 rows=500 width=231) (actual time=2.406..9.584 rows=9547 loops=1)
    Recheck Cond: (a && '{112}'::integer[])
    ->  Bitmap Index Scan on gin_idx  (cost=0.00..88.17 rows=500 width=0) (actual time=1.821..1.821 rows=9547 loops=1)
          Index Cond: (a && '{112}'::integer[])
  Total runtime: 13.194 ms
(5 rows)

>
> Второе:
> На аналогичной таблице имеем еще один intarray со следующими данными:
>
> GIN индекс по этому полю делается за 1-10 секунд... но не работает... см п1.
>
> Создание же GIST индекса занимает черт знает сколько времени (сейчас уже вот
> 4 часа на серьезном сервере работает и грузит одно ядро на 100%).
> Что делать и как вылечить ситуацию (записей смешное количество)?

http://www.postgresql.org/docs/current/static/intarray.html
Two GiST index operator classes are provided: gist__int_ops (used by default) is suitable for small and medium-size
arrays,while gist__intbig_ops uses a larger signature and is more suitable for indexing large arrays. 

В твоем случае надо использовать gist__intbig_ops


     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83