Re: Unexpected (bad) performance when querying indexed JSONB column

On 01.02.15 22:06, "Josh Berkus" <josh@agliodbs.com> wrote:



>Please send us the output of EXPLAIN ( ANALYZE ON, BUFFERS ON ) so that
>we can see what the query is actually doing, rather than just what the
>plan was.
>
>-- 
>Josh Berkus
>PostgreSQL Experts Inc.
>http://pgexperts.com


Sure. Here we go:

"Bitmap Heap Scan on articles  (cost=16.25..135.64 rows=33 width=427) 
(actual time=6.425..43.603 rows=18584 loops=1)"
"  Recheck Cond: (data @> ‘{"locked": true}'::jsonb)"
"  Heap Blocks: exact=1496"
"  Buffers: shared hit=1504"
"  ->  Bitmap Index Scan on idx_data  (cost=0.00..16.24 rows=33 width=0) 
(actual time=6.090..6.090 rows=18584 loops=1)"
"        Index Cond: (data @> ‘{"locked": true}'::jsonb)"
"        Buffers: shared hit=8"
"Planning time: 0.348 ms"
"Execution time: 47.788 ms"


Thanks for looking into this.

-C.