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

From Marc-Olaf Jaschke
Subject Re: performance issue with bitmap index scans on huge amounts of big jsonb documents
Date
Msg-id CAP_hWuPkxz1J=To1JDFJT=hzM=+-UNFJ0w+m2WZQmB4FXuYJoQ@mail.gmail.com
Whole thread Raw
In response to Re: performance issue with bitmap index scans on huge amounts of big jsonb documents  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-performance
Thanks for the explanation!

Best Regards,
Marc-Olaf

Marc-Olaf Jaschke · Softwareentwickler
shopping24 GmbH

Werner-Otto-Straße 1-7 · 22179 Hamburg
Telefon: +49 (0) 40 6461 5830 · Fax: +49 (0) 40 64 61 7879
marc-olaf.jaschke@s24.com · www.s24.com
AG Hamburg HRB 63371
vertreten durch Dr. Björn Schäfers und Martin Mildner

2016-12-05 3:28 GMT+01:00 Jeff Janes <jeff.janes@gmail.com>:

> 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: Jeff Janes
Date:
Subject: Re: performance issue with bitmap index scans on huge amounts of big jsonb documents
Next
From: Andrey Povazhnyi
Date:
Subject: Slow query question