postgreSQL Performance of @> compared to -> and ->>
It is important to understand the performance difference between using @>, -> and ->> in the WHERE part of the query. Although these two queries appear to be broadly equivalent:
SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
SELECT data FROM mytable WHERE data->>'name' = 'Alice';the first statement will use the index created above whereas the latter two will not, requiring a complete table scan.
It is still allowable to use the -> operator when obtaining resultant data, so the following queries will also use the index:
SELECT data->'locations'->'work' FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data->'locations'->'work'->>'city' FROM mytable WHERE data @> '{"name":"Alice"}';