Postgres index on JSONB field

index.md

I'm running this test because a rumor assumes there is a difference between operators and functions in postgres.

DB infos

In our table we have a field (json_aip) that contains a JSONB looking like that:

{
	"properties": {
		"pdi": {
			"contextInformation": {
				"tags": ["SOME_TAG"]
			},
	},
}

Table creation :

create table t_aip (
	json_aip jsonb,
	[...]
);

CREATE INDEX idx_aip_tags 
ON t_aip 
USING gin ((json_aip -> 'properties' -> 'pdi' -> 'contextInformation' -> 'tags'));

Original query (using operator)

EXPLAIN ANALYZE SELECT count(*)  
FROM storage.t_aip 
WHERE json_aip#>'{properties,pdi,contextInformation,tags}' ?| array['SOME_TAG']

Result:

Aggregate  (cost=27052.16..27052.17 rows=1 width=8) (actual time=488.085..488.087 rows=1 loops=1)
  ->  Seq Scan on t_aip  (cost=0.00..27052.06 rows=42 width=0) (actual time=0.134..456.978 rows=16502 loops=1)
        Filter: ((json_aip #> '{properties,pdi,contextInformation,tags}'::text[]) ?| '{SOME_TAG}'::text[])
        Rows Removed by Filter: 17511
Planning time: 23.202 ms
Execution time: 488.449 ms

Final query (with function)

EXPLAIN ANALYZE SELECT count(*)  
FROM storage.t_aip 
WHERE jsonb_exists_any(
	json_aip#>'{properties,pdi,contextInformation,tags}', 
	array['SOME_TAG']
)

Result:

QUERY PLAN
Aggregate  (cost=27087.00..27087.01 rows=1 width=8) (actual time=369.931..369.933 rows=1 loops=1)
  ->  Seq Scan on t_aip  (cost=0.00..27052.06 rows=13979 width=0) (actual time=0.173..350.437 rows=16502 loops=1)
        Filter: jsonb_exists_any((json_aip #> '{properties,pdi,contextInformation,tags}'::text[]), '{SOME_TAG}'::text[])
        Rows Removed by Filter: 17511
Planning time: 56.021 ms
Execution time: 370.252 ms