agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: querying with index on jsonb slower than standard column. Why?
Date: Sun, 07 Dec 2014 17:15:17 -0800
Message-ID: <5484FBA5.8060902@aklaver.com> (raw)
In-Reply-To: <5484F963.40009@gmail.com>
References: <5484DBDA.6090405@gmail.com>
<5484EEA7.1030403@aklaver.com>
<5484F437.2080402@gmail.com>
<5484F69F.2030006@aklaver.com>
<5484F963.40009@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 12/07/2014 05:05 PM, Tim Dudgeon wrote:
>
> On 07/12/2014 21:53, Adrian Klaver wrote:
>> On 12/07/2014 04:43 PM, Tim Dudgeon wrote:
>>>
>>> On 07/12/2014 21:19, Adrian Klaver wrote:
>>>> On 12/07/2014 02:59 PM, Tim Dudgeon wrote:
>>>>> I was doing some performance profiling regarding querying against
>>>>> jsonb
>>>>> columns and found something I can't explain.
>>>>> I created json version and standard column versions of some data, and
>>>>> indexed the json 'fields' and the normal columns and executed
>>>>> equivalent
>>>>> queries against both.
>>>>> I find that the json version is quite a bit (approx 3x) slower which I
>>>>> can't explain as both should (and are according to plans are) working
>>>>> against what I would expect are equivalent indexes.
>>>>>
>>>>> Can anyone explain this?
>>>>
>>>> The docs can:
>>>>
>>>> http://www.postgresql.org/docs/9.4/interactive/datatype-json.html#JSON-INDEXING
>>>>
>>>>
>>>
>>> If so them I'm missing it.
>>> The index created is not a gin index. Its a standard btree index on the
>>> data extracted from the json. So the indexes on the standard columns and
>>> the ones on the 'fields' extracted from the json seem to be equivalent.
>>> But perform differently.
>>
>> Down into the section there is this:
>>
>> "jsonb also supports btree and hash indexes. These are usually useful
>> only if it's important to check equality of complete JSON documents.
>> The btree ordering for jsonb datums is seldom of great interest, but
>> for completeness it is:
>>
>> Object > Array > Boolean > Number > String > Null
>>
>> Object with n pairs > object with n - 1 pairs
>>
>> Array with n elements > array with n - 1 elements
>>
>> Objects with equal numbers of pairs are compared in the order:
>>
>> key-1, value-1, key-2 ...
>>
>> Note that object keys are compared in their storage order; in
>> particular, since shorter keys are stored before longer keys, this can
>> lead to results that might be unintuitive, such as:
>>
>> { "aa": 1, "c": 1} > {"b": 1, "d": 1}
>>
>> Similarly, arrays with equal numbers of elements are compared in the
>> order:
>>
>> element-1, element-2 ...
>>
>> Primitive JSON values are compared using the same comparison rules as
>> for the underlying PostgreSQL data type. Strings are compared using
>> the default database collation.
>> "
>>
>> As I understand it to get useful indexing into the jsonb
>> datum(document) you need to use the GIN indexes.
>
> Yes, but if my understanding is correct I'm not indexing the JSON, I'm
> indexing the PostgreSQL float type extracted from a field of the JSON,
> and indexing using a btree index:
>
> create index idx_data_json_assay2_ic50 on json_test (((data ->>
> 'assay2_ic50')::float));
>
> The data ->> 'assay2_ic50' bit extracts the value from the JSON as text,
Which is where I would say your slow down happens. I have not spent a
lot of time jsonb as I have been waiting on the dust to settle from the
recent big changes, so my empirical evidence is lacking.
> the ::float bit casts to a float, and the index is built on the
> resulting float type.
>
> And the index is being used, and is reasonably fast, just not as fast as
> the equivalent index on the 'normal' float column.
>
> Tim
>>
>>>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (24+ messages) latest in thread
Message-ID: <5484FBA5.8060902@aklaver.com>
Permalink: ../5484FBA5.8060902@aklaver.com/
Also on: postgresql.org/message-id/5484FBA5.8060902@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, tdudgeon.ml@gmail.com
Subject: Re: querying with index on jsonb slower than standard column. Why?
In-Reply-To: <5484FBA5.8060902@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox