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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT DISTINCT
	"op_class"."id",
	"op_class"."name"
FROM
	"turpoisk"."vw_tp_published_hotline" AS "self"
	 LEFT JOIN "dict"."vw_dict_operator" AS "op" ON op.id = self.operator
	 LEFT JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON op.id = op_class_rel.operator
	 JOIN "dict"."dict_operator_class" AS "op_class" ON op_class_rel.class = op_class.id
ORDER BY
	"op_class"."id" ASC



Группировка вместо distinct по сути ничего не дает. Анализатор выдает аналогичный план. Индексы есть.
В результате дофига записей сканируется - овер 2 млн. А возвращается - три.

План запроса :
автор"Sort (cost=115384.17..115384.19 rows=7 width=149)"
" Sort Key: op_class.id"
" -> HashAggregate (cost=115384.01..115384.08 rows=7 width=149)"
" -> Nested Loop (cost=0.00..114712.66 rows=134269 width=149)"
" Join Filter: (dict_operator_class_rel.class = op_class.id)"
" -> Nested Loop (cost=0.00..100613.33 rows=134269 width=4)"
" Join Filter: (dict_operator.id = dict_operator_class_rel.operator)"
" -> Nested Loop (cost=0.00..85348.00 rows=26155 width=8)"
" -> Nested Loop (cost=0.00..75498.78 rows=34780 width=4)"
" -> Seq Scan on tp_published_hotline_ids ids (cost=0.00..536.40 rows=34940 width=4)"
" -> Index Scan using pk_ti_operator_hotline on ti_operator_hotline (cost=0.00..2.14 rows=1 width=8)"
" Index Cond: (id = ids.id)"
" Filter: (NOT trash)"
" -> Index Scan using pk_dict_operator on dict_operator (cost=0.00..0.27 rows=1 width=4)"
" Index Cond: (id = ti_operator_hotline.operator)"
" Filter: (NOT trash)"
" -> Index Scan using pk_dict_operator_class_rel on dict_operator_class_rel (cost=0.00..0.50 rows=7 width=8)"
" Index Cond: (operator = ti_operator_hotline.operator)"
" Filter: (NOT trash)"
" -> Materialize (cost=0.00..1.10 rows=7 width=149)"
" -> Seq Scan on dict_operator_class op_class (cost=0.00..1.07 rows=7 width=149)"
...
Рейтинг: 0 / 0
05.05.2014, 12:49:02
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38633189
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
1. Статистика нет - обновите.
2. Коды вьюх где?
3. LEFT JOIN "dict"."vw_dict_operator" AS "op" ON op.id = self.operator лишний в запросе - можно выкинуть
...
Рейтинг: 0 / 0
05.05.2014, 13:38:08
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38633297
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT
  "op_class"."id",
  "op_class"."name"
FROM
  "dict"."dict_operator_class" AS "op_class"
WHERE EXISTS(
  SELECT 1
  FROM
    "turpoisk"."vw_tp_published_hotline" AS "self"
    LEFT JOIN "dict"."vw_dict_operator" AS "op" ON op.id = self.operator
    LEFT JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON op.id = op_class_rel.operator
  WHERE
    op_class_rel.class = op_class.id
)
ORDER BY
	"op_class"."id" ASC
...
Рейтинг: 0 / 0
05.05.2014, 14:50:44
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38633685
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Ivan Durak1. Статистика нет - обновите.
2. Коды вьюх где?
3. LEFT JOIN "dict"."vw_dict_operator" AS "op" ON op.id = self.operator лишний в запросе - можно выкинуть
Да, про статистику не подумал, спасибо. Сделаю, отпишусь. Про LEFT JOIN - запрос динамически строится поэтому надо будет проанализировать этот нюанс - можно ли выбросить.

LeXa NalBat, спасибо, этот запрос ускорил выполнение > чем в 2 раза. Пока конечно все равно дорогой. > 2 секунд. Вопрос сразу - в чем профит? Мануал пишет что авторThis simple example is like an inner join on col2, but it produces at most one output row for each tab1 row, even if there are several matching tab2 rows я так понимаю здесь выигрыш именно за счет того что одна строка выбирается и отбрасывается дальнейшая проверка?

автор"Sort (cost=98097.67..98097.69 rows=6 width=149)"
" Sort Key: op_class.id"
" -> Nested Loop (cost=98096.42..98097.59 rows=6 width=149)"
" -> HashAggregate (cost=98096.42..98096.46 rows=4 width=4)"
" -> Nested Loop (cost=0.00..95404.26 rows=1076866 width=4)"
" -> Nested Loop (cost=0.00..75632.68 rows=34923 width=4)"
" -> Seq Scan on tp_published_hotline_ids ids (cost=0.00..537.83 rows=35083 width=4)"
" -> Index Scan using pk_ti_operator_hotline on ti_operator_hotline (cost=0.00..2.13 rows=1 width=8)"
" Index Cond: (id = ids.id)"
" Filter: (NOT trash)"
" -> Index Scan using pk_dict_operator_class_rel on dict_operator_class_rel (cost=0.00..0.50 rows=7 width=8)"
" Index Cond: (operator = ti_operator_hotline.operator)"
" Filter: (NOT trash)"
" -> Index Scan using pk_dict_operator_class on dict_operator_class op_class (cost=0.00..0.27 rows=1 width=149)"
" Index Cond: (id = dict_operator_class_rel.class)"


Думаю добавить индекс для op_class_rel.class - оказывается его нет.
...
Рейтинг: 0 / 0
05.05.2014, 18:59:45
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38633710
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
usver_dcIvan Durak1. Статистика нет - обновите.
2. Коды вьюх где?
3. LEFT JOIN "dict"."vw_dict_operator" AS "op" ON op.id = self.operator лишний в запросе - можно выкинуть
Да, про статистику не подумал, спасибо. Сделаю, отпишусь. Про LEFT JOIN - запрос динамически строится поэтому надо будет проанализировать этот нюанс - можно ли выбросить.

LeXa NalBat, спасибо, этот запрос ускорил выполнение > чем в 2 раза. Пока конечно все равно дорогой. > 2 секунд. Вопрос сразу - в чем профит? Мануал пишет что авторThis simple example is like an inner join on col2, but it produces at most one output row for each tab1 row, even if there are several matching tab2 rows я так понимаю здесь выигрыш именно за счет того что одна строка выбирается и отбрасывается дальнейшая проверка?

автор"Sort (cost=98097.67..98097.69 rows=6 width=149)"
" Sort Key: op_class.id"
" -> Nested Loop (cost=98096.42..98097.59 rows=6 width=149)"
" -> HashAggregate (cost=98096.42..98096.46 rows=4 width=4)"
" -> Nested Loop (cost=0.00..95404.26 rows=1076866 width=4)"
" -> Nested Loop (cost=0.00..75632.68 rows=34923 width=4)"
" -> Seq Scan on tp_published_hotline_ids ids (cost=0.00..537.83 rows=35083 width=4)"
" -> Index Scan using pk_ti_operator_hotline on ti_operator_hotline (cost=0.00..2.13 rows=1 width=8)"
" Index Cond: (id = ids.id)"
" Filter: (NOT trash)"
" -> Index Scan using pk_dict_operator_class_rel on dict_operator_class_rel (cost=0.00..0.50 rows=7 width=8)"
" Index Cond: (operator = ti_operator_hotline.operator)"
" Filter: (NOT trash)"
" -> Index Scan using pk_dict_operator_class on dict_operator_class op_class (cost=0.00..0.27 rows=1 width=149)"
" Index Cond: (id = dict_operator_class_rel.class)"


Думаю добавить индекс для op_class_rel.class - оказывается его нет.

1)приведите результаты explain analyze а не explain тогда можно будет понять где время тратится

2)есть ли индекс по "turpoisk"."vw_tp_published_hotline"(operator)

3)а какая у вас версия базы?

4)какие стоят значения random_page_cost и seq_page_cost

PS: в общем explain без analyze не дает много пищи для размышлений о оптимизации

PPS: профит в том что не образуется дублей которые приходится потом долго и упорно удалять

--
postgresql-consulting.ru
...
Рейтинг: 0 / 0
05.05.2014, 19:24:34
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38637900
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Maxim Boguk1)приведите результаты explain analyze а не explain тогда можно будет понять где время тратится

2)есть ли индекс по "turpoisk"."vw_tp_published_hotline"(operator)

3)а какая у вас версия базы?

4)какие стоят значения random_page_cost и seq_page_cost

PS: в общем explain без analyze не дает много пищи для размышлений о оптимизации

PPS: профит в том что не образуется дублей которые приходится потом долго и упорно удалять

--
postgresql-consulting.ru

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
explain analyse SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	(EXISTS(SELECT
	"self"."id"
FROM
	"turpoisk"."vw_tp_published_hotline" AS "self"
	 LEFT JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator
WHERE
	(op_class_rel.class = op_class.id)))
ORDER BY
	"op_class"."id" ASC



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
"Sort  (cost=94013.33..94013.34 rows=6 width=149) (actual time=14637.091..14637.097 rows=3 loops=1)"
"  Sort Key: op_class.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=94012.08..94013.25 rows=6 width=149) (actual time=14637.031..14637.070 rows=3 loops=1)"
"        ->  HashAggregate  (cost=94012.08..94012.12 rows=4 width=4) (actual time=14637.016..14637.022 rows=3 loops=1)"
"              ->  Nested Loop  (cost=0.00..91637.15 rows=949970 width=4) (actual time=0.044..11363.205 rows=2064900 loops=1)"
"                    ->  Nested Loop  (cost=0.00..73783.52 rows=31536 width=4) (actual time=0.028..509.909 rows=31712 loops=1)"
"                          ->  Seq Scan on tp_published_hotline_ids ids  (cost=0.00..504.12 rows=31712 width=4) (actual time=0.007..50.699 rows=31712 loops=1)"
"                          ->  Index Scan using pk_ti_operator_hotline on ti_operator_hotline  (cost=0.00..2.30 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=31712)"
"                                Index Cond: (id = ids.id)"
"                                Filter: (NOT trash)"
"                    ->  Index Scan using pk_dict_operator_class_rel on dict_operator_class_rel  (cost=0.00..0.50 rows=7 width=8) (actual time=0.007..0.145 rows=65 loops=31712)"
"                          Index Cond: (operator = ti_operator_hotline.operator)"
"                          Filter: (NOT trash)"
"                          Rows Removed by Filter: 0"
"        ->  Index Scan using pk_dict_operator_class on dict_operator_class op_class  (cost=0.00..0.27 rows=1 width=149) (actual time=0.005..0.007 rows=1 loops=3)"
"              Index Cond: (id = dict_operator_class_rel.class)"
"Total runtime: 14637.217 ms"



turpoisk.vw_tp_published_hotline

Код: plsql
1.
2.
3.
4.
5.
6.
7.
CREATE OR REPLACE VIEW turpoisk.vw_tp_published_hotline AS 
 SELECT self.id, self.operator, self.url, self.text, self.country, 
    self.placement, self.publication, self.date_create, self.date_start, 
    self.date_end, self.active, self.views, self.type, self.resort, 
    self.allocation, self.allocation_name, self.vote_up, self.vote_down
   FROM turpoisk.tp_published_hotline_ids ids
   JOIN ti.vw_ti_operator_hotline self ON self.id = ids.id;



2) Индекс на operator есть
3) Версия 9.2
...
Рейтинг: 0 / 0
11.05.2014, 10:23:02
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38637935
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
31к индекс сканов это многовато.
Надо попробовать без лупа
...
Рейтинг: 0 / 0
11.05.2014, 12:58:40
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38637938
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Ivan Durak31к индекс сканов это многовато.
Надо попробовать без лупа
Каким образом?
...
Рейтинг: 0 / 0
11.05.2014, 13:09:58
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38637948
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
usver_dc,

А сколько всего у вас записей в "dict"."dict_operator_class" ?

так как проблема вашего запроса в том что
автор" -> HashAggregate (cost=94012.08..94012.12 rows=4 width=4) (actual time=14637.016..14637.022 rows=3 loops=1)"
" -> Nested Loop (cost=0.00..91637.15 rows=949970 width=4) (actual time=0.044..11363.205 rows=2064900 loops=1)"

2M записей схлопываются в 3 записи...

попробуйте вот что:
в части
автор "turpoisk"."vw_tp_published_hotline" AS "self"
LEFT JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator

замените LEFT JOIN на JOIN так как учитывая то что дальше идет
Код: plsql
1.
op_class_rel.class = op_class.id

от LEFT JOIN тут один вред и путаница для планировщика...

есть ли индексы на "dict"."vw_dict_operator_class_rel"(class) и на "turpoisk"."vw_tp_published_hotline"(operator) ?

PS: я все больше склоняюсь к идее что проблема именно в вышеупомянутом LEFT JOIN
...
Рейтинг: 0 / 0
11.05.2014, 13:34:26
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38637953
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Maxim Bogukusver_dc,

А сколько всего у вас записей в "dict"."dict_operator_class" ?

так как проблема вашего запроса в том что
автор" -> HashAggregate (cost=94012.08..94012.12 rows=4 width=4) (actual time=14637.016..14637.022 rows=3 loops=1)"
" -> Nested Loop (cost=0.00..91637.15 rows=949970 width=4) (actual time=0.044..11363.205 rows=2064900 loops=1)"

2M записей схлопываются в 3 записи...

попробуйте вот что:
в части
автор "turpoisk"."vw_tp_published_hotline" AS "self"
LEFT JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator

замените LEFT JOIN на JOIN так как учитывая то что дальше идет
Код: plsql
1.
op_class_rel.class = op_class.id

от LEFT JOIN тут один вред и путаница для планировщика...

есть ли индексы на "dict"."vw_dict_operator_class_rel"(class) и на "turpoisk"."vw_tp_published_hotline"(operator) ?

PS: я все больше склоняюсь к идее что проблема именно в вышеупомянутом LEFT JOIN

Насчет left join тоже заметил и заменял на join, но эффекта не дала замена.

на operator есть, на class не было индекса. Об этом я упоминал выше. Заявка в группу бд висит. Посмотрим как поможет.

на данный момент какие правки внедрены:

Изменен запрос + заявка на БД:
авторvacuum analyze ti.ti_operator_hotline;
vacuum analyze turpoisk.tp_published_hotline_ids;


-- Index: dict.idx_dict_operator_class_rel_class

-- DROP INDEX dict.idx_dict_operator_class_rel_class;

CREATE INDEX idx_dict_operator_class_rel_class
ON dict.dict_operator_class_rel
USING btree
(class );


-- Index: dict.idx_dict_operator_class_rel_operator

-- DROP INDEX dict.idx_dict_operator_class_rel_operator;

CREATE INDEX idx_dict_operator_class_rel_operator
ON dict.dict_operator_class_rel
USING btree
(operator );


В понедельник отпишусь о результате. :)
...
Рейтинг: 0 / 0
11.05.2014, 13:47:12
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638484
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
В общем пока результат не очень. С 6-ти секунд опустились до 2-х. Это очень много.
...
Рейтинг: 0 / 0
12.05.2014, 11:20:07
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638588
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
usver_dcВ общем пока результат не очень. С 6-ти секунд опустились до 2-х. Это очень много.

Приведите на всякий случай:

1)А сколько всего у вас записей в "dict"."dict_operator_class" (это важно).

2)приведите результат (с JOIN внутри exists вместо LEFT JOIN)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
explain (analyze, verbose, costs) 
SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	(EXISTS(SELECT
	"self"."id"
FROM
	"turpoisk"."vw_tp_published_hotline" AS "self"
	 JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator
WHERE
	(op_class_rel.class = op_class.id)))
ORDER BY
	"op_class"."id" ASC




--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
12.05.2014, 12:25:34
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638623
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Maxim Boguk,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
explain (analyze, verbose, costs) 
SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	(EXISTS(SELECT
	"self"."id"
FROM
	"turpoisk"."vw_tp_published_hotline" AS "self"
	 JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator
WHERE
	(op_class_rel.class = op_class.id)))
ORDER BY
	"op_class"."id" ASC



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
"Sort  (cost=92647.24..92647.25 rows=6 width=149) (actual time=15693.833..15693.839 rows=3 loops=1)"
"  Output: op_class.id, op_class.name"
"  Sort Key: op_class.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=92645.99..92647.16 rows=6 width=149) (actual time=15693.778..15693.815 rows=3 loops=1)"
"        Output: op_class.id, op_class.name"
"        ->  HashAggregate  (cost=92645.99..92646.03 rows=4 width=4) (actual time=15693.763..15693.768 rows=3 loops=1)"
"              Output: dict_operator_class_rel.class"
"              ->  Nested Loop  (cost=0.00..90321.31 rows=929873 width=4) (actual time=0.043..12033.599 rows=2007085 loops=1)"
"                    Output: dict_operator_class_rel.class"
"                    ->  Nested Loop  (cost=0.00..73210.13 rows=30694 width=4) (actual time=0.028..563.051 rows=30836 loops=1)"
"                          Output: ti_operator_hotline.operator"
"                          ->  Seq Scan on turpoisk.tp_published_hotline_ids ids  (cost=0.00..495.36 rows=30836 width=4) (actual time=0.007..57.029 rows=30836 loops=1)"
"                                Output: ids.id"
"                          ->  Index Scan using pk_ti_operator_hotline on ti.ti_operator_hotline  (cost=0.00..2.35 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=30836)"
"                                Output: ti_operator_hotline.id, ti_operator_hotline.operator, ti_operator_hotline.url, ti_operator_hotline.text, ti_operator_hotline.country, ti_operator_hotline.placement, ti_operator_hotline.publication, ti_operator_hotline.date_create, ti_operator_hotline.date_start, ti_operator_hotline.date_end, ti_operator_hotline.active, ti_operator_hotline.trash, ti_operator_hotline.updated, ti_operator_hotline.views, ti_operator_hotline.type, ti_operator_hotline.resort, ti_operator_hotline.allocation, ti_operator_hotline.allocation_name, ti_operator_hotline.vote_up, ti_operator_hotline.vote_down"
"                                Index Cond: (ti_operator_hotline.id = ids.id)"
"                                Filter: (NOT ti_operator_hotline.trash)"
"                    ->  Index Scan using idx_dict_operator_class_rel_operator on dict.dict_operator_class_rel  (cost=0.00..0.49 rows=7 width=8) (actual time=0.007..0.147 rows=65 loops=30836)"
"                          Output: dict_operator_class_rel.operator, dict_operator_class_rel.country, dict_operator_class_rel.class, dict_operator_class_rel.updated, dict_operator_class_rel.trash, dict_operator_class_rel.date_create"
"                          Index Cond: (dict_operator_class_rel.operator = ti_operator_hotline.operator)"
"                          Filter: (NOT dict_operator_class_rel.trash)"
"                          Rows Removed by Filter: 0"
"        ->  Index Scan using pk_dict_operator_class on dict.dict_operator_class op_class  (cost=0.00..0.27 rows=1 width=149) (actual time=0.005..0.007 rows=1 loops=3)"
"              Output: op_class.id, op_class.name, op_class.info, op_class.active, op_class.trash, op_class.updated, op_class.weight"
"              Index Cond: (op_class.id = dict_operator_class_rel.class)"
"Total runtime: 15693.951 ms"



Код: plsql
1.
select count(*) from "dict"."dict_operator_class"



7
...
Рейтинг: 0 / 0
12.05.2014, 12:53:03
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638699
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
usver_dc,

Ok тогда начнем постадийно решать проблему (полезный топик получится)...
начнем с максимально упрощенной версии c выкинутой частью функционала:

что говорит:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain (analyze, verbose, costs) 
SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	EXISTS
(SELECT
	*
FROM "dict"."vw_dict_operator_class_rel" AS "op_class_rel"
WHERE op_class_rel.class = op_class.id
)



?

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
12.05.2014, 13:38:39
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638717
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Maxim Bogukusver_dc,

Ok тогда начнем постадийно решать проблему (полезный топик получится)...
начнем с максимально упрощенной версии c выкинутой частью функционала:

что говорит:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain (analyze, verbose, costs) 
SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	EXISTS
(SELECT
	*
FROM "dict"."vw_dict_operator_class_rel" AS "op_class_rel"
WHERE op_class_rel.class = op_class.id
)



?

--Maxim Boguk
www.postgresql-consulting.ru

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
"Nested Loop  (cost=273.17..274.34 rows=6 width=149) (actual time=26.319..26.375 rows=5 loops=1)"
"  Output: op_class.id, op_class.name"
"  ->  HashAggregate  (cost=273.17..273.21 rows=4 width=4) (actual time=26.303..26.310 rows=5 loops=1)"
"        Output: dict_operator_class_rel.class"
"        ->  Seq Scan on dict.dict_operator_class_rel  (cost=0.00..252.79 rows=8151 width=4) (actual time=0.009..13.544 rows=8154 loops=1)"
"              Output: dict_operator_class_rel.operator, dict_operator_class_rel.country, dict_operator_class_rel.class, dict_operator_class_rel.updated, dict_operator_class_rel.trash, dict_operator_class_rel.date_create"
"              Filter: (NOT dict_operator_class_rel.trash)"
"              Rows Removed by Filter: 5327"
"  ->  Index Scan using pk_dict_operator_class on dict.dict_operator_class op_class  (cost=0.00..0.27 rows=1 width=149) (actual time=0.004..0.006 rows=1 loops=5)"
"        Output: op_class.id, op_class.name, op_class.info, op_class.active, op_class.trash, op_class.updated, op_class.weight"
"        Index Cond: (op_class.id = dict_operator_class_rel.class)"
"Total runtime: 26.431 ms"
...
Рейтинг: 0 / 0
12.05.2014, 13:53:35
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638727
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
usver_dc,

так уже понятнее...

а приведите

\d+ "dict"."vw_dict_operator_class_rel"

\d+ dict.dict_operator_class_rel

\d+ dict.dict_operator_class

у меня ощущение что или индексы пропущены всетаки
или типы не совпадают
или в определении VIEW у вас косяк какой то...


--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
12.05.2014, 13:59:36
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638776
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
CREATE TABLE dict.dict_operator_class_rel
(
  operator integer NOT NULL,
  country integer NOT NULL,
  class integer NOT NULL,
  updated bigint NOT NULL,
  trash boolean NOT NULL DEFAULT false,
  date_create timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,
  CONSTRAINT pk_dict_operator_class_rel PRIMARY KEY (operator , country )
)
WITH (
  OIDS=FALSE
);

-- Index: dict.idx_dict_operator_class_rel_class

-- DROP INDEX dict.idx_dict_operator_class_rel_class;

CREATE INDEX idx_dict_operator_class_rel_class
  ON dict.dict_operator_class_rel
  USING btree
  (class );

-- Index: dict.idx_dict_operator_class_rel_operator

-- DROP INDEX dict.idx_dict_operator_class_rel_operator;

CREATE INDEX idx_dict_operator_class_rel_operator
  ON dict.dict_operator_class_rel
  USING btree
  (operator );



CREATE TABLE dict.dict_operator_class
(
  id integer NOT NULL,
  name character varying(255),
  info character varying(4000),
  active boolean NOT NULL DEFAULT true,
  trash boolean NOT NULL DEFAULT false,
  updated bigint NOT NULL DEFAULT 0,
  weight numeric(6,2) NOT NULL DEFAULT 0,
  CONSTRAINT pk_dict_operator_class PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);



CREATE OR REPLACE VIEW dict.vw_dict_operator_class_rel AS 
 SELECT dict_operator_class_rel.operator, dict_operator_class_rel.country, 
    dict_operator_class_rel.class, dict_operator_class_rel.updated, 
    dict_operator_class_rel.trash::integer AS trash, 
    dict_operator_class_rel.date_create
   FROM dict.dict_operator_class_rel
  WHERE dict_operator_class_rel.trash = false;



Maxim Bogukusver_dc,

так уже понятнее...

а приведите

\d+ "dict"."vw_dict_operator_class_rel"

\d+ dict.dict_operator_class_rel

\d+ dict.dict_operator_class

у меня ощущение что или индексы пропущены всетаки
или типы не совпадают
или в определении VIEW у вас косяк какой то...


--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
12.05.2014, 14:23:34
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638785
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
М/б индекс по trash добавить?
...
Рейтинг: 0 / 0
12.05.2014, 14:27:51
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638929
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
добавьте LIMIT 1 в подзапрос EXISTS
...
Рейтинг: 0 / 0
12.05.2014, 16:13:26
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638955
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
LeXa NalBat,

Поднялось до 6 секунд

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
explain (analyze, verbose, costs) SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	(EXISTS(SELECT
	"self"."id"
FROM
	"turpoisk"."vw_tp_published_hotline" AS "self"
	 JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator
WHERE
	(op_class_rel.class = op_class.id) LIMIT 1))
ORDER BY
	"op_class"."id" ASC



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
"Sort  (cost=5.12..5.13 rows=4 width=149) (actual time=6321.658..6321.662 rows=3 loops=1)"
"  Output: op_class.id, op_class.name"
"  Sort Key: op_class.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Seq Scan on dict.dict_operator_class op_class  (cost=0.00..5.08 rows=4 width=149) (actual time=2.104..6321.627 rows=3 loops=1)"
"        Output: op_class.id, op_class.name"
"        Filter: (SubPlan 1)"
"        Rows Removed by Filter: 4"
"        SubPlan 1"
"          ->  Limit  (cost=0.00..0.57 rows=1 width=4) (actual time=903.083..903.083 rows=0 loops=7)"
"                Output: ti_operator_hotline.id"
"                ->  Nested Loop  (cost=0.00..89652.49 rows=156349 width=4) (actual time=903.075..903.075 rows=0 loops=7)"
"                      Output: ti_operator_hotline.id"
"                      ->  Nested Loop  (cost=0.00..73402.38 rows=30954 width=8) (actual time=0.023..280.362 rows=17781 loops=7)"
"                            Output: ti_operator_hotline.id, ti_operator_hotline.operator"
"                            ->  Seq Scan on turpoisk.tp_published_hotline_ids ids  (cost=0.00..497.97 rows=31097 width=4) (actual time=0.007..29.629 rows=17781 loops=7)"
"                                  Output: ids.id"
"                            ->  Index Scan using pk_ti_operator_hotline on ti.ti_operator_hotline  (cost=0.00..2.33 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=124468)"
"                                  Output: ti_operator_hotline.id, ti_operator_hotline.operator, ti_operator_hotline.url, ti_operator_hotline.text, ti_operator_hotline.country, ti_operator_hotline.placement, ti_operator_hotline.publication, ti_operator_hotline.date_create, ti_operator_hotline.date_start, ti_operator_hotline.date_end, ti_operator_hotline.active, ti_operator_hotline.trash, ti_operator_hotline.updated, ti_operator_hotline.views, ti_operator_hotline.type, ti_operator_hotline.resort, ti_operator_hotline.allocation, ti_operator_hotline.allocation_name, ti_operator_hotline.vote_up, ti_operator_hotline.vote_down"
"                                  Index Cond: (ti_operator_hotline.id = ids.id)"
"                                  Filter: (NOT ti_operator_hotline.trash)"
"                      ->  Index Scan using idx_dict_operator_class_rel_operator on dict.dict_operator_class_rel  (cost=0.00..0.51 rows=1 width=4) (actual time=0.032..0.032 rows=0 loops=124468)"
"                            Output: dict_operator_class_rel.operator, dict_operator_class_rel.country, dict_operator_class_rel.class, dict_operator_class_rel.updated, dict_operator_class_rel.trash, dict_operator_class_rel.date_create"
"                            Index Cond: (dict_operator_class_rel.operator = ti_operator_hotline.operator)"
"                            Filter: ((NOT dict_operator_class_rel.trash) AND (dict_operator_class_rel.class = op_class.id))"
"                            Rows Removed by Filter: 65"
"Total runtime: 6321.816 ms"
...
Рейтинг: 0 / 0
12.05.2014, 16:33:49
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38638981
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
usver_dc,

Так... хорошо...

1)
Код: plsql
1.
explain analyze select * FROM "dict"."dict_operator_class";



2)
show random_page_cost;
show seq_page_cost;

3)
сделайте индекс по
Код: plaintext
"dict"."dict_operator_class_rel"(class) WHERE (NOT trash);

4)после 3 выполните
Код: plaintext
analyze "dict"."dict_operator_class_rel";
и
Код: plaintext
analyze "dict"."dict_operator_class";

5)сдетайте еще раз
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain (analyze, verbose, costs) 
SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	EXISTS
(SELECT
	*
FROM "dict"."vw_dict_operator_class_rel" AS "op_class_rel"
WHERE op_class_rel.class = op_class.id
)



В общем если кратко задача избавится от seq_scan в EXISTS, после этого скорее всего и основной запрос ускорится.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
12.05.2014, 16:54:32
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38639087
usver_dc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
usver_dc
Гость
Maxim Bogukusver_dc,

Так... хорошо...

1)
Код: plsql
1.
explain analyze select * FROM "dict"."dict_operator_class";



2)
show random_page_cost;
show seq_page_cost;

3)
сделайте индекс по
Код: plaintext
"dict"."dict_operator_class_rel"(class) WHERE (NOT trash);

4)после 3 выполните
Код: plaintext
analyze "dict"."dict_operator_class_rel";
и
Код: plaintext
analyze "dict"."dict_operator_class";

5)сдетайте еще раз
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain (analyze, verbose, costs) 
SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	EXISTS
(SELECT
	*
FROM "dict"."vw_dict_operator_class_rel" AS "op_class_rel"
WHERE op_class_rel.class = op_class.id
)



В общем если кратко задача избавится от seq_scan в EXISTS, после этого скорее всего и основной запрос ускорится.

--Maxim Boguk
www.postgresql-consulting.ru

1. Не понял, а зачем оно Вам? Тут же и так ясно, что будет скан по таблице.
Код: plsql
1.
2.
"Seq Scan on dict_operator_class  (cost=0.00..1.07 rows=7 width=689) (actual time=0.006..0.020 rows=7 loops=1)"
"Total runtime: 0.057 ms"


2)
show random_page_cost;
4
show seq_page_cost;
1

5) С тестовой бд (количество данных от релиза не сильно разнится):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
"Nested Loop  (cost=191.84..193.01 rows=6 width=6) (actual time=4.600..4.621 rows=5 loops=1)"
"  Output: op_class.id, op_class.name"
"  ->  HashAggregate  (cost=191.84..191.88 rows=4 width=4) (actual time=4.467..4.469 rows=5 loops=1)"
"        Output: dict_operator_class_rel.class"
"        ->  Seq Scan on dict.dict_operator_class_rel  (cost=0.00..178.82 rows=5210 width=4) (actual time=0.008..2.553 rows=5210 loops=1)"
"              Output: dict_operator_class_rel.operator, dict_operator_class_rel.country, dict_operator_class_rel.class, dict_operator_class_rel.updated, dict_operator_class_rel.trash, dict_operator_class_rel.date_create"
"              Filter: (NOT dict_operator_class_rel.trash)"
"              Rows Removed by Filter: 4472"
"  ->  Index Scan using pk_dict_operator_class on dict.dict_operator_class op_class  (cost=0.00..0.27 rows=1 width=6) (actual time=0.027..0.028 rows=1 loops=5)"
"        Output: op_class.id, op_class.name, op_class.info, op_class.active, op_class.trash, op_class.updated, op_class.weight"
"        Index Cond: (op_class.id = dict_operator_class_rel.class)"
"Total runtime: 4.761 ms"


ЗЫ может оптимизатор решил что ему быстрее пройти по таблице чем индекс юзать...
...
Рейтинг: 0 / 0
12.05.2014, 18:32:22
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38639589
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
Проблема в том что 31 тысяча индекс сиков по таблице с 9 тысячами записей - это ПЛОХО!
-------------------------------------------
1. Попробуй "turpoisk"."vw_tp_published_hotline" AS "self" заменить на
ti_operator_hotline если логика это позволяет!!!
2. Также независимо от #1 надо на ti_operator_hotline него индекс по полю operator.
И ждем MERGE JOIN.

Типа:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT
	"op_class"."id",
	"op_class"."name"
FROM
	"dict"."dict_operator_class" AS "op_class"
WHERE
	(EXISTS(SELECT
	"self"."id"
FROM
	ti_operator_hotline  AS "self"
	INNER JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator
WHERE
	(op_class_rel.class = op_class.id)))
ORDER BY
	"op_class"."id" ASC
...
Рейтинг: 0 / 0
13.05.2014, 12:06:56
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38639868
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
usver_dc,

автор
5) С тестовой бд (количество данных от релиза не сильно разнится):

"Nested Loop (cost=191.84..193.01 rows=6 width=6) (actual time=4.600..4.621 rows=5 loops=1)"
" Output: op_class.id, op_class.name"
" -> HashAggregate (cost=191.84..191.88 rows=4 width=4) (actual time=4.467..4.469 rows=5 loops=1)"
" Output: dict_operator_class_rel.class"
" -> Seq Scan on dict.dict_operator_class_rel (cost=0.00..178.82 rows=5210 width=4) (actual time=0.008..2.553 rows=5210 loops=1)"
" Output: dict_operator_class_rel.operator, dict_operator_class_rel.country, dict_operator_class_rel.class, dict_operator_class_rel.updated, dict_operator_class_rel.trash, dict_operator_class_rel.date_create"
" Filter: (NOT dict_operator_class_rel.trash)"
" Rows Removed by Filter: 4472"
" -> Index Scan using pk_dict_operator_class on dict.dict_operator_class op_class (cost=0.00..0.27 rows=1 width=6) (actual time=0.027..0.028 rows=1 loops=5)"
" Output: op_class.id, op_class.name, op_class.info, op_class.active, op_class.trash, op_class.updated, op_class.weight"
" Index Cond: (op_class.id = dict_operator_class_rel.class)"
"Total runtime: 4.761 ms"



Очень странно... а если сделать set enable_seqscan to 0; и повторить explain
и если будет hash join или merge join сделать
set enable_hashjoin to 0;
set enable_mergejoin to 0;

Я к чему веду - если в таблице dict.dict_operator_class 7 записей то это запрос должен выполнятся через
nested loop по таблице dict.dict_operator_class_rel и seq scan по dict.dict_operator_class
что на выходе даст всего 7 index scan по dict.dict_operator_class_rel
что и будет самым быстрым методом выполнения этого запроса, и очень странно что база сама этого не делает.

PS: я бы с настройками планировщика бы поигрался... особенно в сторону увеличения cpu_tuple_cost с 0.01 до 0.1 (чтобы оно меньше пыталось seq scan/merge join делать).
...
Рейтинг: 0 / 0
13.05.2014, 15:05:48
| Ответить | Цитировать | Написать  
оптимизация запроса
    #38640371
Troglodit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Troglodit
Гость
Код: sql
1.
LEFT JOIN "dict"."vw_dict_operator" AS "op" ON op.id = self.operator


Это как писали выше имхо лишнее,
Зачем делать LEFT JOIN если вы потом все равно делаете
Код: sql
1.
JOIN "dict"."dict_operator_class" AS "op_class" ON op_class_rel.class = op_class.id


этим джойном вы уничтожаете разницу между LEFT и INNER "op_class_rel"
ИМХО для начала переписать запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT DISTINCT
	"op_class"."id",
	"op_class"."name"
FROM
	"turpoisk"."vw_tp_published_hotline" AS "self"
	 JOIN "dict"."vw_dict_operator_class_rel" AS "op_class_rel" ON self.operator = op_class_rel.operator
	 JOIN "dict"."dict_operator_class" AS "op_class" ON op_class_rel.class = op_class.id


и оптимизатору уже будет гораздо проще определиться.
...
Рейтинг: 0 / 0
13.05.2014, 23:23:12
| Ответить | Цитировать | Написать  
25 сообщений из 28, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=38638981&tid=1998683]:
 0ms
get settings:
 5ms
get forum list:
 18ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 302ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 62ms
get tp. blocked users:
 1ms
others: 197ms
total:  603ms
созд. / загр.: 603ms
x
x
Закрыть


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