From: | Larry White <ljw1001(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | indexed range queries on jsonb? |
Date: | 2014-08-26 13:30:13 |
Message-ID: | CAMdbzVi4bFfCe_8rViS3+-SQzbi0Ps7MaaBg7vB=oxVfBMs+Uw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Christian Ramseyer | 2014-08-26 13:46:17 | Re: indexed range queries on jsonb? |
Previous Message | John McKown | 2014-08-26 11:26:08 | Re: POWA tool |