Как оптимизаровать запрос в postgresql c фильтрацией и сортировкой?

Question

[WellWisher]

Есть базовая таблица (условно)
entities (id serial, value dp, date timestamp);

Хочется максимально оптимизировать запросы вида:

SELECT * FROM entities WHERE value = __VALUE__ ORDER BY date ASC NULLS LAST

Где __VALUE__ некое dp число, которое представлено в таблице.

Пробовал различные варианты:

1. добавление индекса на value
   
2. добавление индекса на date
   
3. добавление мультиколоночных индексов (value, date) и (date, value)
   

На мой взгляд, единственный рабочий индекс из четырех:
- индекс на поле value

Собственно вопрос, можно ли добиться лучшей производительности и почему индекс (value, date) не работает?
Т.е. сейчас оптимизирована лишь выборка данных а не их сортировка.

Сведения о данных:

• Записей в таблице - миллиард
  
• Все ключи - обычные btree
  
• Фильтр конкретному значению __VALUE__ возвращает около 250 тысяч записей (т.е. индекс эффективен).
  
• Индексы создавались с соответствующими сортировками
  

Сведения о системе:

• postgresql 9.3 или (9.4)
  
• настройки по-умолчанию
  
• с параметрами памяти для сортировок игрался, но добиться использования индекса так и не смог
  

Да, можно видоизменить запрос, и добавить что-то вроде LIMIT в конце, тогда помогает индекc (date, value), но он становится не эффективным, тогда помимо LIMIT в этом же запросе появляется OFFSET

Answer 1

[igruschkafox]

я бы попробовал следующий вариант

если возможно то кластерным индексом отсортировать таблицу по - date ASC
Но надо быть осторожным что бы не затормозить другие запросы (в которых может быть DESC сортировка по дате)
и создать индекс по полю value

и почему индекс (value, date) не работает?

поиск то происходит по полю "value" а не по сочитанию полей
поле "date" - используется только для сортировки результата, а не отбора --- Поэтому и не работает составной индекс

Comments

[WellWisher]

Интересная мысль, верно ли я понял, что фактически это будет выглядеть как физическая сортировка строк таблицы в указанном порядке.
При запросах фактически будет происходить тоже, что и сейчас, только вот сортировать либо:
а) не надо совсем, если строки не менялись
б) надо сортировать, но количество перестановок в сортировке все равно гораздо меньше (множество то было упорядочено)

[igruschkafox]

1. да - будет физическая сортировка строк в таблице
2. результат запроса вообще сортироваться не будет так как нет в этом надобности

[Кирилл]

igruschkafox: есть одно "но" у постгеса нет кластерных индексов, он может 1 раз упорядочить данные с помощью CLUSTER table index, если что-то потом меняется, то оно опять же фрагментируется

[WellWisher]

Кирилл: Да, я так и понял, поэтому и указал в ответе что можно не сортировать при неизменности таблицы, либо сортировать но "стоиомсть" сортировки будет меньше (и будет зависеть от кол-ва измененных/новых данных)

Answer 2

[Swartalf]

>Записей в таблице - миллиард

А партиционирование у нас используется? если нет, то советую www.postgresql.org/docs/9.1/static/ddl-partitionin...

Comments

[WellWisher]

Партиционирование хорошо, а что если записей будет всего миллион?
т.е. это на данный момент эта задача - чисто теоретические изыскания, но хочется понять как её можно решить с минимальными усилиями в виде индексов.

[Swartalf]

ну если чисто теоритическое, то можно использовать рейд из ссд(зеркало),
создать отдельный тейблспейс который разместить на созданом рейде и перести таблицу в новый тейблспейс.

[Swartalf]

в догонку, если записей милиард, но активно используется только милин, то тут опять спасат партицирование, конкретно перести нужные партишены на скоросной рейд :)

[WellWisher]

Swartalf: т.е. по аналогии с другими СУБД postgresql не имеет использовать первую часть ключа для фильтра, а вторую для сортировки?
И все варианты оптимизации больше "физические": по-другому хранить, хранить на более быстром винте, тем не менее, насколько я понимаю, это отразится на ускорении выборки но не сортировки.

[Swartalf]

WellWisher: c индексом btree чтоно не умеет.
>И все варианты оптимизации больше "физические"
у вас простейший запрос с условием по одному полю, максимум что можно с него выжать, это отсечь null значения в _VALUE_ (если они есть).
> настройки по-умолчанию
это означает что у вас конфиг из поставки, где ни однин параметр вроде work_mem или shared_buffers не менялся? если да, то советую пройтись по конфигу хотя бы pg_tune'ом(отлайн верися pgtune.leopard.in.ua/)

[WellWisher]

Swartalf: Не хотел вдаваться в палемику, но, например, на другой субд, помогает мультиколоночный индекс, первая часть которого используется вначале для фильтра а вторая - для сортировки. Я искал похожего решения в посгресе, но не нашел. Перечитал оф. документацию, стек, и ничего путного не нашел, хотя запрос крайне простой.

Видимо пострес просто так не умеет. Будем привыкать ;)

Опять же повторюсь, хочется именно использовать индекс для сортировки в представленном выше запросе. С настройками все понятно, просто "теоритически" я мог не соблюсти некоторые условия, при которых планировщик был бы готов использовать двойной индекс. Думал может быть кто-то сталкивался и знает. Т.к. заставить то его(планировщик) использовать определенный индекс нельзя.

Answer 3

[Кирилл]

Вообще вся эта штука сильно зависит от селективности, если данных по value много постгрес может решить что "посортировать так" это эффективнее, в общем же случае индекс будет использоваться

т.к. "настройки по-умолчанию" сами сделайте analyze для entities, т.к. скорее всего постгрес не в курсе как у вас там данные по таблице распределены и "полюет на все фулсканом"

ps:

create table entities (id serial, value int, date timestamp);

create index idx_order on entities (value, date asc nulls last);


insert into entities (value, date)
select 
generate_series(1, 20),
time
from generate_series(date_trunc('day', current_timestamp - '10 day'::interval), date_trunc('day', current_timestamp ), '1 second') as time;


analyze entities;


explain SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST


Sort  (cost=239331.74..241545.03 rows=885318 width=16)
  Sort Key: date
  ->  Bitmap Heap Scan on entities  (cost=32277.78..136750.25 rows=885318 width=16)
        Recheck Cond: (value = 10)
        ->  Bitmap Index Scan on idx_order  (cost=0.00..32056.45 rows=885318 width=0)
              Index Cond: (value = 10)


truncate entities;

insert into entities (value, date)
select 
generate_series(1, 200),
time
from generate_series(date_trunc('day', current_timestamp - '1 day'::interval), date_trunc('day', current_timestamp ), '1 second') as time;

analyze entities;


explain SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST;

Sort  (cost=106726.86..106942.86 rows=86401 width=16)
  Sort Key: date
  ->  Bitmap Heap Scan on entities  (cost=3202.17..99642.51 rows=86401 width=16)
        Recheck Cond: (value = 10)
        ->  Bitmap Index Scan on idx_order  (cost=0.00..3180.57 rows=86401 width=0)
              Index Cond: (value = 10)



explain analyze SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST;

Sort  (cost=106726.86..106942.86 rows=86401 width=16) (actual time=18774.148..18783.923 rows=86401 loops=1)
  Sort Key: date
  Sort Method: external sort  Disk: 2200kB
  ->  Bitmap Heap Scan on entities  (cost=3202.17..99642.51 rows=86401 width=16) (actual time=2618.908..18681.170 rows=86401 loops=1)
        Recheck Cond: (value = 10)
        Rows Removed by Index Recheck: 9708392
        Heap Blocks: exact=33638 lossy=52763
        ->  Bitmap Index Scan on idx_order  (cost=0.00..3180.57 rows=86401 width=0) (actual time=2611.946..2611.946 rows=86401 loops=1)
              Index Cond: (value = 10)
Planning time: 0.093 ms
Execution time: 18788.452 ms


set work_mem='1GB';

explain analyze SELECT * FROM entities WHERE value = 10 ORDER BY date ASC NULLS LAST;

Sort  (cost=106726.86..106942.86 rows=86401 width=16) (actual time=614.335..619.024 rows=86401 loops=1)
  Sort Key: date
  Sort Method: quicksort  Memory: 7123kB
  ->  Bitmap Heap Scan on entities  (cost=3202.17..99642.51 rows=86401 width=16) (actual time=198.700..595.232 rows=86401 loops=1)
        Recheck Cond: (value = 10)
        Heap Blocks: exact=86401
        ->  Bitmap Index Scan on idx_order  (cost=0.00..3180.57 rows=86401 width=0) (actual time=177.392..177.392 rows=86401 loops=1)
              Index Cond: (value = 10)
Planning time: 0.145 ms
Execution time: 623.327 ms

Comments

[WellWisher]

Да, но в данном случае всё равно везде используется только первая часть индекса, выходит что вторая ненужна. Мысль про достаточно малое кол-во возвращаемых строк интересна, я пробовал только для 250к и 25к, попробую уменьшить кол-во строк.
ANALYZE всегда выполняю, чтобы он понимал какие данные.

[Кирилл]

WellWisher: что-то я туплю, видимо конец недели ) не будет он составной индекс для сортировки использовать, для: "больше", "меньше", "диапазонов"... будет, для сортировки нет. После "отсечения" по первому условию от второй части индекса какие-то куски, сортировка по всему индексу + слияние будет медленнее чем сортировка полученных результатов, как-то так

в общем заюзать индекс для такой сортировки вряд ли, ускорить выборку можно, хотя при отдаче на клиент 25/250к результатов не факт время сортировки на сервере будет критично