ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Пессимистично врущий EXPLAIN
11 сообщений из 36, страница 2 из 2
  2  все
Пессимистично врущий 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
| Ответить | Цитировать | Написать  
11 сообщений из 36, страница 2 из 2
  2  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Пессимистично врущий EXPLAIN
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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