Re: ORDER BY for jsonb

Thanks for the help. So in this case, the performance of ORDER BY will not be affected at all by whether an index is created on the jsonb "setting" field?

Pai-Hung

-----Original Message-----
From: "Jim Nasby" <Jim(dot)Nasby(at)BlueTreble(dot)com>
Sent: ‎4/‎17/‎2015 8:59 PM
To: "Pai-Hung Chen" <paihungchen(at)gmail(dot)com>; "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] ORDER BY for jsonb

On 4/17/15 9:53 PM, Pai-Hung Chen wrote:
> Hi,
>
> I am new to PostgreSQL and have a question about the new jsonb type in
> 9.4. Suppose I have a table called "user" that has two columns: (1)
> "user_id" of type text, also the primary key, (2) "setting" of type
> jsonb. With the following query pattern:
>
> SELECT *
> FROM user
> WHERE user_id IN [...]
> ORDER BY setting->>'foo',
> setting->>'bar',
> ...
>
> where ORDER BY clause can contain an arbitrary list of root-level fields
> in "setting". In this case, how should I create indexes for "user" to
> get good query performance?

The performance for that query is going to come from quickly identifying
records from the WHERE clause, which is going to use the primary key.
For the query you're showing, indexes on the setting field aren't going
to help.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com