Re: Drastic select count performance hit when jsonb GIN indices are present

[Maxim Boguk <maxim.boguk@xxxxxxxxx>]

   ->  Bitmap Heap Scan on myevents  (cost=35.80..3615.09 rows=3716 width=0) (actual time=351.510..77669.907 rows=1417152 loops=1)

         Recheck Cond: (event @> '{"event": "delivered"}'::jsonb)

         Heap Blocks: exact=298362

​​

         Buffers: shared hit=1 read=298589

​...​

 Execution time: 80986.340 ms

 

   ->  Bitmap Heap Scan on

​​

myevents  (cost=42.80..3622.09 rows=3716 width=0) (actual time=534.816..78526.944 rows=1417152 loops=1)

         Recheck Cond: (event @> '{"event": "delivered"}'::jsonb)

         Heap Blocks: exact=298362

         Buffers: shared hit=383 read=299133

​...

 Execution time: 81898.578 ms

​Hi Anton,

What you see there (i think) - it's a performance hit of random disk read for non-cached database.

Try increase a shared buffers to value when table and index could fit into, and redo queries few time until you see something like
Buffers: shared hit=bigvalue read=0 and compare performance, it might change timing quite a lot.

Also, I recommend set track_io_timing=on in postgresql.conf and  after it use explain (analyze, buffers, timing) to see check how much time database spent doing IO operations.

Also try perform vacuum analyze ​myevents; before testing because it seems that you have no up to date visibility map on the table.

However, even in fully cached case selecting 40% on the table rows almost always will be faster via sequential scan, so I don't expect miracles.

--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@xxxxxxxxx
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."