Ребят, ткните пожалуйста, где можно что то дельное почитать. Проблема такая.

postgresql 12.5

запрос

Select t2.* FROM t1 JOIN t2 on t2.id=t1.t2_id WHERE t1.id = 5358;

t2.id primary key

в таблице t2 6млн записей.

В итоге если по t1.id = 5358 отобрано 30400 записей, то используется индекс t2.id primary key, с другим t1.id = 5810 из t1 отбирается 30500 записей и уже идет seq_scan таблицы t2. В итоге 30400 записей достается 0.33ms, а всего на 100 больше 30500 достается уже больше 3секунд.

30500 от 6ти млн это же меньше 1% , селективность для использования индекса хорошая, почему тогда оптимизатор выбирает seq_sqan? В какую сторону копать? Подбирать настройки сервера?

Заранее спасибо.

Дальнейшие эксперименты

Select t2.* FROM t1 JOIN t2 on t2.id=t1.t2_id WHERE t1.id = 6150; отбирается 40000 записей, используется индекс.


Select t2.* FROM t1 JOIN t2 on t2.id=t1.t2_id WHERE t1.id = 5810 ; отбирается 30500 записей, seq_scan

Select t2.id FROM t1 JOIN t2 on t2.id=t1.t2_id WHERE t1.id = 5810 ; используется индекс

Select t1.* FROM t1 JOIN t2 on t2.id=t1.t2_id WHERE t1.id = 5810 ; используется индекс

Select t1.*, t2.id FROM t1 JOIN t2 on t2.id=t1.t2_id WHERE t1.id = 5810 ; используется индекс

Select t1.*, t2.id, t2.group_id FROM t1 JOIN t2 on t2.id=t1.t2_id WHERE t1.id = 5810 ; seq_scan

Select t2.* FROM t2 JOIN t1 on t2.id=t1.t2_id WHERE t1.id = 5810; seq_scan таблицы t2


Select t2.* FROM t2 WHERE t2.id IN (SELECT t2_id FROM t1 where t1.id = 5810); index scan t2.id pk

На другом сервере нашел тоже схожую ситуацию, postgres 9.6

авторexplain(analyze, buffers)select
m.*
from messages m
join messages_users u on u.mess_id = m.id
where u.user_id = 140777;


Nested Loop (cost=1.00..253328.46 rows=29696 width=44) (actual time=0.078..180.555 rows=31920 loops=1)
Buffers: shared hit=143395
-> Index Scan using messages_users_user_id_idx on messages_users u (cost=0.56..72884.30 rows=29696 width=4) (actual time=0.061..80.524 rows=31920 loops=1)
Index Cond: (user_id = 140777)
Buffers: shared hit=15548
-> Index Scan using pk_rep_messages on rep_messages m (cost=0.43..6.07 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=31920)
Index Cond: (id = u.mess_id)
Buffers: shared hit=127847
Planning time: 1.352 ms
Execution time: 182.120 ms


Второй
авторexplain(analyze, buffers)select
m.*
from messages m
join messages_users u on u.mess_id = m.id
where u.user_id = 101678;


Hash Join (cost=197351.00..286288.72 rows=36445 width=44) (actual time=2421.834..2514.146 rows=31620 loops=1)
Hash Cond: (u.mess_id = m.id)
Buffers: shared hit=77644 dirtied=2
-> Index Scan using messages_users_user_id_idx on messages_users u (cost=0.56..88209.38 rows=36445 width=4) (actual time=0.058..69.154 rows=31620 loops=1)
Index Cond: (user_id = 101678)
Buffers: shared hit=19482 dirtied=2
-> Hash (cost=120021.86..120021.86 rows=6186286 width=44) (actual time=2392.010..2392.010 rows=6186564 loops=1)
Buckets: 8388608 Batches: 1 Memory Usage: 548862kB
Buffers: shared hit=58159
-> Seq Scan on messages m (cost=0.00..120021.86 rows=6186286 width=44) (actual time=0.018..576.936 rows=6186564 loops=1)
Buffers: shared hit=58159
Planning time: 1.023 ms
Execution time: 2569.791 ms


По первому пользователю с ид 140777 писем больше 31920, по второму с ид 101678 меньше 31620 писем. Но второй запрос идет по Seq Scan

всего 40млн записей, select n_distinct from pg_stats показывает 8500

да Вы правы, для последнего примера записей в messages больше 6млн, select n_distinct from pg_stats выдает '-1'

https://hdb.docs.pivotal.io/210/hawq/reference/catalog/pg_stats.html

Написано, что для столбца с уникальным ключом n_distinct = -1. То есть это нормально

Да, извиняюсь. 12.3 стоит.

То есть в одном случае id в таблице message в одной или нескольких секциях индекса, а во втором случае id так разбросаны, что нет смысла юзать индекс? Что можно предпринять в таком случае? - Добавить какой то доп. признак в виде даты например?

Тогда запрос потеряет смысл. t2.id мне неизвестны, они получаются из t1

есть и те, с которыми быстро и с которыми медленно

У меня очевидно стоят умолчательные настройки, руками их после установки СУБД никто не менял
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 4GB

То есть они закомментированы.

Да, ту тему я читал. Пробовал менять настройки.
Единственное, может я неверно их менял, потому что никакого результата не получил.

То есть я в одной сессии выполнял
SET random_page_cost = val;
SET seq_page_cost = val;
explain(analyze, buffers)select...

Но я не нашел, как посмотреть текущие параметры сессии, установились ли эти значения таким образом.

Это я не внимательно на исходный вопрос посмотрел. Думал, что условие соединения t1.id = t2.id, поэтому замена выглядела эквивалентной.

Посмотрел внимательнее. Вы уверены, что ниже приведен план именно этого запроса?


Хотел укоротить названия, когда тут текст размещал. Да точно это он. rep_ хотел убрать

Огромное спасибо, помогаете уже не первый раз. Буду читать доку, разбираться в этих настройках дальше.
При данных настройках план запроса для 101678 стал идентичным с 140777. Начал использовать индекс.


Не очень понятно только каким образом тогда вести разработку на постгрис. Разработка обычно ведется на тесте, тест в большинстве случаев на порядок слабее по ресурсам, чем продуктив. Чтобы выжать максимум скорости, тестовый должен быть идентичен боевому с соответствующими настройками СУБД иначе после переноса запросы будут работать не так, как планировалось.

Разница конечно очень существенная, раньше по пользователю с 400тыс писем выборка длилась 8 секунд, теперь 0.7 секунды. В районе 450тыс писем, запрос начинает работать без индекса, но это уже решаемый вопрос.

Maxim Boguk, Спасибо, как всегда все четко и по делу.