ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Пессимистично врущий EXPLAIN
36 сообщений из 36, показаны все 2 страниц
  все  
Пессимистично врущий EXPLAIN
    #35679105
Cane Cat Fisher
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cane Cat Fisher
Участник
Удивительная вещь. Есть запрос, который сам по себе выполняется меньше секунды.

Если же попросить EXPLAIN ANALYSE, он уверяет, что этот запрос выполняется за 25 секунд (столько фактически у него и выходит). План показывает нехороший.

За день до того все работало как надо, и EXPLAIN ANALYSE показывал замечательный план на 44 мс.

Признаюсь честно, что делал в промежутке: создал один индекс по соверешнно другой таблице (не участвующей в запросе, и один удалил). Создал по полям a,b,c, удалил прежний по a,b. После этого сделал VACUUM FULL ANALYSE.

После того, как обнаружил бяку, предположил, что испортилась статистика. Попытался обновить статистику - увеличил default_statistics_target с 10 до 100, сделал postgresql reload, сделал ANALYSE. Ничего не изменилось.

Еще сделал REINDEX всех таблиц, участвующих в запросе. Ничего не изменилось.

Что можно сделать?
...
Рейтинг: 0 / 0
27.11.2008, 13:48
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35679124
KRED
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KRED
Участник
Cane Cat Fisher,

Может всётаки покажете нам свой секретный EXPLAIN ?
...
Рейтинг: 0 / 0
27.11.2008, 13:51
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35679222
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
нет нет ни в коем случае ! и версию сервера не говорите ! а уж версию операционной системы и параметры оборудования даже вскользь не упоминайте ! :)


--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
27.11.2008, 14:11
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35679395
Cane Cat Fisher
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cane Cat Fisher
Участник
Прошу прощения, что заинтриговал - решил еще раз все перепроверить перед тем как писать продолжение. Все подтверждается.

Есть три таблицы - относительно большой журнал документов jpm3 (1,5 млн записей), небольшой реестр пачек этих документов rpm3pack (1700 записей), и крохотный справочник юзеров ruser (70 записей). Документ ссылается на пачку, пачка - на юзера. Запрос показывает итоги по журналу в разрезе юзеров - кол-во документов, пачек, и некую сумму.

Запрос:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
    SELECT u.username, COUNT(DISTINCT j.pm3packid) AS cnt_pack, COUNT(*) AS cnt_doc, SUM(j.pm3currm3 - j.pm3prevm3) AS m3
      FROM jpm3 j, rpm3pack p, ruser u
     WHERE j.pointid   =  1 
       AND j.pointid   = p.pointid
       AND j.pm3packid = p.pm3packid
       AND p.userid    = u.userid
       AND p.pointid   = u.pointid
       AND p.pcurrid   =  155 
    GROUP BY  1 
    ORDER BY  1 ;

Он возвращает 8 строк.

План, который был хороший:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
 GroupAggregate  (cost= 3998 . 15 .. 4000 . 25  rows= 68  width= 33 ) (actual time= 34 . 619 .. 42 . 588  rows= 8  loops= 1 )
   ->  Sort  (cost= 3998 . 15 .. 3998 . 44  rows= 113  width= 33 ) (actual time= 34 . 026 .. 36 . 270  rows= 141  loops= 1 )
         Sort Key: u.username
         ->  Hash Join  (cost= 3 . 05 .. 3994 . 30  rows= 113  width= 33 ) (actual time= 6 . 792 .. 28 . 585  rows= 141  loops= 1 )
               Hash Cond: ("outer".userid = "inner".userid)
               ->  Nested Loop  (cost= 0 . 00 .. 3988 . 71  rows= 113  width= 20 ) (actual time= 1 . 298 .. 14 . 693  rows= 141  loops= 1 )
                     ->  Seq Scan on rpm3pack p  (cost= 0 . 00 .. 41 . 84  rows= 25  width= 12 ) (actual time= 1 . 156 .. 3 . 456  rows= 17  loops= 1 )
                           Filter: ((pcurrid =  155 ) AND (pointid =  1 ))
                     ->  Index Scan using idx_jmp3_pointid_pm3packid on jpm3 j  (cost= 0 . 00 .. 157 . 39  rows= 39  width= 16 ) (actual time= 0 . 027 .. 0 . 226  rows= 8  loops= 17 )
                           Index Cond: ((j.pointid =  1 ) AND (j.pm3packid = "outer".pm3packid))
               ->  Hash  (cost= 2 . 88 .. 2 . 88  rows= 69  width= 29 ) (actual time= 4 . 987 .. 4 . 987  rows= 0  loops= 1 )
                     ->  Seq Scan on ruser u  (cost= 0 . 00 .. 2 . 88  rows= 69  width= 29 ) (actual time= 0 . 108 .. 3 . 411  rows= 68  loops= 1 )
                           Filter: (pointid =  1 )
 Total runtime:  44 . 211  ms

План, который стал плохой:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
 GroupAggregate  (cost= 46871 . 80 .. 47153 . 26  rows= 68  width= 28 ) (actual time= 25741 . 071 .. 25743 . 695  rows= 8  loops= 1 )
   ->  Sort  (cost= 46871 . 80 .. 46927 . 96  rows= 22462  width= 28 ) (actual time= 25740 . 719 .. 25741 . 647  rows= 141  loops= 1 )
         Sort Key: u.username
         ->  Hash Join  (cost= 48 . 20 .. 44959 . 42  rows= 22462  width= 28 ) (actual time= 44 . 136 .. 25732 . 641  rows= 141  loops= 1 )
               Hash Cond: ("outer".pm3packid = "inner".pm3packid)
               ->  Seq Scan on jpm3 j  (cost= 0 . 00 .. 33069 . 62  rows= 1548930  width= 16 ) (actual time= 1 . 075 .. 14320 . 407  rows= 1548930  loops= 1 )
                     Filter: (pointid =  1 )
               ->  Hash  (cost= 48 . 13 .. 48 . 13  rows= 25  width= 24 ) (actual time= 41 . 186 .. 41 . 186  rows= 0  loops= 1 )
                     ->  Merge Join  (cost= 47 . 42 .. 48 . 13  rows= 25  width= 24 ) (actual time= 36 . 247 .. 41 . 054  rows= 17  loops= 1 )
                           Merge Cond: ("outer".userid = "inner".userid)
                           ->  Sort  (cost= 42 . 44 .. 42 . 50  rows= 25  width= 12 ) (actual time= 8 . 058 .. 9 . 553  rows= 17  loops= 1 )
                                 Sort Key: p.userid
                                 ->  Seq Scan on rpm3pack p  (cost= 0 . 00 .. 41 . 86  rows= 25  width= 12 ) (actual time= 3 . 030 .. 6 . 338  rows= 17  loops= 1 )
                                       Filter: ((pcurrid =  155 ) AND (pointid =  1 ))
                           ->  Sort  (cost= 4 . 98 .. 5 . 15  rows= 69  width= 24 ) (actual time= 28 . 109 .. 29 . 272  rows= 75  loops= 1 )
                                 Sort Key: u.userid
                                 ->  Seq Scan on ruser u  (cost= 0 . 00 .. 2 . 88  rows= 69  width= 24 ) (actual time= 24 . 022 .. 26 . 892  rows= 68  loops= 1 )
                                       Filter: (pointid =  1 )
 Total runtime:  25799 . 001  ms

Видно, что во втором случае перестал использоваться индекс idx_jmp3_pointid_pm3packid, и переиначилась структура запроса. Но индекс этот жив и здоров. Структура не менялась, данные в таблицах существенно не менялись (может, 3-4 записи поковырял - это тестовый экземпляр).

Напрашивающийся индекс на rpm3pack по pointid, pcurrid существует, но не используется. В первом случае - результаты были и так хороши. Во втором - непонятно.

Сервер старючий, 7.4.8. Пока жареный петух не клевал. Если пообещаете, что дело именно в этом - будем переходить на 7.4.23.
...
Рейтинг: 0 / 0
27.11.2008, 14:44
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35679994
KRED
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KRED
Участник
Cane Cat Fisher,

А чего не проапгрейдите ... про петуха знаю , но кусается он иногда очень больно!
...
Рейтинг: 0 / 0
27.11.2008, 17:05
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35680248
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
я не думаю, что переход на другой релиз из ветки 7.4 что-то решит. если уж переходить, то на 8.
...
Рейтинг: 0 / 0
27.11.2008, 18:24
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35681193
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
попробуйте set enable_seqscan to off. покажите получившийся explain analyze.
...
Рейтинг: 0 / 0
28.11.2008, 10:53
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35681491
assa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
assa
Участник
eddieя не думаю, что переход на другой релиз из ветки 7.4 что-то решит. если уж переходить, то на 8.не-а.
7.4. - есть величина исключительная.
Предопределенная.
искать по <че-то-там>...ВС
...
Рейтинг: 0 / 0
28.11.2008, 12:04
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35681616
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
в "хорошем" плане эта строчка вызывает подозрения:
Код: plaintext
               ->  Nested Loop  (cost= 0 . 00 .. 3988 . 71  rows= 113  width= 20 ) (actual time= 1 . 298 .. 14 . 693  rows= 141  loops= 1 )

может быть более опытные товарищи расшифруют?
...
Рейтинг: 0 / 0
28.11.2008, 12:37
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682203
Cane Cat Fisher
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cane Cat Fisher
Участник
LeXa NalBatпопробуйте set enable_seqscan to off. покажите получившийся explain analyze.

Поигрался, результат интересный.

Прежде всего скажу, что обновился до 7.4.23, и эти тесты уже на другом железе, поэтому реальное время несколько отличается от прежнего. Поднял резервную копию, с которой все начиналось. Сделал VACUUM FULL ANALYSE при default_statistics_target = 1000.

По поводу "Хорошего" плана - результат в 44 мс, конечно, фантастический, и повторить его не получилось. Я теперь не могу с уверенностью утверждать, с какими параметрами был сделан тот запрос, но подозреваю, что запрос был сделан за период, в котором было мало данных.

Теперь тестирую на нормальном периоде, но явление сохранилось, хотя и с величинами иного порядка.

Исходный запрос выполняется 1.7 сек.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
 2008 - 11 - 28   13 : 28 : 47  [ 22727 ] LOG:  statement:     SELECT u.username, COUNT(DISTINCT j.pm3packid) AS cnt_pack, COUNT(*) AS cnt_doc, SUM(j.pm3currm3 - j.pm3prevm
 3 ) AS m3
              FROM jpm3 j, rpm3pack p, ruser u
             WHERE j.pointid   =  1 
               AND j.pointid   = p.pointid
               AND j.pm3packid = p.pm3packid
               AND p.userid    = u.userid
               AND p.pointid   = u.pointid
               AND p.pcurrid   =  155 
            GROUP BY  1 
            ORDER BY  1 ;
 2008 - 11 - 28   13 : 28 : 48  [ 22727 ] LOG:  duration:  1729 . 757  ms

Выключаем seqscan, выполняется быстрее, 0.6 сек:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
 2008 - 11 - 28   13 : 30 : 29  [ 22750 ] LOG:  statement: SET enable_seqscan = false;
 2008 - 11 - 28   13 : 30 : 29  [ 22750 ] LOG:  statement:     
            SELECT u.username, COUNT(DISTINCT j.pm3packid) AS cnt_pack, COUNT(*) AS cnt_doc, 
                   SUM(j.pm3currm3 - j.pm3prevm3) AS m3
              FROM jpm3 j, rpm3pack p, ruser u
             WHERE j.pointid   =  1 
               AND j.pointid   = p.pointid
               AND j.pm3packid = p.pm3packid
               AND p.userid    = u.userid
               AND p.pointid   = u.pointid
               AND p.pcurrid   =  155 
            GROUP BY  1 
            ORDER BY  1 ;
 2008 - 11 - 28   13 : 30 : 29  [ 22750 ] LOG:  duration:  647 . 870  ms

А теперь планы:

со включенным seqscan - 13 сек. Это тот же самый "Плохой план".

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
 2008 - 11 - 28   13 : 34 : 42  [ 22754 ] LOG:  statement: SET enable_seqscan = true;
 2008 - 11 - 28   13 : 34 : 42  [ 22754 ] LOG:  statement: EXPLAIN ANALYSE
            SELECT u.username, COUNT(DISTINCT j.pm3packid) AS cnt_pack, COUNT(*) AS cnt_doc, 
                   SUM(j.pm3currm3 - j.pm3prevm3) AS m3
              FROM jpm3 j, rpm3pack p, ruser u
             WHERE j.pointid   =  1 
               AND j.pointid   = p.pointid
               AND j.pm3packid = p.pm3packid
               AND p.userid    = u.userid
               AND p.pointid   = u.pointid
               AND p.pcurrid   =  155 
            GROUP BY  1 
            ORDER BY  1 ;
 2008 - 11 - 28   13 : 34 : 55  [ 22754 ] LOG:  duration:  13022 . 703  ms

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
 GroupAggregate  (cost= 45336 . 03 .. 45855 . 92  rows= 68  width= 28 ) (actual time= 12794 . 245 .. 13012 . 124  rows= 10  loops= 1 )
   ->  Sort  (cost= 45336 . 03 .. 45439 . 87  rows= 41537  width= 28 ) (actual time= 12736 . 878 .. 12851 . 041  rows= 33036  loops= 1 )
         Sort Key: u.username
         ->  Hash Join  (cost= 49 . 67 .. 42149 . 71  rows= 41537  width= 28 ) (actual time= 3 . 726 .. 12086 . 879  rows= 33036  loops= 1 )
               Hash Cond: ("outer".pm3packid = "inner".pm3packid)
               ->  Seq Scan on jpm3 j  (cost= 0 . 00 .. 33774 . 76  rows= 1581981  width= 16 ) (actual time= 0 . 012 .. 6319 . 285  rows= 1581981  loops= 1 )
                     Filter: (pointid =  1 )
               ->  Hash  (cost= 49 . 55 .. 49 . 55  rows= 46  width= 24 ) (actual time= 3 . 464 .. 3 . 464  rows= 0  loops= 1 )
                     ->  Merge Join  (cost= 48 . 53 .. 49 . 55  rows= 46  width= 24 ) (actual time= 2 . 088 .. 3 . 278  rows= 45  loops= 1 )
                           Merge Cond: ("outer".userid = "inner".userid)
                           ->  Sort  (cost= 43 . 55 .. 43 . 67  rows= 46  width= 12 ) (actual time= 1 . 403 .. 1 . 568  rows= 45  loops= 1 )
                                 Sort Key: p.userid
                                 ->  Seq Scan on rpm3pack p  (cost= 0 . 00 .. 42 . 28  rows= 46  width= 12 ) (actual time= 0 . 392 .. 1 . 172  rows= 45  loops= 1 )
                                       Filter: ((pcurrid =  155 ) AND (pointid =  1 ))
                           ->  Sort  (cost= 4 . 98 .. 5 . 15  rows= 69  width= 24 ) (actual time= 0 . 620 .. 0 . 991  rows= 101  loops= 1 )
                                 Sort Key: u.userid
                                 ->  Seq Scan on ruser u  (cost= 0 . 00 .. 2 . 88  rows= 69  width= 24 ) (actual time= 0 . 011 .. 0 . 330  rows= 68  loops= 1 )
                                       Filter: (pointid =  1 )
 Total runtime:  13016 . 254  ms

с выключенным seqscan - 1,3 сек.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
 2008 - 11 - 28   13 : 38 : 00  [ 22758 ] LOG:  statement: SET enable_seqscan = false;
 2008 - 11 - 28   13 : 38 : 00  [ 22758 ] LOG:  statement: EXPLAIN ANALYSE
            SELECT u.username, COUNT(DISTINCT j.pm3packid) AS cnt_pack, COUNT(*) AS cnt_doc, 
                   SUM(j.pm3currm3 - j.pm3prevm3) AS m3
              FROM jpm3 j, rpm3pack p, ruser u
             WHERE j.pointid   =  1 
               AND j.pointid   = p.pointid
               AND j.pm3packid = p.pm3packid
               AND p.userid    = u.userid
               AND p.pointid   = u.pointid
               AND p.pcurrid   =  155 
            GROUP BY  1 
            ORDER BY  1 ;
 2008 - 11 - 28   13 : 38 : 01  [ 22758 ] LOG:  duration:  1336 . 414  ms

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
 GroupAggregate  (cost= 151746 . 82 .. 152266 . 72  rows= 68  width= 28 ) (actual time= 1105 . 634 .. 1323 . 768  rows= 10  loops= 1 )
   ->  Sort  (cost= 151746 . 82 .. 151850 . 67  rows= 41537  width= 28 ) (actual time= 1048 . 281 .. 1162 . 767  rows= 33036  loops= 1 )
         Sort Key: u.username
         ->  Nested Loop  (cost= 63 . 96 .. 148560 . 50  rows= 41537  width= 28 ) (actual time= 1 . 553 .. 410 . 450  rows= 33036  loops= 1 )
               ->  Merge Join  (cost= 63 . 96 .. 64 . 98  rows= 46  width= 24 ) (actual time= 1 . 406 .. 2 . 679  rows= 45  loops= 1 )
                     Merge Cond: ("outer".userid = "inner".userid)
                     ->  Sort  (cost= 52 . 24 .. 52 . 36  rows= 46  width= 12 ) (actual time= 0 . 601 .. 0 . 755  rows= 45  loops= 1 )
                           Sort Key: p.userid
                           ->  Index Scan using idx_rpm3pack_pointid_pcurrid on rpm3pack p  (cost= 0 . 00 .. 50 . 97  rows= 46  width= 12 ) (actual time= 0 . 118 .. 0 . 378  rows= 45  loops= 1 )
                                 Index Cond: ((pointid =  1 ) AND (pcurrid =  155 ))
                     ->  Sort  (cost= 11 . 72 .. 11 . 89  rows= 69  width= 24 ) (actual time= 0 . 743 .. 1 . 101  rows= 101  loops= 1 )
                           Sort Key: u.userid
                           ->  Index Scan using ruser_un_pointid_username on ruser u  (cost= 0 . 00 .. 9 . 61  rows= 69  width= 24 ) (actual time= 0 . 104 .. 0 . 449  rows= 68  loops= 1 )
                                 Index Cond: (pointid =  1 )
               ->  Index Scan using idx_jmp3_pointid_pm3packid on jpm3 j  (cost= 0 . 00 .. 3214 . 36  rows= 1104  width= 16 ) (actual time= 0 . 018 .. 3 . 458  rows= 734  loops= 45 )
                     Index Cond: ((j.pointid =  1 ) AND (j.pm3packid = "outer".pm3packid))
 Total runtime:  1328 . 017  ms

Почему такое большое различие между длительностями планов и реальными запросами, особенно в первом случае? Похоже, EXPLAIN выдает не тот план, который используется при запросах? Что это за эвристика? Я же отлаживать ничего не смогу :-(
...
Рейтинг: 0 / 0
28.11.2008, 15:12
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682317
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
есть у вас возможность перейти на более новую или даже последнюю 8.3.5 версию сервера? это может решить проблему.
...
Рейтинг: 0 / 0
28.11.2008, 15:45
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682519
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
LeXa NalBatесть у вас возможность перейти на более новую или даже последнюю 8.3.5 версию сервера? это может решить проблему.

В версии 8.1 подобные проблемы так же присутствуют, в 8.3 я свои дампы грузил, там те же самые грабли. Хотя на тестовой машине загрузить дамп в 8.1 и 8.3 имеет смысл, почему бы и не попробовать.

P.S. Для моих задач один обходной путь удалось найти - выполнять подзапросы, возвращающие десятки или сотни записей, отдельными запросами и результат подставлять в конструкции "in (...,...,...)". Временами это дает выигрыш на несколько порядков. Это усложняет клиентский код, но другого выхода я не вижу.
...
Рейтинг: 0 / 0
28.11.2008, 16:45
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682529
Cane Cat Fisher
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cane Cat Fisher
Участник
LeXa NalBat,

А вообще в восьмерках была ли описана где-то эта ситуация как исправленный баг? Или оставется надеяться, что исправление спрятано за туманным выражением в HISTORY "мы в каждой версии улучшаем оптимизатор, хотя в баглистах этого не пишем, потому что это выражается просто в том, что запросы работают быстрее"?

Мне кажется, если бы это был найденный серьезный баг, его бы пофиксили и в седьмой ветке.

Но в любом случае постараюсь попробовать после выходных восьмерку.
...
Рейтинг: 0 / 0
28.11.2008, 16:46
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682558
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
вот нашёл что-то похожее:
estimates for nested loop very wrong?

из полезного имхо только:
Tom LaneHow much RAM do you have on this machine? If the system is caching
a goodly fraction of the tables, it'd be appropriate to lower
random_page_cost (or increase effective_cache_size).
...
Рейтинг: 0 / 0
28.11.2008, 16:52
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682565
Cane Cat Fisher
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cane Cat Fisher
Участник
MBG,

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

Только результаты промежуточных запросов я сохранял не в IN(), а во временные таблицы, и по ним создавал индексы. Тоже быстродействие в сотни раз улучшалось.

Но в таком запросе, похоже, подобный метод применить не удастся. Я пробовал выкинуть ruser, группировать по rpm3pack.userid, чтобы потом результат связать с ruser. Тоже чудит с планами, хотя реально выполняет быстро.
...
Рейтинг: 0 / 0
28.11.2008, 16:53
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682597
Cane Cat Fisher
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cane Cat Fisher
Участник
eddie,

Мне кажется, здесь немного не тот случай. Там у человека просто выбирало невыгодный план, не тот, что ему хотелось. А здесь показывает один план, а использует другой.
...
Рейтинг: 0 / 0
28.11.2008, 17:00
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682705
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Cane Cat FisherА вообще в восьмерках была ли описана где-то эта ситуация как исправленный баг? Или оставется надеяться, что исправление спрятано за туманным выражением в HISTORY "мы в каждой версии улучшаем оптимизаторда, именно из-за того что постоянно улучшают оптимизатор.

Cane Cat FisherМне кажется, если бы это был найденный серьезный баг, его бы пофиксили и в седьмой ветке.к сожалению, случаи выбора постгресом неоптимального плана бывают.

Cane Cat FisherА здесь показывает один план, а использует другой.это было бы багом, но верится с трудом. может все-таки селект вы выполнили в одном окружении, а эксплейн в другом, или кэширование сыграло роль, или статистика изменилась.

вы привели два плана выполнения. для каждой строки rows должно быть примерно равно actual rows - это выполняется, то есть постгрес не ошибается в оценке кол-ва строк. также должно быть для всех строк примерно одинаковым отношение cost к actual time - это не выполняется, очень медленно по сравнении с другими этапами постгрес оценивает сканирование индекса idx_jmp3_pointid_pm3packid: 3214.36/3.458=929. думаю, что в этом корень проблемы. попробуйте пересоздать этот индекс, а потом пересобрать статистику по таблице? попробуйте вместо индекса по (pointid,pm3packid) индекс по (pm3packid,pointid) - все таки селективность по pm3packid гораздо лучше.
...
Рейтинг: 0 / 0
28.11.2008, 17:30
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682785
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
Cane Cat FisherMBG,

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

Только результаты промежуточных запросов я сохранял не в IN(), а во временные таблицы, и по ним создавал индексы. Тоже быстродействие в сотни раз улучшалось.

Но в таком запросе, похоже, подобный метод применить не удастся. Я пробовал выкинуть ruser, группировать по rpm3pack.userid, чтобы потом результат связать с ruser. Тоже чудит с планами, хотя реально выполняет быстро.

В определенных случаях оказывается выгодно создавать отдельную схему в базе для каждого пользователя и в ней динамически создавать виды и таблицы с параметрами, хранимыми в сессии пользователя (конечно, сессии лучше хранить в этой же базе). Тогда и планировщику хорошо - мы можем разбить сложные запросы на цепочку простых и в итоге получить вид или таблицу (или их набор). И приложение работает просто - вызывает функцию на встроенном в базу языке, которая генерит соответствующие таблицы/виды и после этого приложение отрисовывает содержимое созданных таблиц/видов. Но плохо то, что получается, СУБД становится вторым сервером приложений, это усложняет поддержку.
Это я вот к чему - попробуйте сделать вот так:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
create view user_155.jpm3 as select * from jpm3 where pointid   =  1 ;
create view user_155.rpm3pack as select * from rpm3pack where pcurrid   =  155 ;
create view user_155.ruser as select * from ruser where userid   =  155 ;

SELECT u.username, COUNT(DISTINCT j.pm3packid) AS cnt_pack, COUNT(*) AS cnt_doc, SUM(j.pm3currm3 - j.pm3prevm3) AS m3
      FROM user_155.jpm3 j, user_155.rpm3pack p, user_155.ruser u
       AND j.pointid   = p.pointid
       AND j.pm3packid = p.pm3packid
       AND p.userid    = u.userid
       AND p.pointid   = u.pointid
    GROUP BY  1 
    ORDER BY  1 ;
Здесь мы вручную указываем все необходимые параметры для каждой таблицы, раз уж планировщик сам сообразить не может, что мы от него хотим. Понятно, что вы можете использовать и временные таблицы, хотя приведенный выше способ удобнее в отладке и сохраняет план запроса. Если какая-то из промежуточных операций требует много ресурсов и притом результат содержит немного записей, лучше их в таблицу сохранить.
...
Рейтинг: 0 / 0
28.11.2008, 17:56
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682839
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
нижеуказанные предложения MBG считаю бесполезными или ошибочными

авторвыполнять подзапросы, возвращающие десятки или сотни записей, отдельными запросами и результат подставлять в конструкции "in (...,...,...)"авторсоздавать отдельную схему в базе для каждого пользователя и в ней динамически создавать виды и таблицы с параметрами, хранимыми в сессии пользователяавтормы можем разбить сложные запросы на цепочку простыхавторвызывает функцию на встроенном в базу языке, которая генерит соответствующие таблицы/виды и после этого приложение отрисовывает содержимое созданных таблиц/видовавторcreate view user_155.jpm3 as select * from jpm3 where pointid = 1;
create view user_155.rpm3pack as select * from rpm3pack where pcurrid = 155;
create view user_155.ruser as select * from ruser where userid = 155;авторвы можете использовать и временные таблицыавторЕсли какая-то из промежуточных операций требует много ресурсов и притом результат содержит немного записей, лучше их в таблицу сохранить.
...
Рейтинг: 0 / 0
28.11.2008, 18:11
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682870
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
LeXa NalBatнижеуказанные предложения MBG считаю бесполезными или ошибочными

Ждем от вас патчи, исправляющие работу планировщика. А пока без костылей он сам не ходит. Считать бесполезными техники, ускоряющие запрос на несколько порядков это ваше дело, видимо, "железо" у вас позволяет об этом не беспокоиться.
...
Рейтинг: 0 / 0
28.11.2008, 18:24
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682903
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
MBGЖдем от вас патчи, исправляющие работу планировщика.только после того как Вы напишите отчёт об ошибке в официальный список рассылки, со скриптом или подробным описанием позволяющим воспроизвести ошибку на серверах разработчиков :)

те ошибки про которые я туда писал исправлялись в течении нескольких _дней_ что лично меня поразило наповал :) хотя я конечно допускаю что они были просты для локализации и исправления...
...
Рейтинг: 0 / 0
28.11.2008, 18:49
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682938
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
Cane Cat Fishereddie,

Мне кажется, здесь немного не тот случай. Там у человека просто выбирало невыгодный план, не тот, что ему хотелось. А здесь показывает один план, а использует другой. ???

с чего вы сделали такой вывод? у вас тот план, где seqscan выполняется медленно. тот план, где nested loop - быстро.

проблема в том, что планировщик считает наоборот, соответственно выбирает seqscan.
...
Рейтинг: 0 / 0
28.11.2008, 19:17
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682948
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
ЁшMBGЖдем от вас патчи, исправляющие работу планировщика.только после того как Вы напишите отчёт об ошибке в официальный список рассылки, со скриптом или подробным описанием позволяющим воспроизвести ошибку на серверах разработчиков :)

те ошибки про которые я туда писал исправлялись в течении нескольких _дней_ что лично меня поразило наповал :) хотя я конечно допускаю что они были просты для локализации и исправления...

Я уже публиковал примеры простых запросов, когда планировщик ведет себя непредсказуемо. Могу пополнить эту коллекцию.

Например, такой запрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT d.id, d.phone_number, r.name AS region, p.code AS point_code, p.is_priority AS point_is_priority, 
c.code AS center_code, a.value AS status, u2.name as worker,tp.name as template_name, c.name as center_name,
(a.save_date at time zone interval '03:00')::date as date_work,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) as time_work
   FROM offline.documents d
   JOIN auth.users u ON d.user_id = u.id
   JOIN auth.regions r ON u.region_id = r.id
   JOIN auth.points p ON u.point_id = p.id
   JOIN auth.centers c ON u.center_id = c.id
   JOIN offline.attributes a ON d.id = a.document_id AND a.is_last 
      and a.document_id in (select id from offline.documents where phone_number='1234567890') and a.value!='Удален'
   JOIN auth.users u2 on a.user_id=u2.id
   JOIN offline.document_templates tp ON d.document_template_id = tp.id
   where d.phone_number='1234567890'
                and true
                and true
                and true
   order by point_is_priority desc, status asc, date_work asc, time_work asc;

на "холодной" базе работает так
Код: plaintext
1.
Суммарное время выполнения запроса: 11803  ms.
строк извлечено:  9

на "горячей" базе (после нескольких запусков)
Код: plaintext
1.
Суммарное время выполнения запроса: 820  ms.
строк извлечено:  9

план в обоих случаях такой
Код: 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.
37.
38.
39.
40.
41.
42.
43.
44.
"Sort  (cost=30870.72..30870.76 rows=18 width=233)"
"  Sort Key: p.is_priority, a.value, (timezone('03:00:00'::interval, a.save_date))::date, date_trunc('second'::text, ((timezone('03:00:00'::interval, a.save_date))::time without time zone)::interval)"
"  ->  Hash Join  (cost=30786.62..30870.34 rows=18 width=233)"
"        Hash Cond: ("outer".id = "inner".user_id)"
"        ->  Seq Scan on users u2  (cost=0.00..49.11 rows=911 width=31)"
"        ->  Hash  (cost=30786.58..30786.58 rows=18 width=210)"
"              ->  Hash Join  (cost=882.69..30786.58 rows=18 width=210)"
"                    Hash Cond: ("outer".document_id = "inner".id)"
"                    ->  Seq Scan on attributes a  (cost=364.86..29776.39 rows=98434 width=35)"
"                          Filter: (is_last AND (value <> 'Удален'::text) AND (hashed subplan))"
"                          SubPlan"
"                            ->  Bitmap Heap Scan on documents  (cost=2.33..364.62 rows=95 width=4)"
"                                  Recheck Cond: (phone_number = '1234567890'::text)"
"                                  ->  Bitmap Index Scan on documents_phone_number_idx  (cost=0.00..2.33 rows=95 width=0)"
"                                        Index Cond: (phone_number = '1234567890'::text)"
"                    ->  Hash  (cost=517.74..517.74 rows=38 width=179)"
"                          ->  Hash Join  (cost=494.61..517.74 rows=38 width=179)"
"                                Hash Cond: ("outer".id = "inner".center_id)"
"                                ->  Seq Scan on centers c  (cost=0.00..15.10 rows=510 width=68)"
"                                ->  Hash  (cost=494.50..494.50 rows=46 width=119)"
"                                      ->  Hash Join  (cost=477.90..494.50 rows=46 width=119)"
"                                            Hash Cond: ("outer".id = "inner".point_id)"
"                                            ->  Seq Scan on points p  (cost=0.00..14.76 rows=276 width=37)"
"                                            ->  Hash  (cost=477.69..477.69 rows=83 width=90)"
"                                                  ->  Hash Join  (cost=459.89..477.69 rows=83 width=90)"
"                                                        Hash Cond: ("outer".region_id = "inner".id)"
"                                                        ->  Hash Join  (cost=445.64..455.62 rows=95 width=62)"
"                                                              Hash Cond: ("outer".user_id = "inner".id)"
"                                                              ->  Merge Join  (cost=394.25..397.28 rows=95 width=54)"
"                                                                    Merge Cond: ("outer".document_template_id = "inner".id)"
"                                                                    ->  Sort  (cost=367.74..367.98 rows=95 width=26)"
"                                                                          Sort Key: d.document_template_id"
"                                                                          ->  Bitmap Heap Scan on documents d  (cost=2.33..364.62 rows=95 width=26)"
"                                                                                Recheck Cond: (phone_number = '1234567890'::text)"
"                                                                                ->  Bitmap Index Scan on documents_phone_number_idx  (cost=0.00..2.33 rows=95 width=0)"
"                                                                                      Index Cond: (phone_number = '1234567890'::text)"
"                                                                    ->  Sort  (cost=26.52..27.32 rows=320 width=36)"
"                                                                          Sort Key: tp.id"
"                                                                          ->  Seq Scan on document_templates tp  (cost=0.00..13.20 rows=320 width=36)"
"                                                              ->  Hash  (cost=49.11..49.11 rows=911 width=16)"
"                                                                    ->  Seq Scan on users u  (cost=0.00..49.11 rows=911 width=16)"
"                                                        ->  Hash  (cost=13.40..13.40 rows=340 width=36)"
"                                                              ->  Seq Scan on regions r  (cost=0.00..13.40 rows=340 width=36)"

Переписываем запрос согласно приведенным мною выше рекомендациям.
Подзапрос:
Код: plaintext
1.
select id from offline.documents where phone_number='1234567890';

Время выполнения мало даже при первом запуске:

Код: plaintext
1.
2.
Суммарное время выполнения запроса: 38  ms.
строк извлечено:  69

План:
Код: plaintext
1.
2.
3.
4.
"Bitmap Heap Scan on documents  (cost=2.33..364.62 rows=95 width=4)"
"  Recheck Cond: (phone_number = '1234567890'::text)"
"  ->  Bitmap Index Scan on documents_phone_number_idx  (cost=0.00..2.33 rows=95 width=0)"
"        Index Cond: (phone_number = '1234567890'::text)"

Основной запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT d.id, d.phone_number, r.name AS region, p.code AS point_code, p.is_priority AS point_is_priority, c.code AS center_code, a.value AS status, u2.name as worker,tp.name as template_name, c.name as center_name,
(a.save_date at time zone interval '03:00')::date as date_work,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) as time_work
   FROM offline.documents d
   JOIN auth.users u ON d.user_id = u.id
   JOIN auth.regions r ON u.region_id = r.id
   JOIN auth.points p ON u.point_id = p.id
   JOIN auth.centers c ON u.center_id = c.id
   JOIN offline.attributes a ON d.id = a.document_id AND a.is_last and a.document_id in ( 90928 , 40240 , 229782 , 168238 , 366125 , 49081 , 49082 , 49477 , 49479 , 49480 , 49350 , 90587 , 46959 , 47195 , 47124 , 378825 , 36293 , 38746 , 149909 , 37329 , 37232 , 102816 , 38129 , 17578 , 17581 , 17582 , 228268 , 287310 , 216622 , 289 , 1244 , 287180 , 97351 , 48153 , 94466 , 93978 , 93952 , 285857 , 91070 , 89428 , 155518 , 285163 , 83550 , 285817 , 287333 , 79681 , 155309 , 155111 , 41630 , 157051 , 105024 , 90644 , 214942 , 178669 , 95985 , 52361 , 53484 , 130032 , 168248 , 131343 , 143064 , 277625 , 349635 , 149913 , 168210 , 115669 , 106603 , 105026 , 168081 ) and a.value!='Удален'
   JOIN auth.users u2 on a.user_id=u2.id
   JOIN offline.document_templates tp ON d.document_template_id = tp.id
   where d.phone_number='1234567890'
                and true
                and true
                and true
   order by point_is_priority desc, status asc, date_work asc, time_work asc;

Время выполнения на холодной базе:
Код: plaintext
1.
2.
Суммарное время выполнения запроса: 116  ms.
строк извлечено:  9

На горячей:
Код: plaintext
1.
2.
Суммарное время выполнения запроса: 48  ms.
строк извлечено:  9

План:
Код: 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.
37.
38.
39.
40.
41.
42.
"Sort  (cost=2301.11..2301.11 rows=1 width=233)"
"  Sort Key: p.is_priority, a.value, (timezone('03:00:00'::interval, a.save_date))::date, date_trunc('second'::text, ((timezone('03:00:00'::interval, a.save_date))::time without time zone)::interval)"
"  ->  Hash Join  (cost=2225.44..2301.10 rows=1 width=233)"
"        Hash Cond: ("outer".id = "inner".user_id)"
"        ->  Hash Join  (cost=46.08..119.85 rows=371 width=133)"
"              Hash Cond: ("outer".center_id = "inner".id)"
"              ->  Hash Join  (cost=29.70..95.43 rows=444 width=73)"
"                    Hash Cond: ("outer".region_id = "inner".id)"
"                    ->  Hash Join  (cost=15.45..74.20 rows=508 width=45)"
"                          Hash Cond: ("outer".point_id = "inner".id)"
"                          ->  Seq Scan on users u  (cost=0.00..49.11 rows=911 width=16)"
"                          ->  Hash  (cost=14.76..14.76 rows=276 width=37)"
"                                ->  Seq Scan on points p  (cost=0.00..14.76 rows=276 width=37)"
"                    ->  Hash  (cost=13.40..13.40 rows=340 width=36)"
"                          ->  Seq Scan on regions r  (cost=0.00..13.40 rows=340 width=36)"
"              ->  Hash  (cost=15.10..15.10 rows=510 width=68)"
"                    ->  Seq Scan on centers c  (cost=0.00..15.10 rows=510 width=68)"
"        ->  Hash  (cost=2179.36..2179.36 rows=1 width=108)"
"              ->  Hash Join  (cost=2125.69..2179.36 rows=1 width=108)"
"                    Hash Cond: ("outer".id = "inner".user_id)"
"                    ->  Seq Scan on users u2  (cost=0.00..49.11 rows=911 width=31)"
"                    ->  Hash  (cost=2125.69..2125.69 rows=1 width=85)"
"                          ->  Hash Join  (cost=2110.88..2125.69 rows=1 width=85)"
"                                Hash Cond: ("outer".id = "inner".document_template_id)"
"                                ->  Seq Scan on document_templates tp  (cost=0.00..13.20 rows=320 width=36)"
"                                ->  Hash  (cost=2110.87..2110.87 rows=1 width=57)"
"                                      ->  Hash Join  (cost=1730.53..2110.87 rows=1 width=57)"
"                                            Hash Cond: ("outer".id = "inner".document_id)"
"                                            ->  Bitmap Heap Scan on documents d  (cost=2.33..364.62 rows=95 width=26)"
"                                                  Recheck Cond: (phone_number = '1234567890'::text)"
"                                                  ->  Bitmap Index Scan on documents_phone_number_idx  (cost=0.00..2.33 rows=95 width=0)"
"                                                        Index Cond: (phone_number = '1234567890'::text)"
"                                            ->  Hash  (cost=1728.01..1728.01 rows=75 width=35)"
"                                                  ->  Bitmap Heap Scan on attributes a  (cost=139.57..1728.01 rows=75 width=35)"
"                                                        Recheck Cond: ((document_id = 90928) OR ... OR (document_id = 168081))"
"                                                        Filter: (is_last AND (value <> 'Удален'::text))"
"                                                        ->  BitmapOr  (cost=139.57..139.57 rows=449 width=0)"
"                                                              ->  Bitmap Index Scan on attributes_document_id_idx  (cost=0.00..2.02 rows=7 width=0)"
"                                                                    Index Cond: (document_id = 90928)"
...
"                                                              ->  Bitmap Index Scan on attributes_document_id_idx  (cost=0.00..2.02 rows=7 width=0)"
"                                                                    Index Cond: (document_id = 168081)"

Ясно видно, что планировщик не отрабатывает корректно план выполнения запроса с подзапросами, сваливаясь на Seq Scan по таблице attributes.
...
Рейтинг: 0 / 0
28.11.2008, 19:27
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35682957
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
MBG
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
create view user_155.jpm3 as select * from jpm3 where pointid   =  1 ;
create view user_155.rpm3pack as select * from rpm3pack where pcurrid   =  155 ;
create view user_155.ruser as select * from ruser where userid   =  155 ;

SELECT u.username, COUNT(DISTINCT j.pm3packid) AS cnt_pack, COUNT(*) AS cnt_doc, SUM(j.pm3currm3 - j.pm3prevm3) AS m3
      FROM user_155.jpm3 j, user_155.rpm3pack p, user_155.ruser u
       AND j.pointid   = p.pointid
       AND j.pm3packid = p.pm3packid
       AND p.userid    = u.userid
       AND p.pointid   = u.pointid
    GROUP BY  1 
    ORDER BY  1 ;
Здесь мы вручную указываем все необходимые параметры для каждой таблицы, раз уж планировщик сам сообразить не может, что мы от него хотим.
хотя и так неоднократно сталкивался с тем, как оптимизатор разворачивает view, но всё-таки не поленился проверить:
Код: 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.
test=# create table test1(a integer,b integer);
CREATE TABLE
test=# insert into test1 (select * from generate_series( 1 , 100000 ));
INSERT  0   100000 
test=# update test1 set b= 1  where a< 1000 ;
UPDATE  999 
test=# update test1 set b= 2  where a>= 1000 ;
UPDATE  99001 
test=# create index test1_a on test1 (a);
CREATE INDEX
test=# create index test1_b on test1 (b);
CREATE INDEX
test=# analyze test1;
ANALYZE
test=# create view test1v as select * from test1 where b= 1 ; 
CREATE VIEW
test=# explain select * from test1v;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Index Scan using test1_b on test1  (cost= 0 . 00 .. 47 . 26  rows= 1200  width= 8 )
   Index Cond: (b =  1 )
( 2  rows)

test=# explain select * from test1v where a= 5 ;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using test1_a on test1  (cost= 0 . 00 .. 8 . 28  rows= 1  width= 8 )
   Index Cond: (a =  5 )
   Filter: (b =  1 )
( 3  rows)
...
Рейтинг: 0 / 0
28.11.2008, 19:34
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683044
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
Так будут предложения по решению указанной проблемы планировщика? У меня предложение только одно - необходимо выкинуть вероятностную модель и реализовать детерминированный планировщик. Но это явно не укладывается в рамки багрепорта. А поскольку подобных и намного более сложных запросов у меня сотни, то и отношение к работе планировщика очень скептическое - везде нужны костыли. Можно с различными настройками поиграться, например, seqscan отключать на какие-то таблицы, но это повлияет на сотни других запросов, часть из которых начинают работать совершенно неадекватно. Как я понимаю, те, кто не занимается системами анализа больших массивов данных, с этими проблемами могут не сталкиваться, но утверждение о том, что постгрес мощнейшая из открытых СУБД уже несколько лет не соответствует реальности и разработчиков это положение вполне устраивает. А для простых запросов постгрес просто не нужен - что толку от возможности реализовать свои функции для анализа, если запросы с их использованием чрезвычайно медленны.
...
Рейтинг: 0 / 0
28.11.2008, 21:07
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683097
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
MBGНапример, такой запрос
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT d.id, d.phone_number, r.name AS region, p.code AS point_code, p.is_priority AS point_is_priority, 
c.code AS center_code, a.value AS status, u2.name as worker,tp.name as template_name, c.name as center_name,
(a.save_date at time zone interval '03:00')::date as date_work,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) as time_work
   FROM offline.documents d
   JOIN auth.users u ON d.user_id = u.id
   JOIN auth.regions r ON u.region_id = r.id
   JOIN auth.points p ON u.point_id = p.id
   JOIN auth.centers c ON u.center_id = c.id
   JOIN offline.attributes a ON d.id = a.document_id AND a.is_last 
      and a.document_id in (select id from offline.documents where phone_number='1234567890') and a.value!='Удален'
   JOIN auth.users u2 on a.user_id=u2.id
   JOIN offline.document_templates tp ON d.document_template_id = tp.id
   where d.phone_number='1234567890'
                and true
                and true
                and true
   order by point_is_priority desc, status asc, date_work asc, time_work asc;
масло масляное.
с одной стороны у вас offline.attributes join offline.documents on document_id=id where phone_number='1234567890'
с другой стороны document_id in (select id from offline.documents where phone_number='1234567890')
...
Рейтинг: 0 / 0
28.11.2008, 22:29
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683150
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
eddieмасло масляное.
с одной стороны у вас offline.attributes join offline.documents on document_id=id where phone_number='1234567890'
с другой стороны document_id in (select id from offline.documents where phone_number='1234567890')

О том и речь, что планировщик сам не может понять, что это условие нужно применить к обеим таблицам и приходится вручную расписывать. Если не расписать, то эквивалентный sql-запрос выполняется намного хуже:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT d.id, d.phone_number, r.name AS region, p.code AS point_code, p.is_priority AS point_is_priority, 
c.code AS center_code, a.value AS status, u2.name as worker,tp.name as template_name, c.name as center_name,
(a.save_date at time zone interval '03:00')::date as date_work,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) as time_work
   FROM offline.documents d
   JOIN auth.users u ON d.user_id = u.id
   JOIN auth.regions r ON u.region_id = r.id
   JOIN auth.points p ON u.point_id = p.id
   JOIN auth.centers c ON u.center_id = c.id
   JOIN offline.attributes a ON d.id = a.document_id AND a.is_last 
       and a.value!='Удален'
   JOIN auth.users u2 on a.user_id=u2.id
   JOIN offline.document_templates tp ON d.document_template_id = tp.id
   where d.phone_number='1234567890'
                and true
                and true
                and true
   order by point_is_priority desc, status asc, date_work asc, time_work asc;

Код: plaintext
1.
2.
Суммарное время выполнения запроса: 714  ms.
строк извлечено:  9

Как видим, полученное значение примерно равно времени выполнения исходного запроса (820 ms.)

Код: 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.
37.
38.
"Sort  (cost=28045.25..28045.35 rows=38 width=233)"
"  Sort Key: p.is_priority, a.value, (timezone('03:00:00'::interval, a.save_date))::date, date_trunc('second'::text, ((timezone('03:00:00'::interval, a.save_date))::time without time zone)::interval)"
"  ->  Hash Join  (cost=27957.75..28044.25 rows=38 width=233)"
"        Hash Cond: ("outer".id = "inner".user_id)"
"        ->  Seq Scan on users u2  (cost=0.00..49.11 rows=911 width=31)"
"        ->  Hash  (cost=27957.66..27957.66 rows=38 width=210)"
"              ->  Hash Join  (cost=517.84..27957.66 rows=38 width=210)"
"                    Hash Cond: ("outer".document_id = "inner".id)"
"                    ->  Seq Scan on attributes a  (cost=0.00..26455.11 rows=196868 width=35)"
"                          Filter: (is_last AND (value <> 'Удален'::text))"
"                    ->  Hash  (cost=517.74..517.74 rows=38 width=179)"
"                          ->  Hash Join  (cost=494.61..517.74 rows=38 width=179)"
"                                Hash Cond: ("outer".id = "inner".center_id)"
"                                ->  Seq Scan on centers c  (cost=0.00..15.10 rows=510 width=68)"
"                                ->  Hash  (cost=494.50..494.50 rows=46 width=119)"
"                                      ->  Hash Join  (cost=477.90..494.50 rows=46 width=119)"
"                                            Hash Cond: ("outer".id = "inner".point_id)"
"                                            ->  Seq Scan on points p  (cost=0.00..14.76 rows=276 width=37)"
"                                            ->  Hash  (cost=477.69..477.69 rows=83 width=90)"
"                                                  ->  Hash Join  (cost=459.89..477.69 rows=83 width=90)"
"                                                        Hash Cond: ("outer".region_id = "inner".id)"
"                                                        ->  Hash Join  (cost=445.64..455.62 rows=95 width=62)"
"                                                              Hash Cond: ("outer".user_id = "inner".id)"
"                                                              ->  Merge Join  (cost=394.25..397.28 rows=95 width=54)"
"                                                                    Merge Cond: ("outer".document_template_id = "inner".id)"
"                                                                    ->  Sort  (cost=367.74..367.98 rows=95 width=26)"
"                                                                          Sort Key: d.document_template_id"
"                                                                          ->  Bitmap Heap Scan on documents d  (cost=2.33..364.62 rows=95 width=26)"
"                                                                                Recheck Cond: (phone_number = '1234567890'::text)"
"                                                                                ->  Bitmap Index Scan on documents_phone_number_idx  (cost=0.00..2.33 rows=95 width=0)"
"                                                                                      Index Cond: (phone_number = '1234567890'::text)"
"                                                                    ->  Sort  (cost=26.52..27.32 rows=320 width=36)"
"                                                                          Sort Key: tp.id"
"                                                                          ->  Seq Scan on document_templates tp  (cost=0.00..13.20 rows=320 width=36)"
"                                                              ->  Hash  (cost=49.11..49.11 rows=911 width=16)"
"                                                                    ->  Seq Scan on users u  (cost=0.00..49.11 rows=911 width=16)"
"                                                        ->  Hash  (cost=13.40..13.40 rows=340 width=36)"
"                                                              ->  Seq Scan on regions r  (cost=0.00..13.40 rows=340 width=36)"

Тот же самый запрос, здесь все совсем печально:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT d.id, d.phone_number, r.name AS region, p.code AS point_code, p.is_priority AS point_is_priority, 
c.code AS center_code, a.value AS status, u2.name as worker,tp.name as template_name, c.name as center_name,
(a.save_date at time zone interval '03:00')::date as date_work,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) as time_work
   FROM offline.documents d
   JOIN auth.users u ON d.user_id = u.id
   JOIN auth.regions r ON u.region_id = r.id
   JOIN auth.points p ON u.point_id = p.id
   JOIN auth.centers c ON u.center_id = c.id
   JOIN offline.attributes a ON d.id = a.document_id AND a.is_last 
      and a.document_id in (select id from offline.documents where phone_number='1234567890') and a.value!='Удален'
   JOIN auth.users u2 on a.user_id=u2.id
   JOIN offline.document_templates tp ON d.document_template_id = tp.id
   where 
                true
                and true
                and true
   order by point_is_priority desc, status asc, date_work asc, time_work asc;

Код: plaintext
1.
2.
Суммарное время выполнения запроса: 64992  ms.
строк извлечено:  9

Код: 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.
37.
"Sort  (cost=70073.83..70173.93 rows=40042 width=233)"
"  Sort Key: p.is_priority, a.value, (timezone('03:00:00'::interval, a.save_date))::date, date_trunc('second'::text, ((timezone('03:00:00'::interval, a.save_date))::time without time zone)::interval)"
"  ->  Hash Join  (cost=26426.33..67012.77 rows=40042 width=233)"
"        Hash Cond: ("outer".document_template_id = "inner".id)"
"        ->  Hash Join  (cost=26412.33..65797.51 rows=40042 width=205)"
"              Hash Cond: ("outer".user_id = "inner".id)"
"              ->  Hash Join  (cost=26291.56..64783.69 rows=98434 width=80)"
"                    Hash Cond: ("outer".document_id = "inner".id)"
"                    ->  Hash Join  (cost=416.24..31304.29 rows=98434 width=58)"
"                          Hash Cond: ("outer".user_id = "inner".id)"
"                          ->  Seq Scan on attributes a  (cost=364.86..29776.39 rows=98434 width=35)"
"                                Filter: (is_last AND (value <> 'Удален'::text) AND (hashed subplan))"
"                                SubPlan"
"                                  ->  Bitmap Heap Scan on documents  (cost=2.33..364.62 rows=95 width=4)"
"                                        Recheck Cond: (phone_number = '1234567890'::text)"
"                                        ->  Bitmap Index Scan on documents_phone_number_idx  (cost=0.00..2.33 rows=95 width=0)"
"                                              Index Cond: (phone_number = '1234567890'::text)"
"                          ->  Hash  (cost=49.11..49.11 rows=911 width=31)"
"                                ->  Seq Scan on users u2  (cost=0.00..49.11 rows=911 width=31)"
"                    ->  Hash  (cost=25367.65..25367.65 rows=203065 width=26)"
"                          ->  Seq Scan on documents d  (cost=0.00..25367.65 rows=203065 width=26)"
"              ->  Hash  (cost=119.85..119.85 rows=371 width=133)"
"                    ->  Hash Join  (cost=46.08..119.85 rows=371 width=133)"
"                          Hash Cond: ("outer".center_id = "inner".id)"
"                          ->  Hash Join  (cost=29.70..95.43 rows=444 width=73)"
"                                Hash Cond: ("outer".region_id = "inner".id)"
"                                ->  Hash Join  (cost=15.45..74.20 rows=508 width=45)"
"                                      Hash Cond: ("outer".point_id = "inner".id)"
"                                      ->  Seq Scan on users u  (cost=0.00..49.11 rows=911 width=16)"
"                                      ->  Hash  (cost=14.76..14.76 rows=276 width=37)"
"                                            ->  Seq Scan on points p  (cost=0.00..14.76 rows=276 width=37)"
"                                ->  Hash  (cost=13.40..13.40 rows=340 width=36)"
"                                      ->  Seq Scan on regions r  (cost=0.00..13.40 rows=340 width=36)"
"                          ->  Hash  (cost=15.10..15.10 rows=510 width=68)"
"                                ->  Seq Scan on centers c  (cost=0.00..15.10 rows=510 width=68)"
"        ->  Hash  (cost=13.20..13.20 rows=320 width=36)"
"              ->  Seq Scan on document_templates tp  (cost=0.00..13.20 rows=320 width=36)"

Во всех случаях время выполнения абсолютно неприемлемое. Вот и приходится все условия указывать для каждой из таблиц, плюс заранее вычислять значения подзапросов. Иным способом не удается добиться времени выполнения запроса менее 100мс.
...
Рейтинг: 0 / 0
28.11.2008, 23:59
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683153
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
Да, не уточнил, все рабочие таблицы небольшие:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
select count(*) from offline.attributes;
 1182569 

select count(*) from offline.documents;
 203065 

select count(*) from offline.document_templates;
 36 

select count(*) from auth.regions;
 7 

select count(*) from auth.centers;
 116 

select count(*) from auth.centers;
 405 

select count(*) from auth.users;
 911
...
Рейтинг: 0 / 0
29.11.2008, 00:05
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683220
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
MBGТак будут предложения по решению указанной проблемы планировщика? У меня предложение только одно - необходимо выкинуть вероятностную модель и реализовать детерминированный планировщик. Но это явно не укладывается в рамки багрепорта.ну и что что проблема комплексная и сложная ? :) помимо pgsql-bugs есть ещё pgsql-performance pgsql-general если там не помогли есть pgsql-hackers
А писать отчёты сюда и надеяться что те немногие из разработчиков что читают этот форум - решат с ней разобраться - ну это имхо очень маловероятно. Вы писали об этих проблемах сюда сколько месяцев назад ? год уже наверное прошёл ? я всёж таки советую Вам собраться с силами и обратиться напрямую к разработчикам в официальные списки рассылки. Если конечно Вы хотите что бы что-то изменилось.
MBGА поскольку подобных и намного более сложных запросов у меня сотни, то и отношение к работе планировщика очень скептическое - везде нужны костыли.Ваш пример опять не полон. я допустим хочу у себя воспроизвести Вашу проблему, что мне нужно делать ?
...
Рейтинг: 0 / 0
29.11.2008, 04:40
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683373
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
Ёш

Приведенные планы выполнения доказывают, что один и тот же запрос планировщик может выполнять разными способами, хотя с точки зрения стандарта sql это неправильно. Разумеется, если планов выполнения одного и того же запроса может быть много разных, то большинство из них сильно неоптимальны. Подобных вещей в форумах много опубликовано. Но никто и никогда не отдаст для проверки рабочую базу.
А в форуме обсуждаются обходные пути - раз уж баги не правят, приходится как-то с ними жить.
...
Рейтинг: 0 / 0
29.11.2008, 13:48
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683396
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
MBGПриведенные планы выполнения доказывают, что один и тот же запрос планировщик может выполнять разными способами, хотя с точки зрения стандарта sql это неправильно.позвольте с Вами не согласиться :) один и тот же запрос планировщик _должен_ выполнять разными способами, как минимум - в зависимости от количества данных в таблицах и их видимости. например если в запросе три таблицы с сотней записей - можно с закрытыми глазами выбирать seq scan, а если там сто тысяч, можно уже подумать.
или Вы имеете ввиду что план меняется хотя сами данные - не изменялись ?
MBGРазумеется, если планов выполнения одного и того же запроса может быть много разных, то большинство из них сильно неоптимальны. Подобных вещей в форумах много опубликовано. Но никто и никогда не отдаст для проверки рабочую базу.
А в форуме обсуждаются обходные пути - раз уж баги не правят, приходится как-то с ними жить.ерунда, баги - правят, нужно просто о них сообщать, а не мучится ища обходные пути. и рабочую базу - не нужно. нужно такую, на которой проявится Ваша проблема.
...
Рейтинг: 0 / 0
29.11.2008, 14:18
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683407
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
MBGПриведенные планы выполнения доказывают, что один и тот же запрос планировщик может выполнять разными способами, хотя с точки зрения стандарта sql это неправильно. ???
стандарт sql ничего не говорит об планах исполнения.

Но никто и никогда не отдаст для проверки рабочую базу. хи-хи.
какую ценность представляют ваши "секретные" данные для посторонних людей (особенно для разработчиков postgres'а)? в конце концов критичные данные можно удалить из базы (например заменить фио на белиберду).
хотя лучший путь - найти test case, на котором будет проявляться проблема.

А в форуме обсуждаются обходные пути - раз уж баги не правят, приходится как-то с ними жить. баг - это неверный результат запроса. тут же мы видим неоптимальность.

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

ps: инструмент должен нравиться. если postgres у вас вызывает столько негатива - используете что-то другое, благо альтернатив полно.

pps: а что с проблемой у топикстартера?
...
Рейтинг: 0 / 0
29.11.2008, 14:40
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683786
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
MBGздесь все совсем печально:

строк извлечено: 9

"Sort (rows=40042)"можно обсудить возможности ускорения ваших запросов в отдельной теме
...
Рейтинг: 0 / 0
30.11.2008, 01:33
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35683939
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
Я сталкивался с выбором неоптимальных планов при большом количестве join.
Не уверен, что это общий рецепт, но если задать geqo_effort = 10 и задрать кверху
geqo_threshold = 64
from_collapse_limit = 48
то, в моем случае, планы выправились, или, по меньшей мере, все стало работать приемлемо.

GEQO, по-моему, мало где оправдан.
Кстати, как вычислить накладные расходы аналитического выбора плана?
...
Рейтинг: 0 / 0
30.11.2008, 14:20
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35684586
Cane Cat Fisher
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cane Cat Fisher
Участник
eddieCane Cat Fishereddie,

здесь показывает один план, а использует другой. ???

с чего вы сделали такой вывод? у вас тот план, где seqscan выполняется медленно. тот план, где nested loop - быстро.

проблема в том, что планировщик считает наоборот, соответственно выбирает seqscan.

"С высоты птичьего полета" проблема для меня выглядит так. Я запускаю запрос - он выполняется около секунды. Я хочу его ускорить, проверяю его EXPLAIN ANALYSE - он думает 13 секунд, и еще планом подтверждает, что иначе чем за 13 секунд не получится. Чем ставит меня в тупик в плане оптимизации запроса.

Чтобы исключить влияние кеширования, запускал подряд несколько раз поперемено то сам запрос, то его EXPLAIN - результат повторяется - сам запрос быстро, EXPLAIN медленно.
...
Рейтинг: 0 / 0
01.12.2008, 10:19
| Ответить | Цитировать | Написать  
Пессимистично врущий EXPLAIN
    #35684644
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
tadminЯ сталкивался с выбором неоптимальных планов при большом количестве join.
Не уверен, что это общий рецепт, но если задать geqo_effort = 10 и задрать кверху
geqo_threshold = 64
from_collapse_limit = 48
то, в моем случае, планы выправились, или, по меньшей мере, все стало работать приемлемо.

Эти настройки пробовал менять, но существенных изменений не заметил.

Работает с расписанными, как я указывал выше, запросами быстро, но сам подход не нравится, следовало бы получать одинаковый результат как при однократном указании условия, так и при многократном, т.к. с точки зрения стандарта sql эти запросы эквивалентны. Идея простая - сначала взять подмножества нужных таблиц и пересечь их будет эффективнее, чем выполнять пересечение всех таблиц и после того ограничивать результат, но, к сожалению, я не знаю способа заставить планировщик так работать (в общем случае это не оптимально, но если в выборке участвуют малые подмножества больших таблиц, приходится вручную реализовывать указанную тактику). Запрет seqscan иногда приводит к желаемому результату, но при увеличении количества объединяемых таблиц уже не помогает. В SQLite подобной проблемы нет, там планировщик совсем иначе сделан, но цена этого - неполная реализация стандарта sql, например, нет right join, зато left join работает эффективнее.
...
Рейтинг: 0 / 0
01.12.2008, 10:43
| Ответить | Цитировать | Написать  
36 сообщений из 36, показаны все 2 страниц
  все  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Пессимистично врущий EXPLAIN
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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