Оптимизация запроса с inner join самой первой записи

Question

Всем привет, есть таблица "А" которая содержит +11тыс, и есть таблица "Б" которая содержит +400 миллионов записей, получается что таблица "Б" кол. записей / кол. записей "А" = на каждый внешний ключ таблицы "А" Задача вытащить с лимитом от 10шт до 5000тыс штук из таблицы "А" с привязкой самой 1-ой записи таблицы "Б", накидал запрос, но после 10 минут ожидания сбросил, так и не дождавшись окончания.

select "a".*, b.col1, b.col2, b.col3 from "a"
inner join b on b.col_table_a_id = a.id 
and b.id = (select min(id) from b where a.id = b.col_table_a_id)
limit 100 offset 0;

Есть идея создать в таблице "Б" поле first и установить туда значение в true у первой записи, но мне кажется это како-то костыль прям жесткий...

Answer 1

Может что-то типа такого

SELECT 
  "a".*, 
  b.col1, 
  b.col2, 
  b.col3 
FROM 
  "a" 
  INNER JOIN (
    SELECT 
      b.col_table_a_id, 
      b.col1, 
      b.col2, 
      b.col3 
    FROM 
      b 
    WHERE 
      a.id = b.col_table_b_id 
    LIMIT 
      100
  ) bb ON bb.col_table_a_id = a.id

Answer 2

А если как-то так попробовать, чтобы без подзапроса было, а просто ещё один inner join вместо этого:

select a.*, b.col1, b.col2, b.col3 from a
inner join 
(select min(b.id) min_b_id, a.id a_id from a
inner join b on b.col_table_a_id = a.id 
group by a.id) j on a.id = j.a_id
inner join b on b.col_table_a_id = j.a_id and b.id = j.min_b_id
limit 100 offset 0;

Я не настоящий сварщик, может это и хуже будет или вообще не то, но просто как вариант. :)

Кстати, индексы все ли нужные созданы, чтобы нормально джойнилось, и план выполнения смотрели?

Answer 3

Вы стартуете от таблицы a и все тормоз из-за join. Поиграться бы, но генерить 400 лямов строк не охота.. При индексе по полям col_table_a_id, id в таблице b попробуйте:

select a.*, b.*
from(
  select row_number()over(partition by col_table_a_id order by id)rn,*
  from b
)b
  join a.id = b.col_table_a_id 
where b.rn=1

Неуверен что оптимизатор постгреса сделает с row_number, можно ещё попробовать с группировкой

select a.*, b.*
from(
  select min(id)id, col_table_a_id 
  from b
  group by col_table_a_id 
)t 
  join a on a.id = t.col_table_a_id 
  join b on b.id = t.id

Попробуйте взять не 400 миллионов строк, а лимит, скажем, в 1 миллион в подзапросе на таблицу b. Сможете спокойно поэкспериментировать с разными вариантами. Не жидая каждую попытку по 10+ минут.

пс: Специально не указал limit, т.к. вы не сказали порядок сортировки.

ппс: Сделать битовую метку не такая уж и плохая идея. При условии, что из таблицы b не будут удаляться записи.

пппс: А ещё можно хранить ссылку из таблички a на первую "свою" строку в табличке b

Answer 4

В общем нашел быстрый способ и без особой заморочки, для этого нужно создать materialized view, запросом выбираем все первые id и заполняем таблицу, дальше в самом запросе меняем таблицу на нашу вьюху и получаем скорость выборку от 500mc до 1 минуты в моем случаи, что на много быстрее чем было, я ждал 10минут и запрос не был завершен.

select "a".*, b.col1, b.col2, b.col3 from "a"
   inner join b_view as b on b.col_table_a_id = a.id 
limit 100 offset 0;

БЛОГ НА HUSL

Оптимизация запроса с inner join самой первой записи

Ответы (4 шт):