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

Поиск
Список
Период
Сортировка
От Maxim Boguk
Тема Re: Drastic select count performance hit when jsonb GIN indices are present
Дата
Msg-id CAK-MWwSU-FSFzbRie2C2xThV-xo1vbktofMfNKiobu939KLSJA@mail.gmail.com
обсуждение исходный текст
Ответ на Drastic select count performance hit when jsonb GIN indices are present  (Anton Melser <melser.anton@gmail.com>)
Ответы Re: Drastic select count performance hit when jsonb GIN indices are present  (Anton Melser <melser.anton@gmail.com>)
Список pgsql-general
Дерево обсуждения
   ->  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@gmail.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."

В списке pgsql-general по дате отправления:

Предыдущее
От: Andreas Ulbrich
Дата:
Сообщение: Re: Check constraint on foreign table using SQL function
Следующее
От: Cory Zue
Дата:
Сообщение: Re: help troubleshooting invalid page header error