Re: DISTINCT on jsonb fields and Indexes

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: DISTINCT on jsonb fields and Indexes
Дата
Msg-id CAHOFxGpWgy4PC0MczyWHK6UTgc-RxO_RS0cezLChkH+Tksbk-A@mail.gmail.com
обсуждение исходный текст
Ответ на DISTINCT on jsonb fields and Indexes  (Sankar P <sankar.curiosity@gmail.com>)
Ответы Re: DISTINCT on jsonb fields and Indexes
Список pgsql-general
On Sun, Jun 21, 2020 at 10:43 PM Sankar P <sankar.curiosity@gmail.com> wrote:
I have a table with the schema:

CREATE TABLE fluent (id BIGSERIAL, record JSONB);

Then I created a couple of indexes:
1) CREATE INDEX idx_records ON fluent USING GIN (record);

What about using non-default jsonb_path_ops?

 
2) CREATE INDEX idx_btree_records ON fluent USING BTREE ((record ->>
'destinationServiceName'));

Is this key always set? If so, make it a proper column so you get statistics on common values and number of distinct values as such.

If it is rarely used, create a partial index perhaps. I am a little surprised that the plain btree index wasn't used from my naive point of view. Did you check execution time with sequential scan disabled to try to strongly encourage the use of index scan?

В списке pgsql-general по дате отправления:

Предыдущее
От: Flaris Feller
Дата:
Сообщение: Re: ERROR: invalid memory alloc request size 18446744073709551613
Следующее
От: Paul Förster
Дата:
Сообщение: Re: Netapp SnapCenter