Thread: 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

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

On 4/18/15 1:30 AM, Pai-Hung Chen wrote:
> 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?

No. The optimizer is going to first try and satisfy the WHERE clause
with that relevant index. Because it's doing that it can't do anything
about the ORDER BY.

There are some very limited cases where an index will speed up an ORDER
BY, but they're very rare. The problem is that it only makes sense to
use an index with an ORDER BY if you're only returning a very small
percentage of the table. The only ways I can think of offhand where that
will happen is if you ORDER BY columns that are already part of the
WHERE clause, or if there's no WHERE but you do use a LIMIT.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com