Re: ORDER BY for jsonb

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