By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE leads (id int, user_details jsonb);
INSERT INTO leads VALUES (generate_series(1,10000),('{"name" : "'||random()::text||'", "country" : "IN", "phone_no" : "123456789"}')::jsonb);
INSERT INTO leads VALUES (42,'{"name" : "John Doe", "country" : "IN", "phone_no" : "123456789"}'::jsonb);
INSERT INTO leads VALUES (generate_series(1,10000),('{"name" : "'||random()::text||'", "country" : "IN", "phone_no" : "123456789"}')::jsonb);
CREATE TABLE
INSERT 0 10000
INSERT 0 1
INSERT 0 10000
EXPLAIN (ANALYSE,COSTS OFF)
SELECT * FROM leads
WHERE lower(user_details->>'name') ~~ '%doe%'
| QUERY PLAN |
|---|
| Seq Scan on leads (actual time=8.053..70.857 rows=1 loops=1) |
| Filter: (lower((user_details ->> 'name'::text)) ~~ '%doe%'::text) |
| Rows Removed by Filter: 20000 |
| Planning Time: 0.320 ms |
| Execution Time: 70.887 ms |
EXPLAIN
CREATE INDEX leads_user_details_name_idx ON leads
USING gin( lower((user_details->>'name')) gin_trgm_ops);
CREATE INDEX
EXPLAIN (ANALYSE,COSTS OFF)
SELECT * FROM leads
WHERE lower(user_details->>'name') ~~ '%doe%'
| QUERY PLAN |
|---|
| Bitmap Heap Scan on leads (actual time=0.200..0.201 rows=1 loops=1) |
| Recheck Cond: (lower((user_details ->> 'name'::text)) ~~ '%doe%'::text) |
| Heap Blocks: exact=1 |
| -> Bitmap Index Scan on leads_user_details_name_idx (actual time=0.184..0.185 rows=1 loops=1) |
| Index Cond: (lower((user_details ->> 'name'::text)) ~~ '%doe%'::text) |
| Planning Time: 1.550 ms |
| Execution Time: 0.259 ms |
EXPLAIN