0

In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index?

SET enable_seqscan to FALSE;
EXPLAIN ANALYZE SELECT * FROM items WHERE int_array_cast(metadata->>'types') @> '{52, 53}'

Output of EXPLAIN

Seq Scan on items  (cost=10000000000.00..10000000016.07 rows=1 width=2391) (actual time=0.073..0.117 rows=1 loops=1)
  Filter: (int_array_cast((metadata ->> 'types'::text)) @> '{10,14}'::integer[])
  Rows Removed by Filter: 37
Planning Time: 0.201 ms
Execution Time: 0.197 ms

Table Structure

CREATE TABLE "items" ( 
    "item_uuid" UUid NOT NULL,
    "metadata" JSONB,
    PRIMARY KEY ("item_uuid") 
);

int_array_cast function defintion

CREATE OR REPLACE FUNCTION int_array_cast(TEXT) RETURNS INT[] 
AS 
$$
  SELECT CAST($1 AS INT[])
$$
IMMUTABLE
LANGUAGE SQL
RETURNS NULL ON NULL INPUT

Index created using GIN

CREATE INDEX IF NOT EXISTS items_metadata_dok_index ON items USING GIN(int_array_cast(metadata->>'types'))

Sample items in the table

item_uuid | metadata
--------------------------------------------------
        1 | {"types":"{1,2}", "name": "item_name1"}
        2 | {"types":"{10,11}", "name": "item_name2"}
        3 | {"types":"12", "name": "item_name3"}
        3 | {"name": "item_name4"}
  • Note that with just 4 rows, the database will hardly ever use an index to begin with. – a_horse_with_no_name Apr 3 at 5:42
  • Why aren't you storing your array as a proper array in JSON? E.g.: "types": [1,2] instead of a string of comma separated elements that contains curly braces? – a_horse_with_no_name Apr 3 at 6:01
  • Your exact example works for me in 9.6. I get a bitmap index scan. Except I had to change item_uuid to type integer, because the data you show in the table is not value for type uuid. – jjanes Apr 3 at 16:17
  • @a_horse_with_no_name, I assume that is why "SET enable_seqscan to FALSE;" was specified. We certainly don't want him to paste 100,000 rows into his question, so tricks like this are necessary and appropriate. – jjanes Apr 3 at 16:22
  • @jjanes: but explain analyze should be run on a sufficiently large table. The one used to produce that plan only contains 38 rows – a_horse_with_no_name Apr 3 at 20:26
0

If you want an index to be used for searching in an array, you need to first store the values as a proper array, not as a string with comma separated values that are enclosed with curly braces, e.g: {"types": [1,2], "name": "item_name1"}

Then you need to access the value as JSONB, not as text. So you need to use -> not ->>.

SELECT * 
FROM items
WHERE metadata -> 'types'  @> '[52,53]'::jsonb

And finally you need to index the types array to make that work, not the complete content of the metadata column:

create index idx_metadata_types on items using gin  ((metadata -> 'types'));

To test the index usage, you need a substantially larger table than the one you have with just 38 rows.

Using this query:

explain (analyze, buffers)
SELECT * 
FROM items
WHERE metadata -> 'types' @> '[52,53]'::jsonb

On a table with 100000 rows I then get the following plan (using Postgres 11 on my Windows 10 laptop):

Bitmap Heap Scan on items  (cost=5.38..103.08 rows=100 width=78) (actual time=0.315..0.422 rows=54 loops=1)
  Recheck Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
  Heap Blocks: exact=51
  Buffers: shared hit=57
  ->  Bitmap Index Scan on idx_metadata_types  (cost=0.00..5.35 rows=100 width=0) (actual time=0.298..0.298 rows=54 loops=1)
        Index Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
        Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.453 ms

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.