GIN index on PostgreSql jsonb column not being used in queries

Question

I am using PostgreSql 9.6. (please don't ask me to upgrade - I must use 9.6)

I have a table which has a jsonb column. I have created a GIN index on this column. The table has 320,000 records. A "explain analyse" reveals that the index is not being used and a simple query takes about 3 seconds.

We have a debug logger that records anything at all but stores it as JSON in the format { "key1":"value1", "key2":"value2", ... }

We gather statistics by extracting values for keys.

The table and index are created like so:

CREATE TABLE log ( 
  id SERIAL PRIMARY KEY,
  logEntry jsonb
);

CREATE INDEX log_idx_logentry on log using gin (logentry);

I run a query that I know will return no results:

SELECT id FROM log WHERE logentry->>'modality' = 'XT'

This takes 3 seconds to run.

EXPLAIN ANALYSE SELECT id FROM log WHERE logentry->>'modality' = 'XT' produces:

 Seq Scan on log  (cost=0.00..32458.90 rows=1618 width=4) (actual time=1328.654..1328.660 rows=0 loops=1)
 Filter: ((logentry ->> 'modality'::text) = 'XT'::text)
 Rows Removed by Filter: 323527
 Planning time: 0.450 ms
 Execution time: 1328.724 ms
(5 rows)

Similar results if I write the query as:

EXPLAIN ANALYSE SELECT id FROM log WHERE logentry->'modality' @> '"XT"'::jsonb

 Seq Scan on log  (cost=0.00..32458.90 rows=324 width=4) (actual time=1421.262..1421.266 rows=0 loops=1)
   Filter: ((logentry -> 'modality'::text) @> '"XT"'::jsonb)
   Rows Removed by Filter: 323527
 Planning time: 0.080 ms
 Execution time: 1421.309 ms
(5 rows)

And, just to prove that there is something in the table,

SELECT COUNT(id) FROM log WHERE logentry->'modality' @> '"CT"'::jsonb

returns 42528

So why is the index not being used? In production we would expect the log table to contain millions of records.

The -> and ->> operators cannot be supported by a gin index. You can try e.g. where logentry @> '{"modality": "CT"}' — klin
Well that was simple enough. I'm sure I saw the syntax I was using somewhere in my searches. Oh well. Now it is using a Bitmap Heap Scan and the results for the "XT" query now return in 1.7 seconds instead of 3. Not really much of a difference. — user2577528

user2577528 user2577528 · Accepted Answer · 2019-05-23T18:36:08

klin has the correct answer. As the database gets larger, the difference in performance becomes more pronounced.

GIN index on PostgreSql jsonb column not being used in queries

1 Answers