jsonb case insensitive search

Hi

The goal would be to be able to search case insensitive by any key:value combined with some other columns like outlined below, but initially would be Company:CompuTestSystems

A sample would be

SELECT *
FROM cfg_files_data
WHERE cfg_files_data.show_id = 32
AND cfg_files_data.file_id = 123
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;

- Table definition
Table "csischema.cfg_files_data"
Column | Type | Modifiers
--------------------+--------------------------+-----------------------------------------------------------------------
file_data_id | bigint | not null default nextval('cfg_files_data_file_data_id_seq'::regclass)
file_id | bigint | not null
show_id | bigint | not null
file_data_record | jsonb | not null
additional_info | jsonb |
file_data_add_by | character varying(100) | not null
file_data_add_date | timestamp with time zone | not null default now()
Indexes:
"cfg_files_data_pkey" PRIMARY KEY, btree (file_data_id, show_id)

- show_id, file_id data distribution

select show_id, file_id, count(*) from cfg_files_data group by show_id, file_id order by 1;
show_id | file_id | count
---------+---------+-------
4 | 9 | 3
4 | 68 | 22
4 | 2 | 6
6 | 3 | 13
6 | 5 | 215
13 | 13 | 13
13 | 8 | 22
21 | 11 | 13
21 | 10 | 22
26 | 12 | 13
30 | 16 | 6
32 | 123 | 53731
35 | 125 | 5
40 | 122 | 53731
46 | 69 | 4
46 | 64 | 4
46 | 67 | 4
46 | 70 | 4

For the example mentioned

SELECT *
FROM cfg_files_data
WHERE cfg_files_data.show_id = 32
AND cfg_files_data.file_id = 123
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;

create index cfg_files_data_record_idx on cfg_files_data (show_id, file_id, lower(file_data_record::text));

SELECT *
FROM cfg_files_data
WHERE cfg_files_data.show_id = 35
AND cfg_files_data.file_id = 125
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using cfg_files_data_record_idx on cfg_files_data (cost=0.54..8.57 rows=1 width=359) (actual time=0.287..0.305 rows=5 loops=1)
Index Cond: ((show_id = 35) AND (file_id = 125))
Filter: (lower((file_data_record ->> 'Company'::text)) = 'computestsystems'::text)
Planning time: 0.271 ms
Execution time: 0.370 ms
(5 rows)

SELECT *
FROM cfg_files_data
WHERE cfg_files_data.show_id = 32
AND cfg_files_data.file_id = 123
AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on cfg_files_data (cost=0.00..7276.20 rows=134 width=359) (actual time=194.817..194.848 rows=2 loops=1)
Filter: ((show_id = 32) AND (file_id = 123) AND (lower((file_data_record ->> 'Company'::text)) = 'computestsystems'::text))
Rows Removed by Filter: 107829
Planning time: 1.006 ms
Execution time: 194.905 ms

Shall I understand that prior to apply the lower((file_data_record ->> 'Company'::text)) = 'computestsystems'::text) filtering, it goes by show_id and field_id and for 32,123 since it is way above 5% it gets into a seq scan ? Although the end result is just 2 rows

SET enable_seqscan = OFF;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on cfg_files_data (cost=2485.18..8544.40 rows=134 width=359) (actual time=173.314..173.317 rows=2 loops=1)
Recheck Cond: (show_id = 32)
Filter: ((file_id = 123) AND (lower((file_data_record ->> 'Company'::text)) = 'computestsystems'::text))
Rows Removed by Filter: 53729
Heap Blocks: exact=2437
-> Bitmap Index Scan on cfg_files_data_pkey (cost=0.00..2485.15 rows=53743 width=0) (actual time=12.195..12.195 rows=53731 loops=1)
Index Cond: (show_id = 32)
Planning time: 0.232 ms
Execution time: 173.392 ms
(9 rows)

And that got me to look at

SELECT *
FROM cfg_files_data
WHERE
lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
;

drop index cfg_files_data_record_idx ;
create index cfg_files_data_record_idx on cfg_files_data (lower(file_data_record::text));
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on cfg_files_data (cost=0.00..6737.04 rows=539 width=359) (actual time=194.382..194.468 rows=8 loops=1)
Filter: (lower((file_data_record ->> 'Company'::text)) = 'computestsystems'::text)
Rows Removed by Filter: 107823
Planning time: 0.421 ms
Execution time: 194.539 ms

Well the end result is 8 rows from a total of 100k+

Not sure why the index is ignored

But is is possible to go for a broader search, aka being able to search by any key:value , efficient and case insensitive ? What am I missing in this picture ?

Thanks for help

Armand