Postgres - Performance of large jsonb column

Your query:

select * from T where payload->'stakeholders'->'person'->'taxId' @> '"123456789"';

does not work. This is because 'stakeholders' is array. Worked query is:

select * from T where payload->'stakeholders' @> '[{"person": {"taxId": "54"}}]'::jsonb

But in this case postgres can use use index on whole stakeholders.

                                                       QUERY PLAN                                                       ------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on t  (cost=1388.08..1425.90 rows=10 width=36) (actual time=1.959..1.959 rows=1 loops=1)   Recheck Cond: ((payload -> 'stakeholders'::text) @> '[{"person": {"taxId": "54"}}]'::jsonb)   Heap Blocks: exact=1   ->  Bitmap Index Scan on t_expr_idx3  (cost=0.00..1388.08 rows=10 width=0) (actual time=1.946..1.946 rows=1 loops=1)         Index Cond: ((payload -> 'stakeholders'::text) @> '[{"person": {"taxId": "54"}}]'::jsonb) Planning time: 0.071 ms Execution time: 1.978 ms

For using more specific index I use modified approach by: How do you create a Postgresql JSONB array in array index?

CREATE OR REPLACE FUNCTION extract_taxids(a_json jsonb).RETURNS jsonb AS $BODY$     SELECT jsonb_agg(j) FROM (SELECT jsonb_array_elements(a_json->'stakeholders')->'person'->'taxId' AS j) AS j$BODY$ LANGUAGE sql IMMUTABLE;CREATE INDEX ON T USING gin (extract_taxids(payload));

And voila:

EXPLAIN ANALYZE select * from T where extract_taxids(payload) @> '["54"]';                                                           QUERY PLAN                                                           -------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on t  (cost=12.08..52.38 rows=10 width=36) (actual time=0.101..0.102 rows=1 loops=1)   Recheck Cond: (extract_taxids(payload) @> '["54"]'::jsonb)   Heap Blocks: exact=1   ->  Bitmap Index Scan on t_extract_taxids_idx  (cost=0.00..12.07 rows=10 width=0) (actual time=0.008..0.008 rows=1 loops=1)         Index Cond: (extract_taxids(payload) @> '["54"]'::jsonb) Planning time: 0.128 ms Execution time: 0.117 ms