From: Marc-Olaf Jaschke <marc-olaf.jaschke@s24.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 10.1 \(3251\))
Subject: performance issue with bitmap index scans on huge amounts of big
 jsonb documents
Message-Id: <B944EB44-57EB-4669-9D8F-23CBB23B94FE@s24.com>
Date: Wed, 30 Nov 2016 13:26:16 +0100
To: pgsql-performance@postgresql.org
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

i have a performance issue with bitmap index scans on huge amounts of big j=
sonb documents.


=3D=3D=3D=3D=3D Background =3D=3D=3D=3D=3D=20

- table with big jsonb documents
- gin index on these documents
- queries using index conditions with low selectivity


=3D=3D=3D=3D=3D Example =3D=3D=3D=3D=3D=20

select version();=20
> PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 2012=
0313 (Red Hat 4.4.7-17), 64-bit

show work_mem;
> 1GB

-- setup test data
create table bitmap_scan_test as
select
i,
(select jsonb_agg(jsonb_build_object('x', i % 2, 'filler', md5(j::text))) f=
rom generate_series(0, 100) j) big_jsonb
from
generate_series(0, 100000) i;

create index on bitmap_scan_test using gin (big_jsonb);

analyze bitmap_scan_test;


--  query with bitmap scan
explain analyze
select
count(*)
from
bitmap_scan_test
where
big_jsonb @> '[{"x": 1, "filler": "cfcd208495d565ef66e7dff9f98764da"}]';

Aggregate  (cost=3D272.74..272.75 rows=3D1 width=3D8) (actual time=3D622.27=
2..622.272 rows=3D1 loops=3D1)
  ->  Bitmap Heap Scan on bitmap_scan_test  (cost=3D120.78..272.49 rows=3D1=
00 width=3D0) (actual time=3D16.496..617.431 rows=3D50000 loops=3D1)
        Recheck Cond: (big_jsonb @> '[{"x": 1, "filler": "cfcd208495d565ef6=
6e7dff9f98764da"}]'::jsonb)
        Heap Blocks: exact=3D637
        ->  Bitmap Index Scan on bitmap_scan_test_big_jsonb_idx  (cost=3D0.=
00..120.75 rows=3D100 width=3D0) (actual time=3D16.371..16.371 rows=3D50000=
 loops=3D1)
              Index Cond: (big_jsonb @> '[{"x": 1, "filler": "cfcd208495d56=
5ef66e7dff9f98764da"}]'::jsonb)
Planning time: 0.106 ms
Execution time: 622.334 ms


perf top -p... shows heavy usage of pglz_decompress:

Overhead  Shared Object     Symbol
  51,06%  postgres          [.] pglz_decompress
   7,33%  libc-2.12.so      [.] memcpy
...

=3D=3D=3D=3D=3D End of example =3D=3D=3D=3D=3D=20=20


I wonder why bitmap heap scan adds such a big amount of time on top of the =
plain bitmap index scan.=20
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?


Regards,
Marc-Olaf


[1] (http://dba.stackexchange.com/questions/106264/recheck-cond-line-in-que=
ry-plans-with-a-bitmap-index-scan)

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance