Saturday, March 19, 2016

GIN indexing an array of enums

GIN indexes of arrays can be pretty useful. And arrays of enums can also be pretty useful. Unfortunately, there is no built in GIN indexing of arrays of enums, and in fact there is no pseudo-type for arrays of enums. So, while we can declare an operator class for supporting a type of "anyenum", which I did in my recent work on btree_gin and btree_gist support for enums, we can't declare one for support of "anyenum[]".

However, we can declare one for support of a concrete enum type's array type. And all the pieces are already there.

After a lot of reading and experimentation, Here's what I found that seems to work just like the builtin GIN array operator classes.

Given an enum type of animal, you can declare an operator class for its array type like this (note how almost completely generic this is):

create operator class _animal_ops 
    default for type public.animal[] 
    using gin 
    family array_ops as  
        function 1 enum_cmp(anyenum,anyenum), 
        function 2 pg_catalog.ginarrayextract(anyarray, internal), 
        function 3 ginqueryarrayextract(anyarray, internal, smallint, internal, 
                                        internal, internal, internal), 
        function 4 ginarrayconsistent(internal, smallint, anyarray, integer, 
                                      internal, internal, internal, internal),
        function 6 ginarraytriconsistent(internal, smallint, anyarray, integer, 
                                         internal, internal, internal), 
    storage oid ;

and here it is working:
andrew=# \d animal_classes
 Table "public.animal_classes"
 Column  |   Type   | Modifiers 
---------+----------+-----------
 class   | integer  | 
 animals | animal[] | 

andrew=# select * from animal_classes where '{lion}' <@ (animals);
 class |   animals    
-------+--------------
     1 | {lion,zebra}

andrew=# set enable_seqscan = off;
SET
andrew=# create index animal_classes_animals_idx on animal_classes using gin(animals);
CREATE INDEX
andrew=# explain (costs off) select * from animal_classes where '{lion}' <@ (animals);
                      QUERY PLAN                       
-------------------------------------------------------
 Bitmap Heap Scan on animal_classes
   Recheck Cond: ('{lion}'::animal[] <@ animals)
   ->  Bitmap Index Scan on animal_classes_animals_idx
         Index Cond: ('{lion}'::animal[] <@ animals)
(4 rows)

andrew=# select * from animal_classes where '{lion}' <@ (animals);
 class |   animals    
-------+--------------
     1 | {lion,zebra}
(1 row)

andrew=# 

No comments:

Post a Comment