ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Нужна помощь с оптимизацией запроса
25 сообщений из 32, страница 1 из 2
  1  все
Нужна помощь с оптимизацией запроса
    #35711823
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Сам запрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
 SELECT pl.domain, pl.status_id,
 pl.id, pl.status_set_date AS moderation_duration,
 count(pg.id) AS pages_count,
 SUM(CASE WHEN pg.pr IS NULL THEN  1  END) AS no_pr,
 SUM(CASE WHEN pg.yap IS NULL THEN  1  END) AS no_yap,
 SUM(CASE WHEN pg.gop IS NULL THEN  1  END) AS no_gop
 FROM platforms AS pl JOIN pages AS pg ON (pl.id = pg.platform_id)
 WHERE pl.getting_seo_status_id <>  123 
 GROUP BY pl.domain, pl.status_id, pl.id, pl.status_set_date;
Platforms - 23000 строк
Pages - 9 миллионов строк
Explain analyze
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
HashAggregate  (cost= 239937 . 37 .. 240036 . 39  rows= 4951  width= 42 ) (actual time= 96789 . 593 .. 96789 . 689  rows= 39  loops= 1 )
   ->  Merge Join  (cost= 1125 . 06 .. 236643 . 07  rows= 164715  width= 42 ) (actual time= 19303 . 194 .. 96644 . 125  rows= 68779  loops= 1 )
         Merge Cond: (pl.id = pg.platform_id)
         ->  Index Scan using platforms_pkey on platforms pl  (cost= 0 . 00 .. 6860 . 14  rows= 4951  width= 32 ) (actual time= 133 . 924 .. 495 . 946  rows= 415  loops= 1 )
               Filter: (getting_seo_status_id <>  123 )
         ->  Index Scan using index_pages_on_platform_id on pages pg  (cost= 0 . 00 .. 195641 . 07  rows= 13003481  width= 14 ) (actual time= 0 . 201 .. 76400 . 827  rows= 12155048  loops= 1 )
 Total runtime:  96789 . 965  ms
Что делать? Что тут можно оптимизировать?
В Postgres не сильно шарю, к сожалению. До этого работал с Mysql и MS SQL Server
...
Рейтинг: 0 / 0
12.12.2008, 18:05
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35711864
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
постройте индекс по pages.platform_id
...
Рейтинг: 0 / 0
12.12.2008, 18:25
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35711896
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Есть такой индекс, разумеется.
Код: plaintext
"index_pages_on_platform_id" btree (platform_id)
...
Рейтинг: 0 / 0
12.12.2008, 18:42
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35712307
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Alexey Nayden пишет:

> SELECT pl.domain, pl.status_id,
> pl.id, pl.status_set_date AS moderation_duration,
> count(pg.id) AS pages_count,
> SUM(CASE WHEN pg.pr IS NULL THEN *1* END) AS no_pr,
> SUM(CASE WHEN pg.yap IS NULL THEN *1* END) AS no_yap,
> SUM(CASE WHEN pg.gop IS NULL THEN *1* END) AS no_gop
> FROM platforms AS pl JOIN pages AS pg ON (pl.id = pg.platform_id)
> WHERE pl.getting_seo_status_id <> *123*
> GROUP BY pl.domain, pl.status_id, pl.id, pl.status_set_date;

Тут можно оптимизировать только JOIN. SARG-ов нет, искать нечего, будут
обрабатываться все строки.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
13.12.2008, 00:10
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35712477
Gold_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gold_
Гость
А покажите пжл результаты запроса
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select n, current_setting(n) from (values
  ('enable_bitmapscan'),
  ('enable_hashagg'),
  ('enable_hashjoin'),
  ('enable_indexscan'),
  ('enable_mergejoin'),
  ('enable_nestloop'),
  ('enable_seqscan'),
  ('enable_sort'),
  ('enable_tidscan')
) as s(n);
...
Рейтинг: 0 / 0
13.12.2008, 12:17
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35712883
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Gold_А покажите пжл результаты запроса
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select n, current_setting(n) from (values
  ('enable_bitmapscan'),
  ('enable_hashagg'),
  ('enable_hashjoin'),
  ('enable_indexscan'),
  ('enable_mergejoin'),
  ('enable_nestloop'),
  ('enable_seqscan'),
  ('enable_sort'),
  ('enable_tidscan')
) as s(n);

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
         n         | current_setting 
-------------------+-----------------
 enable_bitmapscan | on
 enable_hashagg    | on
 enable_hashjoin   | on
 enable_indexscan  | on
 enable_mergejoin  | on
 enable_nestloop   | on
 enable_seqscan    | on
 enable_sort       | on
 enable_tidscan    | on
...
Рейтинг: 0 / 0
13.12.2008, 22:39
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713245
Gold_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gold_
Гость
VACUUM FULL ANALYSE делается?
покажите пжл результаты запроса
Код: plaintext
1.
2.
 
SELECT getting_seo_status_id , COUNT(*) FROM platforms GROUP BY  1
...
Рейтинг: 0 / 0
14.12.2008, 16:22
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713561
Gold_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gold_
Гость
так много, давай так:
Код: plaintext
1.
2.
3.
4.
SELECT COUNT(*) FROM
(
SELECT getting_seo_status_id , COUNT(*) FROM platforms GROUP BY  1 )
 S
...
Рейтинг: 0 / 0
14.12.2008, 23:30
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713585
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Gold_VACUUM FULL ANALYSE делается?
покажите пжл результаты запроса
Код: plaintext
1.
2.
 
SELECT getting_seo_status_id , COUNT(*) FROM platforms GROUP BY  1

Нормально выполнилось:
Код: plaintext
1.
2.
3.
4.
5.
 getting_seo_status_id | count 
-----------------------+-------
                    123  |  23050 
                      0  |    391 
                     12  |     24
...
Рейтинг: 0 / 0
14.12.2008, 23:57
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713586
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Gold_VACUUM FULL ANALYSE делается?Включен авто-VACUUM. Этого достаточно?
...
Рейтинг: 0 / 0
14.12.2008, 23:57
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713590
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Gold_VACUUM FULL ANALYSE делается?Включен авто-VACUUM. Этого достаточно?
...
Рейтинг: 0 / 0
15.12.2008, 00:00
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713591
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Попробовал разбить свой запрос на 3 разных:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
create temporary table pids (platform_id INTEGER);
insert into pids 
	select id as platform_id from platforms where getting_seo_status_id <>  123 ;
	
create temporary table grouped (platform_id INTEGER, no_pr INTEGER, no_yap INTEGER, no_gop INTEGER, pages_count INTEGER);
INSERT INTO grouped 
	SELECT pg.platform_id, SUM(CASE WHEN pg.pr IS NULL THEN  1  END) AS no_pr,
	SUM(CASE WHEN pg.yap IS NULL THEN  1  END) AS no_yap,
	SUM(CASE WHEN pg.gop IS NULL THEN  1  END) AS no_gop,
	count(pg.id) AS pages_count
	from pages pg
	where pg.platform_id in (select platform_id from pids)
	group by pg.platform_id;

select pl.domain, pl.status_id,
pl.id, pl.status_set_date AS moderation_duration, grouped.*        
from platforms pl join grouped on grouped.platform_id = pl.id;

drop table grouped;
drop table pids;

Теперь ситуация очень странная: запрос почти всегда выполняется за 3 секунды, но иногда - опять по 2 минуты. С чем может быть связно?
...
Рейтинг: 0 / 0
15.12.2008, 00:02
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713633
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
Alexey Nayden,

всё-таки стоит начать с vacuum full analyze.

также интересно посмотреть
Код: plaintext
1.
2.
SELECT getting_seo_status_id , COUNT(*)
    FROM platforms AS pl JOIN pages AS pg ON (pl.id = pg.platform_id)
    GROUP BY  1
что-то у вас не сходится - вы говорите о 9 миллионах записей в pages, postgres сканирует 13 миллионов.

также интересно посмотреть остальные настройки планировщика
Код: plaintext
1.
2.
3.
4.
5.
6.
select n, current_setting(n) from (values
      ('seq_page_cost'),
      ('random_page_cost'),
      ('cpu_tuple_cost'),
      ('cpu_index_tuple_cost'),
      ('cpu_operator_cost')
) as s(n);
...
Рейтинг: 0 / 0
15.12.2008, 01:46
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713637
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Код: plaintext
1.
2.
3.
4.
5.
 getting_seo_status_id |  count   
-----------------------+----------
                    123  |  11588802 
                     12  |     96040 
                      0  |       755
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
          n           | current_setting 
----------------------+-----------------
 seq_page_cost        |  0 . 001 
 random_page_cost     |  0 . 001 
 cpu_tuple_cost       |  0 . 01 
 cpu_index_tuple_cost |  0 . 005 
 cpu_operator_cost    |  0 . 0025

На счет количества записей - я смотрел по приложению, но не учел, что там не все pages отображаются. Нехилая ошибка в 1.5 раза
Код: plaintext
1.
2.
3.
4.
5.
select count(id) from pages;
  count   
----------
  12343752

P.S. Запустил full vacuum analyze. Думаю, к утру закончится - отпишу результат.
...
Рейтинг: 0 / 0
15.12.2008, 01:54
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713645
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Alexey NaydenТеперь ситуация очень странная: запрос почти всегда выполняется за 3 секунды, но иногда - опять по 2 минуты. С чем может быть связно?
Подозреваю, когда все данные находятся в shared_buffers, тогда - 3 секунды, когда приходится лазить на винт - 3 минуты.
Вряд ли вам удастся достичь стабилоного ускорения (я могу быть не прав) - всё-таки 10млн строк - это около 500Mb данных - не мало :-). Если у вас много памяти - попробуйте увеличить shared_buffers, но это до того момента, когда у вас станет не 10, а 100 миллионов.
Если у вас этот запрос не частый - может стоить забить на него.
Если его жизненно необходимо его быстрое выполнение, может быть стоит подумать о хранении "срезов" данных по дате (бьюсь об заклад - у pages есть какое-либо поле, отвечающее за дату), и потом делать просто выборку с union?
...
Рейтинг: 0 / 0
15.12.2008, 02:16
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713646
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
Alexey Nayden,

а если сказать
Код: plaintext
 \set enable_mergejoin to off
что покажет explain analyze на первоначальный запрос?
...
Рейтинг: 0 / 0
15.12.2008, 02:17
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713647
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
вернее
Код: plaintext
SET enable_mergejoin TO off;
...
Рейтинг: 0 / 0
15.12.2008, 02:20
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713649
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Также, В СЛУЧАЕ, ЕСЛИ getting_seo_status_id В platforms НЕ ИЗМЕННЫ есть ОЧЕНЬ ГРЯЗНЫЙ ХАК:
Код: 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.
create function platform_id_to_getting_seo_status_id( int4 ) returns int4
language sql as $$ select getting_seo_status_id from platforms where id = $ 1  $$
IMMUTABLE; 
-- объявление IMMUTABLE - ОЧЕНЬ ГРЯЗНЫЙ ХАК в данном случае,
-- его можно применить, если вы уверены, что getting_seo_status_id менятся не будет

create index pages_ix_seo_status_id_not_123 on pages
( platform_id, ( platform_id_to_getting_seo_status_id( platform_id ) ) )
where platform_id_to_getting_seo_status_id( platform_id )<>  123 ;
-- Создание этого индекса возможно только, 
-- если platform_id_to_getting_seo_status_id объявленна как immutable

 SELECT pl.domain, pl.status_id,
 pl.id, pl.status_set_date AS moderation_duration,
 count(pg.id) AS pages_count,
 SUM(CASE WHEN pg.pr IS NULL THEN  1  END) AS no_pr,
 SUM(CASE WHEN pg.yap IS NULL THEN  1  END) AS no_yap,
 SUM(CASE WHEN pg.gop IS NULL THEN  1  END) AS no_gop
 FROM platforms AS pl JOIN pages AS pg ON (pl.id = pg.platform_id)
 WHERE pl.getting_seo_status_id <>  123 
-- ВНИМАНИЕ СЕЙЧАС ПРИМЕНИМ ХАК!!!!
     AND platform_id_to_getting_seo_status_id( pl.platform_id ) <>  123 
 GROUP BY pl.domain, pl.status_id, pl.id, pl.status_set_date;
В принципе, если вам придётся делать UPDATE platforms SET getting_seo_status_id = ... , то просто надо не забыть REINDEX pages_ix_seo_status_id_not_123 . Если же таких UPDATE у вас быть не может, то всё вообще шеколадно.
...
Рейтинг: 0 / 0
15.12.2008, 02:28
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713650
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
eddieAlexey Nayden,

а если сказать
Код: plaintext
 \set enable_mergejoin to off
что покажет explain analyze на первоначальный запрос?
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
 HashAggregate  (cost= 392347 . 44 .. 392722 . 88  rows= 18772  width= 41 ) (actual time= 60232 . 410 .. 60232 . 541  rows= 26  loops= 1 )
   ->  Hash Join  (cost= 8471 . 45 .. 383439 . 32  rows= 445406  width= 41 ) (actual time= 282 . 500 .. 59927 . 138  rows= 98652  loops= 1 )
         Hash Cond: (pg.platform_id = pl.id)
         ->  Seq Scan on pages pg  (cost= 0 . 00 .. 156350 . 98  rows= 15575479  width= 14 ) (actual time= 0 . 007 .. 34656 . 168  rows= 12344065  loops= 1 )
         ->  Hash  (cost= 8236 . 80 .. 8236 . 80  rows= 18772  width= 31 ) (actual time= 280 . 313 .. 280 . 313  rows= 417  loops= 1 )
               ->  Seq Scan on platforms pl  (cost= 0 . 00 .. 8236 . 80  rows= 18772  width= 31 ) (actual time= 224 . 553 .. 279 . 144  rows= 417  loops= 1 )
                     Filter: (getting_seo_status_id <>  123 )
 Total runtime:  60232 . 971  ms
...
Рейтинг: 0 / 0
15.12.2008, 02:33
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713652
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
Alexey Nayden,

мда... а если и hash join запретить? чтобы он nested loop сделал (seq scan по platforms, index scan на соответствующие значения в pages)
...
Рейтинг: 0 / 0
15.12.2008, 02:49
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713653
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Funny_FalconТакже, В СЛУЧАЕ, ЕСЛИ getting_seo_status_id В platforms НЕ ИЗМЕННЫ есть ОЧЕНЬ ГРЯЗНЫЙ ХАК:
Код: plaintext
Поскипано...
В принципе, если вам придётся делать UPDATE platforms SET getting_seo_status_id = ... , то просто надо не забыть REINDEX pages_ix_seo_status_id_not_123 . Если же таких UPDATE у вас быть не может, то всё вообще шеколадно.Не помогает даже грязный хак:
При включенном merge_join 115 секунд, при выключенном - 52.
...
Рейтинг: 0 / 0
15.12.2008, 02:52
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713656
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
eddieAlexey Nayden,

мда... а если и hash join запретить? чтобы он nested loop сделал (seq scan по platforms, index scan на соответствующие значения в pages)
А вот это уже серьезная заявка на победу :)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SET enable_mergejoin TO off;
SET enable_hashjoin TO off;
explain analyze....

 HashAggregate  (cost= 2597790 . 35 .. 2598166 . 29  rows= 18797  width= 41 ) (actual time= 3550 . 268 .. 3550 . 427  rows= 29  loops= 1 )
   ->  Nested Loop  (cost= 0 . 00 .. 2590686 . 93  rows= 355171  width= 41 ) (actual time= 599 . 364 .. 3278 . 830  rows= 93885  loops= 1 )
         ->  Seq Scan on platforms pl  (cost= 0 . 00 .. 8248 . 12  rows= 18797  width= 31 ) (actual time= 585 . 106 .. 673 . 623  rows= 416  loops= 1 )
               Filter: (getting_seo_status_id <>  123 )
         ->  Index Scan using index_pages_on_platform_id on pages pg  (cost= 0 . 00 .. 80 . 26  rows= 4570  width= 14 ) (actual time= 0 . 063 .. 5 . 350  rows= 226  loops= 416 )
               Index Cond: (pg.platform_id = pl.id)
 Total runtime:  3550 . 805  ms
Если это не опять случайное ускорение, то так и буду делать.
...
Рейтинг: 0 / 0
15.12.2008, 02:56
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713658
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Alexey Nayden Не помогает даже грязный хак:
При включенном merge_join 115 секунд, при выключенном - 52.Можно посмотреть на Explain Analyze ?
Alexey Nayden
Код: plaintext
1.
2.
SET enable_mergejoin TO off;
SET enable_hashjoin TO off;
Если это не опять случайное ускорение, то так и буду делать.Хотя, возможно действительно так лучше.
...
Рейтинг: 0 / 0
15.12.2008, 03:01
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713659
Alexey Nayden
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Nayden
Гость
Funny_FalconAlexey Nayden Не помогает даже грязный хак:
При включенном merge_join 115 секунд, при выключенном - 52.Можно посмотреть на Explain Analyze ?
Я уже дропнул индекс и закрыл консоль до завтра. Завтра днем сделаю снова и кину сюда.
...
Рейтинг: 0 / 0
15.12.2008, 03:04
| Ответить | Цитировать | Написать  
Нужна помощь с оптимизацией запроса
    #35713661
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
автор
Код: plaintext
1.
         ->  Seq Scan on platforms pl  (cost= 0 . 00 .. 8248 . 12  rows= 18797  width= 31 ) (actual time= 585 . 106 .. 673 . 623  rows= 416  loops= 1 )
Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT getting_seo_status_id , COUNT(*) FROM platforms GROUP BY  1 
 getting_seo_status_id | count 
-----------------------+-------
                    123  |  23050 
                      0  |    391 
                     12  |     24

Как-то резко он в количестве строк ошибается. VACUUM ANALYZE platforms делали? можно попробовать просто ANALYZE platforms, чтобы не ждать VACUUM .
...
Рейтинг: 0 / 0
15.12.2008, 03:07
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Нужна помощь с оптимизацией запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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