Помогите создать индексы к запросу / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите создать индексы к запросу

25 сообщений из 30, страница 1 из 2

все

Помогите создать индексы к запросу

#39345409

Alexander Krasny

Гость

Есть несколько таблиц:
s_c.selected_parameters_values as tab1,
s_f.messages_titles as tab2,
s_c.categories_data as tab3,
s_c.data_sources as tab4,
s_c.id_messages_direction_types as tab5,
s_c.messages_settings as tab6.
(s_c, s_f - схемы)

Есть запрос

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

SELECT  
tab2.arrive_date, tab2.arrive_date,  
92,  tab2.send_date,  tab2.send_date,  
92,  tab1.data_category,  tab3.name_full,  
297,  tab2.Arm,  tab4.name_full,  
294,  tab2.id_messages_direction_types, tab5.name_full,  
387, tab2.id_message_settings, tab6.name_full, 
382,  tab1.id,  tab1.id, 0,  
tab1.uuid,  tab1.uuid,  0 
FROM s_c.selected_parameters_values as tab1
LEFT JOIN s_f.messages_titles as tab2 ON tab2.id=tab1.id_message_header    
LEFT JOIN s_c.categories_data as tab3 ON tab3.id=tab1.data_category   
LEFT JOIN s_c.data_sources as tab4 ON tab4.id=tab2.Arm   
LEFT JOIN s_c.id_messages_direction_types as tab5 ON tab5.id=tab2.id_messages_direction_types   
LEFT JOIN s_c.messages_settings as tab6 ON tab6.id=tab2.id_message_settings
ORDER BY  tab2.arrive_date, tab1.id ASC ASC OFFSET  0  ROWS  FETCH  NEXT  100  ROWS  ONLY

Работ происходит с базой на 3 миллиона записей.
Первоначально создал индексы btree для таблицы s_f.messages_titles:
(id, arrive_date), (id), (arrive_date);
для таблицы s_content.selected_parameters_values:(id).

Этот запрос без ORDER BY tab2.arrive_date работает от 60 до 70 ms (это меня устраивает).

Но всё дело в "ORDER BY tab2.arrive_date" (тип поля t_timestamp), из-за этого жутко тормозит и запрос выполняется с 2.45 до 3.10 минут.

Прошу помочь ускорить данный запрос связанный с "ORDER BY tab2.arrive_date".
Из Join-ов видно информацию о ключах таблиц. (т.е. если поле называется id - это PK для этой таблицы)

...

Рейтинг:

0 / 0

11.11.2016, 10:31

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345418

Alexander Krasny

Гость

Дополнительная информация от планировщика вот:
Это данные о плане к запросу без "ORDER BY tab2.arrive_date".
"Limit (cost=1.28..288.48 rows=100 width=298) (actual time=0.038..0.518 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1.28..9229252.03 rows=3213492 width=298) (actual time=0.038..0.514 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1.14..8682938.49 rows=3213492 width=173) (actual time=0.036..0.414 rows=100 loops=1)"
" Join Filter: ((id_message_headerid_messages_direction_types20.id)::bigint = (selected_parameters_valuesid_message_header10.id_messages_direction_types)::bigint)"
" Rows Removed by Join Filter: 99"
" -> Nested Loop Left Join (cost=1.14..8586532.70 rows=3213492 width=155) (actual time=0.017..0.365 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=1.00..8064328.31 rows=3213492 width=137) (actual time=0.016..0.293 rows=100 loops=1)"
" -> Nested Loop Left Join (cost=0.86..7526052.40 rows=3213492 width=93) (actual time=0.014..0.212 rows=100 loops=1)"
" -> Index Scan using selected_parameters_values_id_idx on selected_parameters_values selected_parameters_values0 (cost=0.43..128967.81 rows=3213492 width=61) (actual time=0.007..0.021 rows=100 loops=1)"
" -> Index Scan using messages_titles_id_idx on messages_titles selected_parameters_valuesid_message_header10 (cost=0.43..2.29 rows=1 width=48) (actual time=0.001..0.001 rows=1 loops=100)"
" Index Cond: ((id)::bigint = (selected_parameters_values0.id_message_header)::bigint)"
" -> Index Scan using categories_data_pkey on categories_data selected_parameters_valuesdata_category10 (cost=0.14..0.16 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=100)"
" Index Cond: ((id)::bigint = (selected_parameters_values0.data_category)::bigint)"
" -> Index Scan using data_sources_pkey on data_sources id_message_headerarm20 (cost=0.14..0.15 rows=1 width=26) (actual time=0.001..0.001 rows=1 loops=100)"
" Index Cond: ((id)::bigint = (selected_parameters_valuesid_message_header10.arm)::bigint)"
" -> Materialize (cost=0.00..1.03 rows=2 width=26) (actual time=0.000..0.000 rows=2 loops=100)"
" -> Seq Scan on id_messages_direction_types id_message_headerid_messages_direction_types20 (cost=0.00..1.02 rows=2 width=26) (actual time=0.015..0.015 rows=2 loops=1)"
" -> Index Scan using messages_settings_pkey on messages_settings id_message_headerid_message_settings20 (cost=0.14..0.16 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=100)"
" Index Cond: ((id)::bigint = (selected_parameters_valuesid_message_header10.id_message_settings)::bigint)"
"Planning time: 1.041 ms"
"Execution time: 0.596 ms"

Это данные о плане к запросу с "ORDER BY tab2.arrive_date".
"Limit (cost=1210933.88..1210934.13 rows=100 width=298) (actual time=182684.346..182684.355 rows=100 loops=1)"
" -> Sort (cost=1210933.88..1218967.61 rows=3213492 width=298) (actual time=182684.343..182684.346 rows=100 loops=1)"
" Sort Key: selected_parameters_valuesid_message_header10.arrive_date"
" Sort Method: top-N heapsort Memory: 76kB"
" -> Hash Left Join (cost=152340.48..1088116.53 rows=3213492 width=298) (actual time=29886.328..179857.756 rows=3213585 loops=1)"
" Hash Cond: ((selected_parameters_valuesid_message_header10.id_message_settings)::bigint = (id_message_headerid_message_settings20.id)::bigint)"
" -> Hash Left Join (cost=152335.17..1043925.70 rows=3213492 width=173) (actual time=29886.276..177754.236 rows=3213585 loops=1)"
" Hash Cond: ((selected_parameters_valuesid_message_header10.id_messages_direction_types)::bigint = (id_message_headerid_messages_direction_types20.id)::bigint)"
" -> Hash Left Join (cost=152334.12..999739.14 rows=3213492 width=155) (actual time=29886.260..176294.504 rows=3213585 loops=1)"
" Hash Cond: ((selected_parameters_valuesid_message_header10.arm)::bigint = (id_message_headerarm20.id)::bigint)"
" -> Hash Left Join (cost=152332.81..955601.58 rows=3213492 width=137) (actual time=29886.237..174848.419 rows=3213585 loops=1)"
" Hash Cond: ((selected_parameters_values0.data_category)::bigint = (selected_parameters_valuesdata_category10.id)::bigint)"
" -> Hash Right Join (cost=152329.57..911412.82 rows=3213492 width=93) (actual time=29886.174..173382.564 rows=3213585 loops=1)"
" Hash Cond: ((selected_parameters_valuesid_message_header10.id)::bigint = (selected_parameters_values0.id_message_header)::bigint)"
" -> Seq Scan on messages_titles selected_parameters_valuesid_message_header10 (cost=0.00..615495.36 rows=3228636 width=48) (actual time=0.019..83581.593 rows=3228678 loops=1)"
" -> Hash (cost=77640.92..77640.92 rows=3213492 width=61) (actual time=29865.390..29865.390 rows=3213585 loops=1)"
" Buckets: 65536 Batches: 128 Memory Usage: 2882kB"
" -> Seq Scan on selected_parameters_values selected_parameters_values0 (cost=0.00..77640.92 rows=3213492 width=61) (actual time=0.031..25909.846 rows=3213585 loops=1)"
" -> Hash (cost=2.55..2.55 rows=55 width=52) (actual time=0.047..0.047 rows=55 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 13kB"
" -> Seq Scan on categories_data selected_parameters_valuesdata_category10 (cost=0.00..2.55 rows=55 width=52) (actual time=0.015..0.024 rows=55 loops=1)"
" -> Hash (cost=1.14..1.14 rows=14 width=26) (actual time=0.018..0.018 rows=14 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 9kB"
" -> Seq Scan on data_sources id_message_headerarm20 (cost=0.00..1.14 rows=14 width=26) (actual time=0.009..0.012 rows=14 loops=1)"
" -> Hash (cost=1.02..1.02 rows=2 width=26) (actual time=0.012..0.012 rows=2 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 9kB"
" -> Seq Scan on id_messages_direction_types id_message_headerid_messages_direction_types20 (cost=0.00..1.02 rows=2 width=26) (actual time=0.007..0.007 rows=2 loops=1)"
" -> Hash (cost=4.03..4.03 rows=103 width=40) (actual time=0.045..0.045 rows=103 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 16kB"
" -> Seq Scan on messages_settings id_message_headerid_message_settings20 (cost=0.00..4.03 rows=103 width=40) (actual time=0.007..0.023 rows=103 loops=1)"
"Planning time: 0.941 ms"
"Execution time: 182684.838 ms"

...

Рейтинг:

0 / 0

11.11.2016, 10:40

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345436

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

Alexander Krasny,

приведите план запроса в читаемом виде (т.е. со всеми отступами), который выдает psql и оберните его в тег src. и покажите, какие индексы есть в s_f.messages_titles.

...

Рейтинг:

0 / 0

11.11.2016, 11:08

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345444

Alexander Krasny

Гость

Alexius, Первоначально создал индексы btree для таблицы s_f.messages_titles:
(id, arrive_date), (id), (arrive_date);

Писал выше)

...

Рейтинг:

0 / 0

11.11.2016, 11:27

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345449

Alexander Krasny

Гость

Alexius,

Код: sql

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.

"Limit  (cost=1210968.47..1210968.72 rows=100 width=298) (actual time=177999.832..177999.836 rows=100 loops=1)"
"  ->  Sort  (cost=1210968.47..1219002.43 rows=3213585 width=298) (actual time=177999.830..177999.831 rows=100 loops=1)"
"        Sort Key: tab2.arrive_date, tab1.id"
"        Sort Method: top-N heapsort  Memory: 76kB"
"        ->  Hash Left Join  (cost=152343.58..1088147.56 rows=3213585 width=298) (actual time=27814.401..175256.329 rows=3213585 loops=1)"
"              Hash Cond: ((tab2.id_message_settings)::bigint = (tab6.id)::bigint)"
"              ->  Hash Left Join  (cost=152338.26..1043955.45 rows=3213585 width=173) (actual time=27814.310..173253.802 rows=3213585 loops=1)"
"                    Hash Cond: ((tab2.id_messages_direction_types)::bigint = (tab5.id)::bigint)"
"                    ->  Hash Left Join  (cost=152337.22..999767.61 rows=3213585 width=155) (actual time=27814.289..171813.784 rows=3213585 loops=1)"
"                          Hash Cond: ((tab2.arm)::bigint = (tab4.id)::bigint)"
"                          ->  Hash Left Join  (cost=152335.90..955610.56 rows=3213585 width=137) (actual time=27814.255..170352.773 rows=3213585 loops=1)"
"                                Hash Cond: ((tab1.data_category)::bigint = (tab3.id)::bigint)"
"                                ->  Hash Right Join  (cost=152332.66..911420.53 rows=3213585 width=93) (actual time=27814.189..168856.236 rows=3213585 loops=1)"
"                                      Hash Cond: ((tab2.id)::bigint = (tab1.id_message_header)::bigint)"
"                                      ->  Seq Scan on messages_titles tab2  (cost=0.00..615495.78 rows=3228678 width=48) (actual time=0.023..83438.661 rows=3228678 loops=1)"
"                                      ->  Hash  (cost=77641.85..77641.85 rows=3213585 width=61) (actual time=27762.194..27762.194 rows=3213585 loops=1)"
"                                            Buckets: 65536  Batches: 128  Memory Usage: 2882kB"
"                                            ->  Seq Scan on selected_parameters_values tab1  (cost=0.00..77641.85 rows=3213585 width=61) (actual time=0.027..24566.955 rows=3213585 loops=1)"
"                                ->  Hash  (cost=2.55..2.55 rows=55 width=52) (actual time=0.050..0.050 rows=55 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 13kB"
"                                      ->  Seq Scan on categories_data tab3  (cost=0.00..2.55 rows=55 width=52) (actual time=0.019..0.032 rows=55 loops=1)"
"                          ->  Hash  (cost=1.14..1.14 rows=14 width=26) (actual time=0.027..0.027 rows=14 loops=1)"
"                                Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                ->  Seq Scan on data_sources tab4  (cost=0.00..1.14 rows=14 width=26) (actual time=0.011..0.014 rows=14 loops=1)"
"                    ->  Hash  (cost=1.02..1.02 rows=2 width=26) (actual time=0.014..0.014 rows=2 loops=1)"
"                          Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                          ->  Seq Scan on id_messages_direction_types tab5  (cost=0.00..1.02 rows=2 width=26) (actual time=0.008..0.009 rows=2 loops=1)"
"              ->  Hash  (cost=4.03..4.03 rows=103 width=40) (actual time=0.081..0.081 rows=103 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 16kB"
"                    ->  Seq Scan on messages_settings tab6  (cost=0.00..4.03 rows=103 width=40) (actual time=0.032..0.053 rows=103 loops=1)"
"Planning time: 1.110 ms"
"Execution time: 178000.314 ms"

...

Рейтинг:

0 / 0

11.11.2016, 11:35

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345451

Alexander Krasny

Гость

Alexander Krasny, вот план планировщика с ORDER BY tab2.arrive_date.

И небольшая оговорка: я сократил имя схем (s_content = s_c, s_fast = s_f)

...

Рейтинг:

0 / 0

11.11.2016, 11:37

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345455

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexander Krasny,

А переделайте его как (я не причесывал имена полей... идея понятна)

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

SELECT  
tab2.arrive_date, tab2.arrive_date,  
92,  tab2.send_date,  tab2.send_date,  
92,  tab1.data_category,  tab3.name_full,  
297,  tab2.Arm,  tab4.name_full,  
294,  tab2.id_messages_direction_types, tab5.name_full,  
387, tab2.id_message_settings, tab6.name_full, 
382,  tab1.id,  tab1.id, 0,  
tab1.uuid,  tab1.uuid,  0 
FROM (
   SELECT * 
   FROM s_c.selected_parameters_values as tab1
   LEFT JOIN s_f.messages_titles as tab2 ON tab2.id=tab1.id_message_header    
   ORDER BY  tab2.arrive_date, tab1.id LIMIT 100
) AS _t
LEFT JOIN s_c.categories_data as tab3 ON tab3.id=tab1.data_category   
LEFT JOIN s_c.data_sources as tab4 ON tab4.id=tab2.Arm   
LEFT JOIN s_c.id_messages_direction_types as tab5 ON tab5.id=tab2.id_messages_direction_types   
LEFT JOIN s_c.messages_settings as tab6 ON tab6.id=tab2.id_message_settings
ORDER BY  _t.arrive_date, _t.id LIMIT 100

Суть в том чтобы СНАЧАЛА подсчитать нужные вам 100 строк из tab1/tab2
а потом уже к ним цеплять все остальное.

--
Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

11.11.2016, 11:40

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345474

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim Boguk,

дальше можно брать 100 строк из полного джойна, смешать со 100 строками из антиджойна и отсортировать/отлимитить (а потом слева навесить оставшееся). не думаю, что оптимайзер пж такое сумеет сам (обычно такие простейшие выкладки ему не доступны).

...

Рейтинг:

0 / 0

11.11.2016, 12:03

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345477

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

qwwqMaxim Boguk,

дальше можно брать 100 строк из полного ( INNER, not FULL) джойна, смешать со 100 строками из антиджойна и отсортировать/отлимитить (а потом слева навесить оставшееся). не думаю, что оптимайзер пж такое сумеет сам (обычно такие простейшие выкладки ему не доступны).

-- поправил

...

Рейтинг:

0 / 0

11.11.2016, 12:05

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345577

Alexander Krasny

Гость

Maxim Boguk, большое спасибо, я сегодня же это попробую. Тогда задам еще вопрос, может нужны какие-то индексы еще? И правильно ли я понял, что мои индексы планировщик игнорит?

...

Рейтинг:

0 / 0

11.11.2016, 13:42

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345622

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexander KrasnyMaxim Boguk, большое спасибо, я сегодня же это попробую. Тогда задам еще вопрос, может нужны какие-то индексы еще? И правильно ли я понял, что мои индексы планировщик игнорит?

А чем ваши индексы могут помочь для основного блока запроса?
Когда у вас сортировка по полям из 2х таблиц идет.
Кстати у вас там точно left join между s_c.selected_parameters_values и s_f.messages_titles а не inner join?
Как то очень странно видеть запрос с первым полем в сортировке из левой стороны left join.

--
Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

11.11.2016, 14:17

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345864

Alexander Krasny

Гость

Maxim Boguk, я просто намеревался с помощью индексов хоть как-то улучшить скорость. Поймите, я новичёк в таких интересных вещах..
В универе меня научили лишь делать представления, процедуры, функции, тригеры, таблицы, как с правами пользователя обращаться. Но вот таких неординарных вещей нам не объясняли..

...

Рейтинг:

0 / 0

11.11.2016, 17:57

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39345869

Alexander Krasny

Гость

Maxim BogukКстати у вас там точно left join между s_c.selected_parameters_values и s_f.messages_titles а не inner join?
Как то очень странно видеть запрос с первым полем в сортировке из левой стороны left join.

Я еще разберусь в структуре, может ли у меня поле id_message_header из s_c.selected_parameters_values быть пустым.

...

Рейтинг:

0 / 0

11.11.2016, 18:01

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346797

Alexander Krasny

Гость

Maxim Boguk, воспользовался вашим советом.
Получилось что-то такое:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SELECT  
t2_ad, t2_ad,  
92,  t2_sd,  t2_sd,  
92,  t1_dc,  tab3.name_full,  
297,  t2_arm,  tab4.name_full,  
294,  t2_mdt, tab5.name_full,  
387, t2_ims, tab6.name_full, 
382,  t1_id,  t1_id, 0,  
t1_uuid,  t1_uuid,  0 
FROM (
   SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM s_content.selected_parameters_values as tab1
   LEFT JOIN s_fast.messages_titles as tab2 ON tab2.id=tab1.id_message_header    
   ORDER BY  tab2.arrive_date, tab1.id LIMIT 100
) AS _t
LEFT JOIN s_content.categories_data as tab3 ON tab3.id=_t.t1_dc  
LEFT JOIN s_content.data_sources as tab4 ON tab4.id=_t.t2_arm   
LEFT JOIN s_content.id_messages_direction_types as tab5 ON tab5.id=_t.t2_mdt   
LEFT JOIN s_content.messages_settings as tab6 ON tab6.id=_t.t2_ims
ORDER BY  _t.t2_ad, _t.t1_id LIMIT 100

Скорость выполнения такого улучшилась где-то от 8 до 10 секунд.
Может стоит попробовать сделать то же и с остальными LEFT JOIN-ами?

...

Рейтинг:

0 / 0

14.11.2016, 13:13

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346807

Alexander Krasny

Гость

Maxim Boguk, вот план планировщика

Код: sql

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.

"Limit  (cost=1034240.38..1034511.81 rows=100 width=314) (actual time=27256.265..27259.830 rows=100 loops=1)"
"  ->  Nested Loop Left Join  (cost=1034240.38..1034511.81 rows=100 width=314) (actual time=27256.264..27259.817 rows=100 loops=1)"
"        Join Filter: ((tab6.id)::bigint = (tab2.id_message_settings)::bigint)"
"        Rows Removed by Join Filter: 10108"
"        ->  Nested Loop Left Join  (cost=1034240.38..1034353.02 rows=100 width=173) (actual time=27256.219..27257.739 rows=100 loops=1)"
"              Join Filter: ((tab5.id)::bigint = (tab2.id_messages_direction_types)::bigint)"
"              Rows Removed by Join Filter: 99"
"              ->  Nested Loop Left Join  (cost=1034240.38..1034348.99 rows=100 width=155) (actual time=27256.202..27257.643 rows=100 loops=1)"
"                    Join Filter: ((tab4.id)::bigint = (tab2.arm)::bigint)"
"                    Rows Removed by Join Filter: 1296"
"                    ->  Nested Loop Left Join  (cost=1034240.38..1034326.82 rows=100 width=137) (actual time=27256.172..27257.305 rows=100 loops=1)"
"                          Join Filter: ((tab3.id)::bigint = (tab1.data_category)::bigint)"
"                          Rows Removed by Join Filter: 5398"
"                          ->  Limit  (cost=1034240.38..1034240.63 rows=100 width=93) (actual time=27256.107..27256.138 rows=100 loops=1)"
"                                ->  Sort  (cost=1034240.38..1042274.26 rows=3213551 width=93) (actual time=27256.107..27256.129 rows=100 loops=1)"
"                                      Sort Key: tab2.arrive_date, tab1.id"
"                                      Sort Method: top-N heapsort  Memory: 39kB"
"                                      ->  Hash Right Join  (cost=152331.90..911420.77 rows=3213551 width=93) (actual time=3562.544..26091.300 rows=3213585 loops=1)"
"                                            Hash Cond: ((tab2.id)::bigint = (tab1.id_message_header)::bigint)"
"                                            ->  Seq Scan on messages_titles tab2  (cost=0.00..615496.61 rows=3228761 width=48) (actual time=0.067..16001.899 rows=3228678 loops=1)"
"                                            ->  Hash  (cost=77641.51..77641.51 rows=3213551 width=61) (actual time=3550.010..3550.010 rows=3213585 loops=1)"
"                                                  Buckets: 65536  Batches: 128  Memory Usage: 2882kB"
"                                                  ->  Seq Scan on selected_parameters_values tab1  (cost=0.00..77641.51 rows=3213551 width=61) (actual time=0.019..1666.856 rows=3213585 loops=1)"
"                          ->  Materialize  (cost=0.00..2.82 rows=55 width=52) (actual time=0.000..0.004 rows=55 loops=100)"
"                                ->  Seq Scan on categories_data tab3  (cost=0.00..2.55 rows=55 width=52) (actual time=0.027..0.042 rows=55 loops=1)"
"                    ->  Materialize  (cost=0.00..1.21 rows=14 width=26) (actual time=0.000..0.001 rows=14 loops=100)"
"                          ->  Seq Scan on data_sources tab4  (cost=0.00..1.14 rows=14 width=26) (actual time=0.012..0.016 rows=14 loops=1)"
"              ->  Materialize  (cost=0.00..1.03 rows=2 width=26) (actual time=0.000..0.000 rows=2 loops=100)"
"                    ->  Seq Scan on id_messages_direction_types tab5  (cost=0.00..1.02 rows=2 width=26) (actual time=0.010..0.011 rows=2 loops=1)"
"        ->  Materialize  (cost=0.00..4.54 rows=103 width=40) (actual time=0.000..0.006 rows=102 loops=100)"
"              ->  Seq Scan on messages_settings tab6  (cost=0.00..4.03 rows=103 width=40) (actual time=0.012..0.043 rows=103 loops=1)"
"Planning time: 1.270 ms"
"Execution time: 27260.152 ms"

...

Рейтинг:

0 / 0

14.11.2016, 13:29

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346816

Alexander Krasny

Гость

Maxim Boguk, ускорил, отключив seqscan на 5 секунд.
План планировщика такой:

Код: sql

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.

"Limit  (cost=1785307.42..1785421.29 rows=100 width=314) (actual time=23260.293..23261.259 rows=100 loops=1)"
"  ->  Nested Loop Left Join  (cost=1785307.42..1785421.29 rows=100 width=314) (actual time=23260.292..23261.240 rows=100 loops=1)"
"        ->  Nested Loop Left Join  (cost=1785307.28..1785384.29 rows=100 width=173) (actual time=23260.258..23260.916 rows=100 loops=1)"
"              Join Filter: ((tab5.id)::bigint = (tab2.id_messages_direction_types)::bigint)"
"              Rows Removed by Join Filter: 99"
"              ->  Nested Loop Left Join  (cost=1785307.15..1785369.13 rows=100 width=155) (actual time=23260.215..23260.775 rows=100 loops=1)"
"                    ->  Nested Loop Left Join  (cost=1785307.02..1785340.88 rows=100 width=137) (actual time=23260.183..23260.506 rows=100 loops=1)"
"                          ->  Limit  (cost=1785306.88..1785307.13 rows=100 width=93) (actual time=23260.143..23260.175 rows=100 loops=1)"
"                                ->  Sort  (cost=1785306.88..1793340.38 rows=3213403 width=93) (actual time=23260.142..23260.168 rows=100 loops=1)"
"                                      Sort Key: tab2.arrive_date, tab1.id"
"                                      Sort Method: top-N heapsort  Memory: 39kB"
"                                      ->  Merge Left Join  (cost=7444.71..1662492.92 rows=3213403 width=93) (actual time=109.655..21623.134 rows=3213585 loops=1)"
"                                            Merge Cond: ((tab1.id_message_header)::bigint = (tab2.id)::bigint)"
"                                            ->  Index Scan using ind_id_msg_header on selected_parameters_values tab1  (cost=0.43..128968.71 rows=3213403 width=61) (actual time=0.013..1944.228 rows=3213585 loops=1)"
"                                            ->  Index Scan using ind_id on messages_titles tab2  (cost=0.43..1485284.38 rows=3228918 width=48) (actual time=0.009..15978.248 rows=3228678 loops=1)"
"                          ->  Index Scan using ind_id_cd on categories_data tab3  (cost=0.14..0.32 rows=1 width=52) (actual time=0.002..0.002 rows=1 loops=100)"
"                                Index Cond: ((id)::bigint = (tab1.data_category)::bigint)"
"                    ->  Index Scan using ind_id_ds on data_sources tab4  (cost=0.14..0.27 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=100)"
"                          Index Cond: ((id)::bigint = (tab2.arm)::bigint)"
"              ->  Materialize  (cost=0.13..12.17 rows=2 width=26) (actual time=0.000..0.001 rows=2 loops=100)"
"                    ->  Index Scan using ind_id_mdt on id_messages_direction_types tab5  (cost=0.13..12.16 rows=2 width=26) (actual time=0.028..0.029 rows=2 loops=1)"
"        ->  Index Scan using ind_id_msgs on messages_settings tab6  (cost=0.14..0.36 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=100)"
"              Index Cond: ((id)::bigint = (tab2.id_message_settings)::bigint)"
"Planning time: 3.239 ms"
"Execution time: 23261.448 ms"

...

Рейтинг:

0 / 0

14.11.2016, 13:38

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346834

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

попробуйте как--то так

Код: plsql

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.
37.
38.
39.
40.

SELECT  
t2_ad, t2_ad,  
92,  t2_sd,  t2_sd,  
92,  t1_dc,  tab3.name_full,  
297,  t2_arm,  tab4.name_full,  
294,  t2_mdt, tab5.name_full,  
387, t2_ims, tab6.name_full, 
382,  t1_id,  t1_id, 0,  
t1_uuid,  t1_uuid,  0 
FROM (
SELECT * FROM
 (
   SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc
, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM s_content.selected_parameters_values as tab1
   INNER JOIN s_fast.messages_titles as tab2 ON tab2.id=tab1.id_message_header
           -- можно загнать на lateral t1 по t2 с разумным лимитом
   ORDER BY  tab2.arrive_date , tab1.id  LIMIT 100 

 ) foo1
UNION ALL -- можно попытаться родить with recursive с вычислением дельты лимита для антиджойна

 (
   SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc
, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM s_content.selected_parameters_values as tab1
   LEFT JOIN s_fast.messages_titles as tab2 ON tab2.id=tab1.id_message_header
WHERE tab2.id /*или что там*/ IS NULL    
   ORDER BY  /*tab2.arrive_date,*/ tab1.id LIMIT 100

 ) foo2
ORDER BY  t2_ad, t1_id LIMIT 100
) AS _t
LEFT JOIN s_content.categories_data as tab3 ON tab3.id=_t.t1_dc  
LEFT JOIN s_content.data_sources as tab4 ON tab4.id=_t.t2_arm   
LEFT JOIN s_content.id_messages_direction_types as tab5 ON tab5.id=_t.t2_mdt   
LEFT JOIN s_content.messages_settings as tab6 ON tab6.id=_t.t2_ims
ORDER BY  _t.t2_ad, _t.t1_id LIMIT 100

...

Рейтинг:

0 / 0

14.11.2016, 13:54

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346836

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

qwwq,

скобок унутре добавьте только, обсчитался

...

Рейтинг:

0 / 0

14.11.2016, 13:56

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346837

Alexander Krasny

Гость

qwwq, ЩА ПОПробую))

...

Рейтинг:

0 / 0

14.11.2016, 13:57

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346839

Alexander Krasny

Гость

qwwq, мне непонятны комментарии ваши в коде(

...

Рейтинг:

0 / 0

14.11.2016, 13:58

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346851

Alexander Krasny

Гость

qwwq, поясните пожалуйста, для меня ламера, где не хватает скобки в вашем запросе(
Я скопировал, попробовал так, так, так... Не получается

...

Рейтинг:

0 / 0

14.11.2016, 14:12

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346863

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

qwwqпопробуйте как--то так

Код: plsql

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.
37.
38.
39.
40.
41.
42.

SELECT  
t2_ad, t2_ad,  
92,  t2_sd,  t2_sd,  
92,  t1_dc,  tab3.name_full,  
297,  t2_arm,  tab4.name_full,  
294,  t2_mdt, tab5.name_full,  
387, t2_ims, tab6.name_full, 
382,  t1_id,  t1_id, 0,  
t1_uuid,  t1_uuid,  0 
FROM (
SELECT * FROM
(
 (
   SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc
, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM s_content.selected_parameters_values as tab1
   INNER JOIN s_fast.messages_titles as tab2 ON tab2.id=tab1.id_message_header
           -- можно загнать на lateral t1 по t2 с разумным лимитом <=100
   ORDER BY  tab2.arrive_date , tab1.id  LIMIT 100 

 ) foo1
UNION ALL -- можно попытаться родить with recursive с вычислением дельты лимита для антиджойна

 (
   SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc
, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM s_content.selected_parameters_values as tab1
   LEFT JOIN s_fast.messages_titles as tab2 ON tab2.id=tab1.id_message_header
WHERE tab2.id /*или что там*/ IS NULL    
   ORDER BY  /*tab2.arrive_date,*/ tab1.id LIMIT 100

 ) foo2
) foo
ORDER BY  t2_ad, t1_id LIMIT 100
) AS _t
LEFT JOIN s_content.categories_data as tab3 ON tab3.id=_t.t1_dc  
LEFT JOIN s_content.data_sources as tab4 ON tab4.id=_t.t2_arm   
LEFT JOIN s_content.id_messages_direction_types as tab5 ON tab5.id=_t.t2_mdt   
LEFT JOIN s_content.messages_settings as tab6 ON tab6.id=_t.t2_ims
ORDER BY  _t.t2_ad, _t.t1_id LIMIT 100

примерно так

комментарии означают комментарии. хинтов в пж не бывает.

например

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

(
   SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc
, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM s_content.selected_parameters_values as tab1
   INNER JOIN s_fast.messages_titles as tab2 ON tab2.id=tab1.id_message_header
           -- можно загнать на lateral t1 по t2 с разумным лимитом <=100
   ORDER BY  tab2.arrive_date , tab1.id  LIMIT 100 

 ) foo1

можно попробовать загнать руками на (вероятно) правильный план примерно так

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

(
   SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc
, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM   s_fast.messages_titles as tab2 
--   INNER JOIN
,LATERAL (SELECT * FROM s_content.selected_parameters_values as tab1 
   WHERE tab2.id=tab1.id_message_header
     ORDER BY  tab1.id  LIMIT 100 
) as tab1
   ORDER BY  tab2.arrive_date , tab1.id  LIMIT 100
 ) foo1

...

Рейтинг:

0 / 0

14.11.2016, 14:23

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346872

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

qwwq,

откуда невооруженным глазом видим, что напрашивается индекс по tab1 (id_message_header,id) (или применение хенджобной техники а--ля лузиндекскан -- для быстрого обрезания сорта вдоль неуникального инд--а с уникализирующим постфиксом в ордербай--листе -- чего пж сам не умеет, помнится)

...

Рейтинг:

0 / 0

14.11.2016, 14:30

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346876

Alexander Krasny

Гость

qwwq, правильно ли я понял вас:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SELECT  
t2_ad, t2_ad,  
92,  t2_sd,  t2_sd,  
92,  t1_dc,  tab3.name_full,  
297,  t2_arm,  tab4.name_full,  
294,  t2_mdt, tab5.name_full,  
387, t2_ims, tab6.name_full, 
382,  t1_id,  t1_id, 0,  
t1_uuid,  t1_uuid,  0 
FROM (SELECT tab2.arrive_date as t2_ad, tab2.send_date as t2_sd, tab1.data_category as t1_dc, tab2.Arm as t2_arm, tab2.id_messages_direction_types as t2_mdt,
   tab2.id_message_settings as t2_ims, tab1.id as t1_id, tab1.uuid as t1_uuid
   FROM s_content.selected_parameters_values as tab1 INNER JOIN s_fast.messages_titles as tab2 ON tab2.id=tab1.id_message_header,
LATERAL (SELECT * FROM s_content.selected_parameters_values as tab1 
   WHERE tab2.id=tab1.id_message_header
     ORDER BY  tab1.id  LIMIT 100 
) foo1) AS _t
LEFT JOIN s_content.categories_data as tab3 ON tab3.id=_t.t1_dc  
LEFT JOIN s_content.data_sources as tab4 ON tab4.id=_t.t2_arm   
LEFT JOIN s_content.id_messages_direction_types as tab5 ON tab5.id=_t.t2_mdt   
LEFT JOIN s_content.messages_settings as tab6 ON tab6.id=_t.t2_ims
ORDER BY  _t.t2_ad, _t.t1_id LIMIT 100

...

Рейтинг:

0 / 0

14.11.2016, 14:32

| Ответить | Цитировать | Написать

Помогите создать индексы к запросу

#39346892

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Alexander Krasnyqwwq, правильно ли я понял вас:
почти
вы потеряли второй компонент юнион-олла.

в юнионе у вас д.б. 100 (или менее) записей из полного джойна т1 т2
и остаток (не более 100) -- из аниджойна т1 т2.
всё смешать и отсортировать по новой.

вот остаток вы и потеряли.

...

Рейтинг:

0 / 0

14.11.2016, 14:48

| Ответить | Цитировать | Написать

25 сообщений из 30, страница 1 из 2

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите создать индексы к запросу

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39345577&tid=1996875]:	0ms
get settings:	11ms
get forum list:	15ms
check forum access:	5ms
check topic access:	5ms
track hit:	166ms
get topic data:	13ms
get forum data:	3ms
get page messages:	61ms
get tp. blocked users:	2ms
others:	300ms

total:	581ms