From: Marc-Olaf Jaschke <marc-olaf.jaschke@s24.com>
To: pgsql-performance@postgresql.org
Subject: performance issue with bitmap index scans on huge amounts of big jsonb documents
Date: Wed, 30 Nov 2016 13:26:16 +0100
Message-ID: <B944EB44-57EB-4669-9D8F-23CBB23B94FE@s24.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
i have a performance issue with bitmap index scans on huge amounts of big jsonb documents.
===== Background =====
- table with big jsonb documents
- gin index on these documents
- queries using index conditions with low selectivity
===== Example =====
select version();
> PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (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))) from 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=272.74..272.75 rows=1 width=8) (actual time=622.272..622.272 rows=1 loops=1)
-> Bitmap Heap Scan on bitmap_scan_test (cost=120.78..272.49 rows=100 width=0) (actual time=16.496..617.431 rows=50000 loops=1)
Recheck Cond: (big_jsonb @> '[{"x": 1, "filler": "cfcd208495d565ef66e7dff9f98764da"}]'::jsonb)
Heap Blocks: exact=637
-> Bitmap Index Scan on bitmap_scan_test_big_jsonb_idx (cost=0.00..120.75 rows=100 width=0) (actual time=16.371..16.371 rows=50000 loops=1)
Index Cond: (big_jsonb @> '[{"x": 1, "filler": "cfcd208495d565ef66e7dff9f98764da"}]'::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
...
===== End of example =====
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?
Regards,
Marc-Olaf
[1] (http://dba.stackexchange.com/questions/106264/recheck-cond-line-in-query-plans-with-a-bitmap-index-s...)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: marc-olaf.jaschke@s24.com
Subject: Re: performance issue with bitmap index scans on huge amounts of big jsonb documents
In-Reply-To: <B944EB44-57EB-4669-9D8F-23CBB23B94FE@s24.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox