В запросах дичь какая-то, выгрузка дампа в stdout

Господа, добрый день.

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

Есть запрос

SELECT count(*) AS total
FROM (SELECT *
      FROM incoming
               JOIN sync_devices ON incoming.device_id_int = sync_devices.device_id
      WHERE incoming.demodulation_time >= '2020-07-23T16:54:37.840Z'
        AND sync_devices.batch_id = 500
      LIMIT 10000) AS query

В таблице sync_devices есть 500 записей с batch_id=500 и 3200 записей с batch_id=3200.

В таблице incoming ~18000 записей для join cо строками, содержащими batch_id=500. И больше 200000 записей для join со строками, содержащими batch_id=3200.

Планировщик использует Nested Loop для маленького джойна и Hash Join для большого джойна. В итоге, небольшое количество данных обрабатывается за 30 секунд, а в разы больший объем данных - за 700мс.

Подскажите в какую сторону копать, чтобы побороть эти тормоза на ровном месте?

В запросах дичь какая-то, выгрузка дампа в stdout

LIMIT без ORDER BY — это не "ровное место", правда. Т.е. в чём вообще смысл этого запроса?
А так — показали бы Вы планы, что ли...

C ORDER BY ситуация аналогична.

смысл запроса - посчитать количество строк, для последующей пагинации через LIMIT/OFFSET. Каждая страница по 100 записей. Так как данных много, то решили, что дальше 100й страницы вряд ли кто-то в здравом уме долистает, поставили ограничение 10000.

В каком виде удобнее прислать планы? Как файлы или как прямо в сообщении?

Удобнее всего — выложить EXPLAIN (ANALYZE, BUFFERS /*, SETTINGS /* если у Вас v12 */ */) на https://explain.depesz.com/ , а сюда — ссылки, IMHO.

Удобнее всего — выложить EXPLAIN (ANALYZE, BUFFERS /*, SETTINGS /* если у Вас v12 */ */) на https://explain.depesz.com/ , а сюда — ссылки, IMHO.

Много данных обрабатывается быстро: https://explain.depesz.com/s/wGUD

Мало данных обрабатывается медленно: https://explain.depesz.com/s/slNL

https://explain.depesz.com/s/wmdG — это какая-то "окрошка", а не план — Вы что-то криво вставили.

Сорри, видимо план вставился дважды. Поправил в начальном сообщении и тут: https://explain.depesz.com/s/slNL

Ну а тот же план (для batch_id = 511 и owner_id = 3) при "enable_nestloop = off" как оценивается / выполняется?
Вообще, тут явно ошибка в оценках:

  ->  Bitmap Heap Scan on sync_devices  (cost=12.24..679.72 rows=15 width=4) (actual time=33.360..187.568 rows=325 loops=1)

А вообще, PostgreSQL тут правильно настроен под нагрузку (странный какой-то effective_cache_size)?

Ну а тот же план (для batch_id = 511 и owner_id = 3) при "enable_nestloop = off" как оценивается / выполняется?
Вообще, тут явно ошибка в оценках:

  ->  Bitmap Heap Scan on sync_devices  (cost=12.24..679.72 rows=15 width=4) (actual time=33.360..187.568 rows=325 loops=1)

А вообще, PostgreSQL тут правильно настроен под нагрузку (странный какой-то effective_cache_size)?

Прямо сейчас postgresql на настройках по-умолчанию, которые генерируются на AWS RDS.

Пробовал настройки Web Application Server и Data Warehouse, нагенеренные на этом сайте:https://pgtune.leopard.in.ua/#/ . В результате все становилось только хуже.

> Прямо сейчас postgresql на настройках по-умолчанию, которые генерируются на AWS RDS.

Так это и не PostgreSQL, что ли (я всё время забываю, fork это или нет)? ;)
Потому что:

> SELECT pg_size_pretty(388872.0 * 1024.0);
380 MB

В системе действительно менее полугигабайта RAM?!

>  В результате все становилось только хуже.

Он для настоящего postgres и настоящего "железа". И то, только в первом приближении.

> Можно как-то указать enable_nestloop=false прямо в запросе?

Перед запросом — можно. А hints в PostgreSQL нет. ;)

> и не хочется лишний раз его дергать в середине рабочего дня.

Достаточно одного раза же...

> Так это и не PostgreSQL, что ли (я всё время забываю, fork это или нет)? ;)
PostgreSQL. Это просто managed instance обычной PostgreSQL. Fork вроде как на Serverless-версии

> В системе действительно менее полугигабайта RAM?!
Нет, системе 1ГБ RAM. Не много, но в 3 раза больше, чем в pg_size_pretty

> Перед запросом — можно. А hints в PostgreSQL нет. ;)
Перед запросом - это как?)

> Достаточно одного раза же...
Одного раза - для одного эксперимента же. Если я совсем отключу nested loop, то в других запросах все не станет супер-сильно хуже?

> Нет, системе 1ГБ RAM. Не много, но в 3 раза больше, чем в pg_size_pretty

Мало, да. Тем не менее, обычная настройка побольше (50 — 75%, можно ставить и 100%, ничего страшного).

> Перед запросом - это как?)

Это в той же сессии. Подключаетесь psql (да неважно чем), сначала "SET enable_nestloop = off;", потом EXPLAIN ...

> Если я совсем отключу nested loop, то в других запросах все не станет супер-сильно хуже?

Нет, для всего сервера не отключайте, конечно! Это просто чтобы оценки сравнить (PostgreSQL выбирает nested loop потому, что считает его дешевле — хочется посмотреть на оценку альтернативного плана).