From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Pai-Hung Chen <paihungchen(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ORDER BY for jsonb |
Date: | 2015-04-18 03:59:12 |
Message-ID: | 5531D690.2040507@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2015-04-18 04:01:05 | Re: "Cast" SRF returning record to a table type? |
Previous Message | Pai-Hung Chen | 2015-04-18 02:53:14 | ORDER BY for jsonb |