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?
June 10th 19 at 16:01
1 answer
June 10th 19 at 16:03
Solution
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.
to use in queries as well as with no index: kastuem to int and compare?
it turns out that each numeric feature is necessary to index hands? :( is not very convenient. wanted to take as an alternative to EAV, try on a project and this is now forced to seriously think about. - Easter.Waelchi commented on June 10th 19 at 16:06
in the General case the expression in the index must match with what is written in the query. For beauty you can make immutable language sql hranicu with jsonb and the name of the parameter input and the number output.
For catalog filters like silver bullet has not yet - Ignacio_Romaguera commented on June 10th 19 at 16:09

Find more questions by tags PostgreSQL