I am currently studying a new JSONB type and the correct way to index it. I have a jsonb details column that looks like JSON:
{ price: { amount: 435, currency: "USD" }, condition: "Used", make: "iPhone 5c", serial: 203980982377442 }
My application starts the selection of the order of requests using the price->amount key, for example. ... order by details->'price'->'amount' desc nulls last . Do I understand correctly that in order for PostgreSQL 9.4 to use an index, it must be created in the same way as the order by field? Value:
create index on ads using gin ((details -> 'price' -> 'amount'))
Somehow, I don’t see this index being used when I run the analysis of the explanation:
dev=# explain analyze select "ads"."id", "ads"."title", details->'price' as price from "ads" where ("classified_id" in (select "id" from "classifieds" where "id" = 1 or "parent_id" = 1 and "parent_id" is not null) and "section_id" = 1) and "status_id" = 1 order by details->'price'->'amount' desc nulls last; QUERY PLAN ------------------------- Sort (cost=20.30..20.31 rows=1 width=556) (actual time=0.276..0.277 rows=24 loops=1) Sort Key: (((ads.details -> 'price'::text) -> 'amount'::text)) Sort Method: quicksort Memory: 28kB -> Nested Loop (cost=0.14..20.29 rows=1 width=556) (actual time=0.042..0.227 rows=24 loops=1) Join Filter: (ads.classified_id = classifieds.id) Rows Removed by Join Filter: 144 -> Index Scan using ads_classified_id_section_id_category_id_idx on ads (cost=0.14..8.16 rows=1 width=560) (actual time=0.018..0.028 rows=24 loops=1) Index Cond: (section_id = 1) -> Seq Scan on classifieds (cost=0.00..12.10 rows=2 width=4) (actual time=0.001..0.005 rows=7 loops=24) Filter: ((id = 1) OR ((parent_id = 1) AND (parent_id IS NOT NULL))) Rows Removed by Filter: 7 Planning time: 0.760 ms Execution time: 6.
source share