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

-> 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/ <http://www.postgresql-consulting.com/>

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(dot)boguk(at)gmail(dot)com
МойКруг: 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."