Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 13 сообщений из 13, страница 1 из 1
16.11.2016, 13:52
    #39348369
Strippett
Strippett
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Коллеги имеется вот такого рода запрос,как его лучше всего оптимизировать(дайте пожалуйста рекомендации на ваш взгляд,примеры и советы)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
 SELECT category.item_id,
    regexp_replace(bti_address.hier_pos::text, '\.\d+$'::text, ''::text) AS hier_pos,
    (
    SELECT data.value
    FROM data
    WHERE data.name::text = 'ROU_DOGM_for_GU_Spec/FULL_NAME'::text AND
        category.item_id::text = data.item_id::text
    ) AS full_name,
    COALESCE((((bti_ao.value::text || ', '::text) || bti_rn.value::text) ||
        ', '::text) || bti_address.value::text, ((
    SELECT data.value
    FROM data
    WHERE data.name::text = 'ROU_DOGM_for_GU_Spec/OFFICIAL_ADDRESS'::text AND
        category.item_id::text = data.item_id::text
    ))::text) AS official_address
FROM category category
     LEFT JOIN data bti_address ON bti_address.name::text =
         'ROU_DOGM_for_GU_Spec/BTI/ADDRESS'::text AND bti_address.item_id::text = category.item_id::text
     JOIN data bti_ao ON bti_ao.name::text =
         'ROU_DOGM_for_GU_Spec/BTI/AO'::text AND bti_ao.item_id::text = category.item_id::text AND regexp_replace(bti_address.hier_pos::text, '\.\d+$'::text, ''::text) = regexp_replace(bti_ao.hier_pos::text, '\.\d+$'::text, ''::text)
     JOIN data bti_rn ON bti_rn.name::text =
         'ROU_DOGM_for_GU_Spec/BTI/RN'::text AND bti_rn.item_id::text = category.item_id::text AND regexp_replace(bti_address.hier_pos::text, '\.\d+$'::text, ''::text) = regexp_replace(bti_rn.hier_pos::text, '\.\d+$'::text, ''::text)
WHERE category.catalog_id::text = '63804'::text AND category.value::text = '2'::text;



Дополнительно по этому запросу я сделал EXPLAIN ANALYZE и получилось что практически 85 процентов времени занимает работа HASH Join'а,я предполагаю что там можно сделать индекс по name::text,но Не уверен как правильно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 13:53
    #39348370
Strippett
Strippett
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
План запроса таков:
Код: 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.
QUERY PLAN
Hash Join  (cost=26006.04..65929.89 rows=1 width=124) (actual time=1369.417..348602.489 rows=4805 loops=1)
  Hash Cond: (((bti_rn.item_id)::text = (bti_address.item_id)::text) AND (regexp_replace((bti_rn.hier_pos)::text, '\.\d+$'::text, ''::text) = regexp_replace((bti_address.hier_pos)::text, '\.\d+$'::text, ''::text)))
  ->  Seq Scan on data bti_rn  (cost=0.00..12624.48 rows=14021 width=48) (actual time=1.774..101.811 rows=13817 loops=1)
        Filter: ((name)::text = 'ROU_DOGM_for_GU_Spec/BTI/RN'::text)
        Rows Removed by Filter: 368221
  ->  Hash  (cost=26005.94..26005.94 rows=7 width=101) (actual time=1295.671..1295.671 rows=4805 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 993kB
        ->  Hash Join  (cost=13110.40..26005.94 rows=7 width=101) (actual time=1072.436..1276.268 rows=4805 loops=1)
              Hash Cond: (((bti_address.item_id)::text = (category.item_id)::text) AND (regexp_replace((bti_address.hier_pos)::text, '\.\d+$'::text, ''::text) = regexp_replace((bti_ao.hier_pos)::text, '\.\d+$'::text, ''::text)))
              ->  Seq Scan on data bti_address  (cost=0.00..12624.48 rows=13550 width=48) (actual time=0.100..122.924 rows=13817 loops=1)
                    Filter: ((name)::text = 'ROU_DOGM_for_GU_Spec/BTI/ADDRESS'::text)
                    Rows Removed by Filter: 368221
              ->  Hash  (cost=13096.35..13096.35 rows=937 width=53) (actual time=1071.664..1071.664 rows=4805 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 550kB
                    ->  Hash Join  (cost=390.93..13096.35 rows=937 width=53) (actual time=23.369..1029.798 rows=4805 loops=1)
                          Hash Cond: ((bti_ao.item_id)::text = (category.item_id)::text)
                          ->  Seq Scan on data bti_ao  (cost=0.00..12624.48 rows=14314 width=48) (actual time=0.074..973.267 rows=13817 loops=1)
                                Filter: ((name)::text = 'ROU_DOGM_for_GU_Spec/BTI/AO'::text)
                                Rows Removed by Filter: 368221
                          ->  Hash  (cost=382.00..382.00 rows=714 width=5) (actual time=21.276..21.276 rows=733 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 28kB
                                ->  Seq Scan on category  (cost=0.00..382.00 rows=714 width=5) (actual time=0.629..19.614 rows=733 loops=1)
                                      Filter: (((catalog_id)::text = '63804'::text) AND ((value)::text = '2'::text))
                                      Rows Removed by Filter: 10934
  SubPlan 1
    ->  Seq Scan on data  (cost=0.00..13579.57 rows=1 width=38) (actual time=46.807..72.194 rows=1 loops=4805)
          Filter: (((name)::text = 'ROU_DOGM_for_GU_Spec/FULL_NAME'::text) AND ((category.item_id)::text = (item_id)::text))
          Rows Removed by Filter: 382037
  SubPlan 2
    ->  Seq Scan on data data_1  (cost=0.00..13579.57 rows=1 width=38) (never executed)
          Filter: (((name)::text = 'ROU_DOGM_for_GU_Spec/OFFICIAL_ADDRESS'::text) AND ((category.item_id)::text = (item_id)::text))
Total runtime: 348609.733 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 14:25
    #39348414
Melkij
Melkij
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Strippett,

а нафига вы всё к тексту приводите?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 15:14
    #39348468
Strippett
Strippett
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Melkij,Ну это не я делаю,а разработана так бд и только так внешнее приложение может воспринимать данные из этого материализованного представления.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 15:15
    #39348470
Strippett
Strippett
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Melkij,Судя по плану ведь приведение данных к тексту не сильно занимает много времени или я не прав?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 15:59
    #39348526
Melkij
Melkij
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
StrippettMelkij,Судя по плану ведь приведение данных к тексту не сильно занимает много времени или я не прав?
Угу, всего-то начисто лишает возможности использовать любые существующие индексы, кроме функциональных с этим же явным приведением типов. Что, в свою очередь, резко раздует объём индексов.

StrippettMelkij,Ну это не я делаю,а разработана так бд и только так внешнее приложение может воспринимать данные из этого материализованного представления.
Да, всякий дичайший бред на приложениях встречается. Иногда этот кусок говнокода даже не представляется возможным править.
Вот только если это вьюха - то этот запрос не имеет к приложению никакого отношения и вы можете выдать то же самое бинарное представление клиенту, но с нормальным запросом на субд.
И вы не указали DDL используемых таблиц.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 18:31
    #39348629
Strippett
Strippett
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Melkij,
До меня дошло почему используется тип text везде,тут же COALESCE он сам не умеет приводить данные к одному виду)

по DDL вы имеете ввиду то представление которое создаётся если так то вот:

CREATE MATERIALIZED VIEW public.m_sch (
item_id,
hier_pos,
full_name,
official_address
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 18:43
    #39348631
Melkij
Melkij
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
StrippettДо меня дошло почему используется тип text везде,тут же COALESCE он сам не умеет приводить данные к одному виду)
И как же это связано с тем, что приведение к тексту воткнуто везде, начиная с явно числовых item_id в объединениях таблиц?

Под DDL используемых таблиц я, как ни странно, имею в виду DDL используемых таблиц.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.11.2016, 21:14
    #39348676
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Strippett,

Код: sql
1.
2.
3.
4.
  SubPlan 1
    ->  Seq Scan on data  (cost=0.00..13579.57 rows=1 width=38) (actual time=46.807..72.194 rows=1 loops=4805)
          Filter: (((name)::text = 'ROU_DOGM_for_GU_Spec/FULL_NAME'::text) AND ((category.item_id)::text = (item_id)::text))
          Rows Removed by Filter: 382037



сейчас большая часть времени уходит на вычисление этого подзапроса. если нельзя переписать этот треш с приведениями, то нужно как минимум создать индекс в data либо по item_id::text либо по item_id::text и name::text (в зависимости от данных).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.11.2016, 18:45
    #39349376
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
MelkijStrippettMelkij,Судя по плану ведь приведение данных к тексту не сильно занимает много времени или я не прав?
Угу, всего-то начисто лишает возможности использовать любые существующие индексы, кроме функциональных с этим же явным приведением типов. Что, в свою очередь, резко раздует объём индексов.


А ничего, что pg неявно приводит varchar в text, что можно увидеть на любом плане ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.11.2016, 19:10
    #39349393
Lonepsycho
Lonepsycho
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ora601,

думаю, Melkij имел ввиду это:
Код: sql
1.
bti_rn.item_id::text = category.item_id::text

ID, конечно, может быть текст, но по этому поводу есть некоторые сомнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2016, 23:39
    #39368220
ZiB
ZiB
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Strippett,


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
with c as (
	select
		item_id,
		item_id::text as item_id_text
	from category
	WHERE category.catalog_id::text = '63804'::text
		AND category.value::text = '2'::text
)
,d as (
	select
		data.item_id::text as item_id,
		data.name::text as name,
		data.value::text as value,
		data.regexp_replace(hier_pos::text, '\.\d+$'::text, ''::text) as hier_pos
	from data
	join c on c.item_id::text = data.item_id::text
)
SELECT
	c.item_id,
	bti_address.hier_pos,
	full_name.value AS full_name,
	COALESCE(bti_ao.value || ', ' || bti_rn.value || ', ' || bti_address.value,official.value) AS official_address
FROM c
JOIN d bti_address ON bti_address.item_id = c.item_id_text
	AND bti_address.name = 'ROU_DOGM_for_GU_Spec/BTI/ADDRESS'
JOIN d bti_ao ON bti_ao.item_id = c.item_id_text
	AND bti_ao.hier_pos = bti_address.hier_pos
	AND bti_ao.name = 'ROU_DOGM_for_GU_Spec/BTI/AO'
JOIN d bti_rn ON bti_rn.item_id = c.item_id_text
	AND bti_address.hier_pos = bti_rn.hier_pos
	AND bti_rn.name = 'ROU_DOGM_for_GU_Spec/BTI/RN'
left join d full_name on full_name.item_id = c.item_id_text
	and full_name.name = 'ROU_DOGM_for_GU_Spec/FULL_NAME'
left join d official on official.item_id = c.item_id_text
	and official.name = 'ROU_DOGM_for_GU_Spec/OFFICIAL_ADDRESS';



А так работает?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2016, 14:12
    #39368653
ZiB
ZiB
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ZiB,

Код: sql
1.
data.regexp_replace(hier_pos::text, '\.\d+$'::text, ''::text) as hier_pos


надо как
Код: sql
1.
regexp_replace(data.hier_pos::text, '\.\d+$'::text, ''::text) as hier_pos
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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