aurora postgres difference between jsonb_path_match and operator @@ gin index

0
votes

I am relatively new to postgressql. We are using aurora postgressql.

I created a GIN index on a table with about 400k rows with one JSONB column. I created index on JSONB column.

CREATE INDEX index1 ON schemanm.tblname USING gin(colname) TABLESPACE pg_default;

The version i use is PostgreSQL 12.7

With the below query it does not use the gin index and does a seq scan

select count(*) from schemanm.tblname
where  jsonb_path_match(colname,'($.colname.system == "X" ||$.colname.system == "Y")    && $.colname.attra == "attrvala" && $.colname.attrb == "aatrvalb"' )

But the below uses index and gives the same result as the above query

select count(*) from schemanm.tblname
where  colname @@ '($.colname.system == "X" ||$.colname.system == "Y")  && $.colname.attra == "attrvala" && $.colname.attrb == "aatrvalb"'

Is there any difference between the queries or i am doing anything wrong

1
postgresqlindexingdatabase-performancejsonbamazon-aurora
Please don't use Indian words like lakh / lac in global forums like this. People living elsewhere have no idea what those mean. – James Z
ok thanks did not realize it, will follow it henceforth – Balaji Govindan

1 Answers

0
votes

Only operators, not functions, can support indexes. I think that this is not strictly true anymore with the introduction of "function support functions", but it is still the case for @@ and jsonb_path_match.