How to do filtering by the number of JSONB in PostgreSQL using an index?

Question

How to do filtering by the number of JSONB in PostgreSQL using an index?

Approximately such structure: sqlfiddle.com/#!17/6cea9/1

Only about 2 million of goods, all subsets of queries are performed quickly, within 20 MS. high, but a request using a numeric comparison is performed for a long time.
1. What you need the index to hang and how to write the query to the index used? Tried regular (not gin) index and btree. But if you write (features->>'capacity')::int < 300, then the request still works very long (around 300-500 MS.).

2. Is there a universal way to fetch a comparison on the number to index each field of a JSON object? Today, there is capacity, tomorrow color in decimal, each time to hang up the index when adding numerical features did not really want.

upd.: when prompted

EXPLAIN (ANALYSE, BUFFERS) SELECT id, name, price FROM product features
WHERE (features->>'color')::int > 1000000
ORDER BY price ASC
LIMIT 25

get:

EXPLAIN

Limit (cost=0.43..10.56 rows=25 width=161) (actual time=0.018..0.093 rows=25 loops=1)
 Buffers: shared hit=51
 -> Index Scan Backward using product_price_index on product (cost=0.43..317546.33 rows=783959 width=161) (actual time=0.016..0.071 rows=25 loops=1)
 Filter: (((features ->> 'color'::text))::integer > 1000000)
 Rows Removed by Filter: 23
 Buffers: shared hit=51
Planning time: 0.068 ms
Execution time: 0.120 ms

I.e. the index is used, although in this case the index is triggered on price column? Then why the request fulfills quickly? How to force to work an index if the WHERE conditions several?

PostgreSQL

Easter.Waelchi asked June 10th 19 at 16:01

Related questions

More answers about "How to do filtering by the number of JSONB in PostgreSQL using an index?"

1 answer

Ignacio_Romaguera answered on · Accepted Answer · 2019-06-10T14:03:43.222Z

The index may hang functional:

create index on tablename using btree(((features ->> 'capacity'::text)::integer));

Accordingly, the predicate for this same expression will have the opportunity to use this index.

More opportunities clearly indexing for jsonb query for numeric ranges, I somehow do not recall. Sorted by the field-only btree and know how from all the relevant access method.

I.e. the index is used, although in this case the index is triggered on price column? Then why the request fulfills quickly?

Yes, the index used is the index for price. Because you have it sorted. And planner based on your stats hopes to be able to quickly find a 25 lines by reading the lines in the desired order to sort by index and on the way to check is to throw wrong.