Can PostgreSQL index array columns?

by

Yes you can index an array, but you have to use the array operators and the GIN-index type.

Example:

    CREATE TABLE "Test"("Column1" int[]);
    INSERT INTO "Test" VALUES ('{10, 15, 20}');
    INSERT INTO "Test" VALUES ('{10, 20, 30}');

    CREATE INDEX idx_test on "Test" USING GIN ("Column1");

    -- To enforce index usage because we have only 2 records for this test... 
    SET enable_seqscan TO off;

    EXPLAIN ANALYZE
    SELECT * FROM "Test" WHERE "Column1" @> ARRAY[20];

Result:

Bitmap Heap Scan on "Test"  (cost=4.26..8.27 rows=1 width=32) (actual time=0.014..0.015 rows=2 loops=1)
  Recheck Cond: ("Column1" @> '{20}'::integer[])
  ->  Bitmap Index Scan on idx_test  (cost=0.00..4.26 rows=1 width=0) (actual time=0.009..0.009 rows=2 loops=1)
        Index Cond: ("Column1" @> '{20}'::integer[])
Total runtime: 0.062 ms

Note

it appears that in many cases the gin__int_ops option is required 

create index <index_name> on <table_name> using GIN (<column> gin__int_ops)

I have not yet seen a case where it would work with the && and @> operator without the gin__int_ops options

More Related Contents:

  1. Unnest multiple arrays in parallel
  2. Return rows matching elements of input array in plpgsql function
  3. Can PostgreSQL have a uniqueness constraint on array elements?
  4. Loop over array dimension in plpgsql
  5. Unnest array by one level
  6. Compare arrays for equality, ignoring order of elements
  7. Are there other common “c-like” or non “c-like” languages with non zero index array position? [closed]
  8. Can Excel’s INDEX function return array?
  9. Return Index of an Element in an Array Excel VBA
  10. Using jsonb_set() for updating specific jsonb array value
  11. Why array indexes are zero based in most programming languages?
  12. In an assignment A(:) = B, the number of elements in A and B must be the same
  13. Easiest way to check for an index or a key in an array?
  14. Iterating over integer[] in PL/pgSQL
  15. jsonb vs jsonb[] for multiple addresses for a customer
  16. Creating a specific pattern with two-dimensional array in scala
  17. maximize the top element of the stack after performing exactly K operations
  18. How to split a string into an array in Bash?
  19. How to sort an array in Bash
  20. When to use a linked list over an array/array list?
  21. What is the proper way to initialize a fixed length array?
  22. Finding sum of elements in Swift array
  23. Remove empty strings from array while keeping record Without Loop?
  24. Postgres query optimization (forcing an index scan)
  25. Remove a field from all elements in array in mongodb [duplicate]
  26. $in requires an array as a second argument, found: missing
  27. Is there a convenient way to apply a lookup table to a large array in numpy?
  28. PowerShell array initialization
  29. Simple VBA array join not working
  30. ReDim Preserve “Subscript Out of Range”

Leave a Comment