ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
6 сообщений из 6, страница 1 из 1
Оптимизация запроса
    #34691328
holem
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
holem
Гость
Привет всем!
Бьюсь над оптимизацией запроса.. никак не получается улучшить результат.

Запрос такой
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
EXPLAIN ANALYZE SELECT
  "News"."Id",
  "News"."Title",
  "News"."SourceUrl",
  "News"."PublicDate",
  "News"."Severity",
  "News"."RssId",
  "News"."ThemeId",
  "RssChannels"."Title" AS "RssTitle",
  "RssChannels"."Url" AS "RssUrl",
  "Agencies"."Url" AS "AgencyUrl",
  "UserFavorites"."NewsId" AS "InFavorites",
  "News"."Content" 
FROM "News" 
LEFT JOIN "RssChannels" ON ("News"."RssId" = "RssChannels"."Id") 
LEFT JOIN "Agencies" ON ("RssChannels"."AgencyId" = "Agencies"."Id") 
LEFT JOIN "UserChoosedChannels" ON ("News"."RssId" = "UserChoosedChannels"."RssId") 
LEFT JOIN "UserFavorites" ON ("News"."Id" = "UserFavorites"."NewsId") 
WHERE "News"."PublicDate" > '2007-03-02 00:13:37' 
AND "UserChoosedChannels"."UserId" = '3' 
AND "News"."RssId" = "UserChoosedChannels"."RssId" 
AND "UserChoosedChannels"."Section" = 'lenta' 
ORDER BY "News"."PublicDate" DESC 
LIMIT  10
результат
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
"Limit  (cost=3807.43..3807.45 rows=10 width=2640) (actual time=902.846..902.877 rows=10 loops=1)"
"  ->  Sort  (cost=3807.43..3808.18 rows=301 width=2640) (actual time=902.838..902.856 rows=10 loops=1)"
"        Sort Key: "News"."PublicDate""
"        ->  Hash Left Join  (cost=295.92..3795.04 rows=301 width=2640) (actual time=13.441..564.584 rows=13269 loops=1)"
"              Hash Cond: ("RssChannels"."AgencyId" = "Agencies"."Id")"
"              ->  Hash Left Join  (cost=272.84..3767.81 rows=301 width=2128) (actual time=11.498..529.665 rows=13269 loops=1)"
"                    Hash Cond: ("News"."Id" = "UserFavorites"."NewsId")"
"                    ->  Hash Left Join  (cost=271.79..3765.63 rows=301 width=2124) (actual time=11.425..502.627 rows=13269 loops=1)"
"                          Hash Cond: ("News"."RssId" = "RssChannels"."Id")"
"                          ->  Nested Loop  (cost=39.16..3272.86 rows=301 width=1088) (actual time=5.987..461.491 rows=13269 loops=1)"
"                                ->  Seq Scan on "UserChoosedChannels"  (cost=0.00..3.41 rows=1 width=4) (actual time=0.069..0.489 rows=48 loops=1)"
"                                      Filter: (("UserId" = 3) AND (("Section")::text = 'lenta'::text))"
"                                ->  Bitmap Heap Scan on "News"  (cost=39.16..3258.16 rows=903 width=1088) (actual time=3.818..9.040 rows=276 loops=48)"
"                                      Recheck Cond: ("News"."RssId" = "UserChoosedChannels"."RssId")"
"                                      Filter: ("PublicDate" > '2007-03-02 00:13:37'::timestamp without time zone)"
"                                      ->  Bitmap Index Scan on "News_RssId_index"  (cost=0.00..39.08 rows=903 width=0) (actual time=0.473..0.473 rows=1044 loops=48)"
"                                            Index Cond: ("News"."RssId" = "UserChoosedChannels"."RssId")"
"                          ->  Hash  (cost=44.17..44.17 rows=1317 width=1040) (actual time=5.341..5.341 rows=1317 loops=1)"
"                                ->  Seq Scan on "RssChannels"  (cost=0.00..44.17 rows=1317 width=1040) (actual time=0.020..2.191 rows=1317 loops=1)"
"                    ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.026..0.026 rows=2 loops=1)"
"                          ->  Seq Scan on "UserFavorites"  (cost=0.00..1.02 rows=2 width=4) (actual time=0.009..0.013 rows=2 loops=1)"
"              ->  Hash  (cost=15.26..15.26 rows=626 width=520) (actual time=1.898..1.898 rows=626 loops=1)"
"                    ->  Seq Scan on "Agencies"  (cost=0.00..15.26 rows=626 width=520) (actual time=0.028..0.903 rows=626 loops=1)"
"Total runtime: 913.154 ms"
Структура таблиц в общем-то понятна из запроса. Проблема в том, что таблица News большая (~150000 pfgbctq) и почему-то постгрес сначала делает сортировку по полю PublicDate, а потом уже применяет условия WHERE..
Можно ли как-то специально указать в запросе, чтоб сначала применялись условия, указанные во WHERE, а потом уже делалась сортировка..?
Спасибо за внимание
...
Рейтинг: 0 / 0
29.07.2007, 21:56
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #34691331
holem
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
holem
Гость
Да, забыл сказать
Индексы есть на News.PublicDate и News.RssId, и на поля, участвующие в JOIN
...
Рейтинг: 0 / 0
29.07.2007, 21:59
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #34691853
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
WHERE "News"."PublicDate" > '2007-03-02 00:13:37'
AND "UserChoosedChannels"."UserId" = '3'
AND "News"."RssId" = "UserChoosedChannels"."RssId"
AND "UserChoosedChannels"."Section" = 'lenta'

1. повторяете условие JOIN-а ("News"."RssId" = "UserChoosedChannels"."RssId") . Зачем? Вместо внешнего забабачьте внутреннее соединение, и выбросьте красненькое из WHERE

далее

2. - напрашивается составные индексы по всем полям WHERE,
2.а. на таблу UserChoosedChannels по полям UserId,Section, порядок промеж них не важен(можете точнее определить из других соображений), вероятно добавить именно в _конец_ этого индекса , (...,RssId) (что не очевидно - зависит от оптимизатора, но из общих соображений - должно быть во многих случаях наилучшим решением)
2.б. News - возможно (надо проверять), вместо (или в дополнение к) индекса на PublicDate вам не помешает составной индекс на (RssId,PublicDate) (и именно в этом порядке).
...
Рейтинг: 0 / 0
30.07.2007, 10:52
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #34694256
holem
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
holem
Гость
4321
Спасибо, сделал еще составной индекс по RssId и PublicDate в таблице News, и в UserChoosedChannels составной по UserId, Section и RssId. В результате время стало порядка 550ms.. но все равно картина та же - первоначальная сортировка во всей таблице по полю PublicDate, как и в первом посте. Если делать сортировку по RssId или Id - время выполнения порядка 30ms.
Как я понимаю индекс по PublicDate не применяется.. может дело в методе доступа? все индексы делаю btree. Или есть способ принудительно указать использовать индекс в запросе?
...
Рейтинг: 0 / 0
30.07.2007, 23:20
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #34695620
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
holem 4321
Спасибо, сделал еще составной индекс по RssId и PublicDate в таблице News, и в UserChoosedChannels составной по UserId, Section и RssId. В результате время стало порядка 550ms.. но все равно картина та же - первоначальная сортировка во всей таблице по полю PublicDate, как и в первом посте. Если делать сортировку по RssId или Id - время выполнения порядка 30ms.
Как я понимаю индекс по PublicDate не применяется.. может дело в методе доступа? все индексы делаю btree. Или есть способ принудительно указать использовать индекс в запросе?
время, кстати сказать, вполне таки нормальное для такого запроса, НО (если охота пуще неволи)...

так и не понял, вам нужно таки невнешнее объединение (т.е. в WHERE проверять не пустоту свзязанностей)? Если да - на кой у вас там именно лефт джойны? Если нет - то боюсь от такой последовательности не избавиться. Правда можно (если считать гарантированным наличие 10-ка требуемых левых частей для некоего поднабора данных (скажем 1000) попытаться сразу зарезать на порядки выборку по News

т.е. придумать что-то наподобие:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
SELECT
  "News"."Id",
  "News"."Title",
  "News"."SourceUrl",
  "News"."PublicDate",
  "News"."Severity",
  "News"."RssId",
  "News"."ThemeId",
  "RssChannels"."Title" AS "RssTitle",
  "RssChannels"."Url" AS "RssUrl",
  "Agencies"."Url" AS "AgencyUrl",
  "UserFavorites"."NewsId" AS "InFavorites",
  "News"."Content" 
FROM (
SELECT
  "Id",
  "Title",
  "SourceUrl",
  "PublicDate",
  "Severity",
  "RssId",
  "ThemeId",
  "News"."Content" 
FROM "News" 
WHERE "News"."PublicDate" > '2007-03-02 00:13:37' 
ORDER BY "News"."PublicDate" DESC 
LIMIT 1000) foo
LEFT JOIN "RssChannels" ON ("foo"."RssId" = "RssChannels"."Id") 
LEFT JOIN "Agencies" ON ("RssChannels"."AgencyId" = "Agencies"."Id") 
LEFT JOIN "UserChoosedChannels" ON ("foo"."RssId" = "UserChoosedChannels"."RssId") 
LEFT JOIN "UserFavorites" ON ("foo"."Id" = "UserFavorites"."NewsId") 
WHERE 
"UserChoosedChannels"."UserId" = '3' 
--AND "foo"."RssId" = "UserChoosedChannels"."RssId" 
AND "UserChoosedChannels"."Section" = 'lenta' 
ORDER BY "foo"."PublicDate" DESC 
LIMIT 10



=======================================
PS кстати, можно еще например слепить на триггерах вспомогательную табличечку, включающую
"UserChoosedChannels"."UserId","UserChoosedChannels"."Section" и соответсвенное "News"."PublicDate"
ну и, соответсвенно NewsId,RssId, и т.п. - в силу необходимости вязаться к оным (если связь не много - много, (много вариантов того и другого при заданном RssId) возможно хватит служебно-поддерживаемого поля PublicDate в UserChoosedChannels)... и на нее таки навесить индекс по искомой тройке ("UserId","Section","PublicDate"). Тогда ваш запрос сведется к запросу к одной служебной табличке, (строго по ее индексу) + связка всего прочего по мере необходимости добавления полей. однако стоимость такой бодяги будет весьма велика - и размер записи не мал, и число, кажется, близко к декарту ключей. (хорошо, б если вся эта "табличка" физически, на диске, была просто индексом, ан, похоже, так не получается)


Второй способ - написать хп-ку, где руками в лупе по
SELECT * FROM "News" ORDER BY "News"."PublicDate" DESC
,в случае нахождения соответственных связок в прочих таблицах (вложенными лупами), выкидывать 10 первых записей и прикрывать лавочку.
Можно ли такое поведение замутить чисто запросом, и как его для этого склепать - сказать трудно. Скорее всего - нет (если только структурой не гарантировано не более одной записи ньюс на исходящую запись - тогда можно попробовать записать то же через поля-подзапросы вместо джойнов).
...
Рейтинг: 0 / 0
31.07.2007, 14:28
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #34695625
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
зы ю в запросе с урезанием в первой части забыл заменить "News" на "foo"
...
Рейтинг: 0 / 0
31.07.2007, 14:30
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]