Sorry for missing analyze and buffers, we did only had these plans at the time, providing ones performed with such:
When it does us an index:
----------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=255.29..329.26 rows=21 width=0) (actual time=8.023..8.025 rows=1 loops=1)
Buffers: shared hit=54 read=6
I/O Timings: read=7.094
-> Bitmap Heap Scan on account_user (cost=255.29..16293.12 rows=4553 width=0) (actual time=8.022..8.023 rows=1
loops=1)
Recheck Cond: (private_metadata @> '{"somekey": "somevalue"}'::jsonb)
Heap Blocks: exact=2
Buffers: shared hit=54 read=6
I/O Timings: read=7.094
-> Bitmap Index Scan on user_p_meta_idx (cost=0.00..254.15 rows=4553 width=0) (actual time=7.985..7.985
rows=2loops=1) |
Index Cond: (private_metadata @> '{"somekey": "somevalue"}'::jsonb)|
Buffers: shared hit=52 read=6
I/O Timings: read=7.094
Planning Time: 1.134 ms
Execution Time: 8.065 ms
----------------------------------------------------------------------------------------------------------------------------------+
When it does not:
----------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.00..1184.30 rows=21 width=4) (actual time=1567.136..1619.956 rows=1 loops=1)
Buffers: shared hit=199857
-> Seq Scan on account_user (cost=0.00..256768.27 rows=4553 width=4) (actual time=1567.135..1619.953 rows=1
loops=1)
Filter: (private_metadata @> '{"somekey": "somevalue"}'::jsonb)
Rows Removed by Filter: 4592408
Buffers: shared hit=199857
Planning Time: 0.072 ms
Execution Time: 1619.972 ms
----------------------------------------------------------------------------------------------------------------------------------+
> Should we assume that not using the index is much slower (otherwise, why would you be asking the question?)?
Yes, the issue is the sequence scan being expensive and slow.