Re: performance issue with bitmap index scans on huge amounts of big jsonb documents - Mailing list pgsql-performance

From Jeff Janes
Subject Re: performance issue with bitmap index scans on huge amounts of big jsonb documents
Date
Msg-id CAMkU=1y+_XTgLtA2uL01jX4=E_vFVa4YnE9_ZMA704uRLAB5yg@mail.gmail.com
Whole thread Raw
In response to performance issue with bitmap index scans on huge amounts of big jsonb documents  (Marc-Olaf Jaschke <marc-olaf.jaschke@s24.com>)
Responses Re: performance issue with bitmap index scans on huge amounts of big jsonb documents
List pgsql-performance

> big_jsonb @> '[{"x": 1, "filler": "cfcd208495d565ef66e7dff9f98764da"}]';


I wonder why bitmap heap scan adds such a big amount of time on top of the plain bitmap index scan.
It seems to me, that the recheck is active although all blocks are exact [1] and that pg is loading the jsonb for the recheck.

Is this an expected behavior?


Yes, this is expected.  The gin index is lossy.  It knows that all the elements are present (except when it doesn't--large elements might get hashed down and suffer hash collisions), but it doesn't know what the recursive structure between them is, and has to do a recheck.

For example, if you change your example where clause to:

big_jsonb @> '[{"filler": 1, "x": "cfcd208495d565ef66e7dff9f98764da"}]';

You will see that the index still returns 50,000 rows, but now all of them get rejected upon the recheck.

You could try changing the type of index to jsonb_path_ops.  In your given example, it won't make a difference, because you are actually counting half the table and so half the table needs to be rechecked.  But in my example, jsonb_path_ops successfully rejects all the rows at the index stage.

Cheers,

Jeff

pgsql-performance by date:

Previous
From: Bill Measday
Date:
Subject: Re: Substantial different index use between 9.5 and 9.6
Next
From: Marc-Olaf Jaschke
Date:
Subject: Re: performance issue with bitmap index scans on huge amounts of big jsonb documents