indexed range queries on jsonb?

Поиск
Список
Период
Сортировка
От Larry White
Тема indexed range queries on jsonb?
Дата
Msg-id CAMdbzVi4bFfCe_8rViS3+-SQzbi0Ps7MaaBg7vB=oxVfBMs+Uw@mail.gmail.com
обсуждение исходный текст
Ответы Re: indexed range queries on jsonb?
Re: indexed range queries on jsonb?
Список pgsql-general
Hi,

I'm trying to find a way to do a range query on json such that it will use an index. This seems possible given that jsonb supports btrees and expression indices.

For example I have:
create index t1 on document using btree ((payload->'intTest')); 

where: payload is a jsonb column and intTest is a json key whose value is always an int. Based on the documentation examples, I created an index like this:

create index t1 on document using btree ((payload->'intTest'));

Logically, what I want is to be able to make queries like this:
select * from document where ((payload->'intTest'))> 5;
With casting, I came up with:
select * from document where (((payload->'intTest'))::text)::integer > 5;

But this query does not use the index according to Explain

"Seq Scan on public.document  (cost=0.00..1868.33 rows=5764 width=619) (actual time=286.228..1706.638 rows=1974 loops=1)"
"  Output: owner, document_type, guid, schema_version, payload, last_update, payload_class, instance_version, acl_read, deleted, fts_text"
"  Filter: ((((document.payload -> 'intTest'::text))::text)::integer > 5)"
"  Rows Removed by Filter: 15319"
"  Buffers: shared hit=5420 read=29085"
"Planning time: 0.108 ms"
"Execution time: 1706.941 ms"

Any help at all would be appreciated.

Thanks.

В списке pgsql-general по дате отправления:

Предыдущее
От: John McKown
Дата:
Сообщение: Re: POWA tool
Следующее
От: Christian Ramseyer
Дата:
Сообщение: Re: indexed range queries on jsonb?