Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE json_test (id serial, test JSONB);
INSERT INTO json_test SELECT generate_series(0, 500000), '{"name": "john"}';
-- Insert into the middle
INSERT INTO json_test(test) VALUES ('{"name": "jane"}');
INSERT INTO json_test SELECT generate_series(0, 500000), '{"name": "john"}';
CREATE INDEX name ON json_test ((test->>'name'));
SELECT count(*) FROM json_test;
500001 rows affected

1 rows affected

500001 rows affected

count

1000003

-- Prewarm
SELECT * FROM json_test WHERE (test->>'name') = 'nothing' LIMIT 10;
EXPLAIN (analyze, buffers, verbose)
    SELECT * FROM json_test WHERE (test->>'name') = 'john' LIMIT 10;

QUERY PLAN

Limit  (cost=0.42..30.57 rows=10 width=36) (actual time=0.051..0.054 rows=10 loops=1)

  Output: id, test

  Buffers: shared hit=1 read=3 dirtied=1

  ->  Index Scan using name on public.json_test  (cost=0.42..15071.92 rows=5000 width=36) (actual time=0.050..0.052 rows=10 loops=1)

        Output: id, test

        Index Cond: ((json_test.test ->> 'name'::text) = 'john'::text)

        Buffers: shared hit=1 read=3 dirtied=1

Planning time: 0.045 ms

Execution time: 0.074 ms

EXPLAIN (analyze, buffers, verbose)
    SELECT * FROM json_test WHERE (test->>'name') = 'jane' LIMIT 10;

QUERY PLAN

Limit  (cost=0.42..30.57 rows=10 width=36) (actual time=0.022..0.023 rows=1 loops=1)

  Output: id, test

  Buffers: shared hit=3 read=1 dirtied=1

  ->  Index Scan using name on public.json_test  (cost=0.42..15071.92 rows=5000 width=36) (actual time=0.021..0.022 rows=1 loops=1)

        Output: id, test

        Index Cond: ((json_test.test ->> 'name'::text) = 'jane'::text)

        Buffers: shared hit=3 read=1 dirtied=1

Planning time: 0.043 ms

Execution time: 0.036 ms