Оптимизация Join а в Postgres

← →
Piter © (2009-10-12 19:38) [0]

Есть запрос:

SELECT c.id, c.unixtime, u.id AS user_id, u.username FROM cb_comments AS c LEFT JOIN cb_users AS u ON (u.id = c.user_id) ORDER BY c.unixtime LIMIT 50 OFFSET 553200

Нужно показать последние в порядке следования даты комментарии. Имя пользователя, написавшего комментарий, берется из таблицы пользователей. Время исполнения 12 секунд - очень долго. Много времени, как я понимию, уходит на джойн, потому что джойнятся все записи.

План запроса:

Limit (cost=134686.94..134687.06 rows=47 width=35) (actual time=10834.742..10834.775 rows=47 loops=1) -> Sort (cost=133303.94..134687.06 rows=553247 width=35) (actual time=10370.345..10749.087 rows=553247 loops=1) Sort Key: c.unixtime -> Hash Left Join (cost=5521.18..46489.92 rows=553247 width=35) (actual time=133.807..8779.188 rows=553247 loops=1) Hash Cond: (c.user_id = u.id) -> Seq Scan on cb_comments c (cost=0.00..23727.47 rows=553247 width=24) (actual time=0.035..381.617 rows=553247 loops=1) -> Hash (cost=3577.97..3577.97 rows=100497 width=19) (actual time=133.166..133.166 rows=100497 loops=1) -> Seq Scan on cb_users u (cost=0.00..3577.97 rows=100497 width=19) (actual time=0.021..71.352 rows=100497 loops=1) Total runtime: 11626.238 ms

На картинках:

http://savepic.ru/877370.gif

http://savepic.ru/883514.gif

Понятно, что намного актуальнее сделать бы сначала сортировку по cb_comments.unixtime, отобрать последние записи и только их сджойнить с cb_users.
Как это элегантно сделать, как оптимизировать?

← →
Piter © (2009-10-12 20:14) [1]

написал с подзапросом:

SELECT c.id, c.unixtime, u.id AS user_id, u.username FROM cb_comments AS c LEFT JOIN cb_users AS u ON (u.id = c.user_id) WHERE c.id in ( SELECT c_help.id from cb_comments AS c_help ORDER BY c_help.unixtime ASC limit 50 offset 553200 )

теперь время исполнения из 12 секунд в 700мс. Поругайте?

← →
Piter © (2009-10-12 20:30) [2]

SELECT c.id, c.unixtime,
u.id AS user_id, u.username
FROM cb_comments AS c
LEFT JOIN cb_users AS u
ON (u.id = c.user_id)
WHERE c.id in (
SELECT c_help.id from cb_comments AS c_help

ORDER BY c_help.unixtime ASC

limit 50 offset 553200
)
ORDER BY c.unixtime ASC

← →
SELECT (2009-10-12 21:07) [3]

почему бы не попробовать джойнить с селектом?
типа
SELECT c.id, c.unixtime, u.id AS user_id, u.username FROM cb_comments AS c LEFT JOIN ( SELECT id from cb_comments ORDER BY unixtime ASC limit 50 offset 553200 ) AS u ON (u.id = c.user_id) ORDER BY c.unixtime ASC
и почему селектим u.id, а не c.user_id?

← →
Piter © (2009-10-13 02:40) [4]

SELECT (12.10.09 21:07) [3]
и почему селектим u.id, а не c.user_id?

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

Насчет джойнить с select"ом - интересно, не нужно двойное сканирование таблицы (хотя это мелочи по сравнению с полным джойном), завтра затестю по скорости, какая разница выльется разница, сейчас уж спать охота...

← →
Sergey13 © (2009-10-13 11:14) [5]

Не знаю смысла фразы
limit 50 offset 553200
наверное какое то ограничение вывода?

Может стОит просто ограничивать время, типа
where unixtime > sysdate-1
ну и индекс на unixtime разумеется нужен.

← →
sniknik © (2009-10-13 12:26) [6]

> Не знаю смысла фразы
> limit 50 offset 553200
дать 50 записей начиная с 553200-й. такой, или похожий синтаксис используется в MySql.

кстати, а почему внутренний запрос на выполнять "от обратного" типа
SELECT id from cb_comments
ORDER BY unixtime DESC
limit 50
(или first вместо limit, если есть), может даже быстрее будет (не нужно отсчитывать первые 553200, и высчитывать последние 50, а просто возьмет первое по индексу с конца)

← →
Piter © (2009-10-13 15:10) [7]

sniknik, не понял логики. Этот лимит - вывод определенной страницы комментов.

Первая страница - limit 50 offset 0
Вторая страница - limit 50 offset 50
Третья - limit 50 offset 100

Или ты что предлагаешь... Допустим, есть 100 страниц всего, ты предлагаешь до 50-ой страницы отсчитывать ASC, а после 50-ой DESC? ))

Не думаю что путаница в логике стоит непонятного выигрыша в миллисекунды может быть. Вот ограничение джойна отлично сработало, в результате весь запрос (он крупнее этого) из 64 секунд на выполнение вышел в 700мс

← →
sniknik © (2009-10-13 15:47) [8]

если
> вывод определенной страницы комментов.
то ничего не поделать, но я по
> [0] отобрать последние записи
понял так, что это именно последние в таблице, и показывать нужно только их, типа как последние строки лога...

← →
Piter © (2009-10-13 15:51) [9]

а, не. Это пример вывода одной из страниц.

← →
SELECT (2009-10-13 20:32) [10]

> Piter © (13.10.09 02:40) [4]
> SELECT (12.10.09 21:07) [3]и почему селектим u.id, а не
> c.user_id?
> если присмотреться - то селектится и то, и другое.

где же и то и другое???

> Piter © (12.10.09 20:30) [2]
> SELECT c.id, c.unixtime, u.id AS user_id, u.username

вопрос связан не со скоростью, а со смыслом и логикой.

← →
Piter © (2009-10-13 21:35) [11]

а, теперь понял претензию. Точнее, не понял )))

А какая разница, ведь это одно и тоже из-за условия джойна: u.id = c.user_id

Что у каждого комментария есть ID пользователя, который его написал, что в таблице пользователей у юзера есть ID...

← →
Loginov Dmitry © (2009-10-14 00:28) [12]

Для FB я бы решил эту задачу приблизительно так:

SELECT c.id, c.unixtime, u.id AS user_id, u.username FROM (SELECT c.* FROM (SELECT c.id, c.unixtime FROM cb_comments AS c ORDER BY c.unixtime LIMIT 50 OFFSET 553200) c LEFT JOIN cb_users AS u ON (u.id = c.user_id))

Только обязательно нужен индекс по c.unixtime и u.id

← →
b z (2009-10-14 10:49) [13]

> Что у каждого комментария есть ID пользователя, который
> его написал, что в таблице пользователей у юзера есть ID.
Зачем тут LEFT JOIN ?

а какой ты предлагаешь? Inner?

> Piter © (13.10.09 21:35) [11]
> А какая разница

разница принципиальна! из-за внешнего соединения.
просто выведи и то и другое, и посмотри...

ЗЫ
кстати, как было подмечено, а в чем смысл именно внешнего соединения в даннном случае?

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

В базе не используется FK... не знаю почему, возможно потому что мы с мускула переехали, а там нету FK? Или есть... В общем, я не знаю ответа на этот вопрос.

Хотелось бы узнать в обратку - а с какой целью интересуетесь? Разве inner будет сильно быстрее left?

SELECT © (16.10.09 21:42) [15]
разница принципиальна! из-за внешнего соединения

а, ты имеешь в виду что u.id может быть NULL, тогда когда c.id будет иметь значение? В данном случае не вижу принципиальной разницы.

Ув. Михаил.
Разберитесь сначала в том, что и когда Вам надо.
Потом, строго после изучения матчасти, вполне возможно обсуждение, где, как и сколько можно сэкономить на запросах. Хотя Вам, я думаю, это вовсе и не надо...

ЗЫ
Без обид - у меня впечатление, что разговор глухого с немым...(

Оптимизация Join а в Postgres Найти похожие ветки