Огромная разница по времени выполнения запроса / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Огромная разница по времени выполнения запроса

14 сообщений из 14, страница 1 из 1

Огромная разница по времени выполнения запроса

#38796547

sabbath66

Гость

Доброго дня всем,

Помогите, пожалуйста, разобраться. При примерно одинаковом количестве строк на выводе, при разных входящих данных один и тот-же запрос выполняется либо 1-2 секунды, либо 25 МИНУТ! Меняется только WHERE LA_ART_ID = 1359385 на 1359418. В одиночные кавычки ставить пробовал, таблицы проиндексированы. Заранее огромное спасибо. Вот сам запрос:

SELECT TYP_ID, MFA_BRAND, DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT,
TOF_DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT, TYP_PCON_START, TYP_PCON_END,
TYP_CCM, TYP_KW_FROM, TYP_KW_UPTO, TYP_HP_FROM, TYP_HP_UPTO,
TYP_CYLINDERS, string_agg(TOF_ENGINES.ENG_CODE, ', '),
DES_TEXTS2.TEX_TEXT AS TYP_ENGINE_DES_TEXT,
DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT,
CASE WHEN DES_TEXTS4.TEX_TEXT IS NULL THEN DES_TEXTS5.TEX_TEXT ELSE DES_TEXTS4.TEX_TEXT END AS TYP_BODY_DES_TEXT,
DES_TEXTS6.TEX_TEXT AS TYP_AXLE_DES_TEXT, TYP_MAX_WEIGHT

FROM TOF_LINK_ART

INNER JOIN TOF_LINK_LA_TYP ON LAT_LA_ID = LA_ID AND SUBSTRING(lat_ctm FROM 249 FOR 1)='1'
INNER JOIN TOF_TYPES ON TYP_ID = LAT_TYP_ID AND SUBSTRING(typ_ctm FROM 249 FOR 1)='1'
INNER JOIN TOF_COUNTRY_DESIGNATIONS ON TOF_COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID
INNER JOIN TOF_DES_TEXTS ON TOF_DES_TEXTS.TEX_ID = TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID
INNER JOIN TOF_MODELS ON MOD_ID = TYP_MOD_ID
INNER JOIN TOF_MANUFACTURERS ON MFA_ID = MOD_MFA_ID
INNER JOIN TOF_COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID AND SUBSTRING(COUNTRY_DESIGNATIONS2.cds_ctm FROM 249 FOR 1)='1'
INNER JOIN TOF_DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID = COUNTRY_DESIGNATIONS2.CDS_TEX_ID
LEFT JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID = TYP_KV_ENGINE_DES_ID
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = TOF_DESIGNATIONS.DES_TEX_ID
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = TYP_KV_FUEL_DES_ID
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN TOF_LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID
LEFT JOIN TOF_ENGINES ON ENG_ID = LTE_ENG_ID
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = TYP_KV_BODY_DES_ID
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = TYP_KV_MODEL_DES_ID
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = TYP_KV_AXLE_DES_ID
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID

WHERE LA_ART_ID = 1359385

AND TOF_COUNTRY_DESIGNATIONS.CDS_LNG_ID = 37
AND COUNTRY_DESIGNATIONS2.CDS_LNG_ID = 37
AND (TOF_DESIGNATIONS.DES_LNG_ID IS NULL OR TOF_DESIGNATIONS.DES_LNG_ID = 37)
AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = 37)
AND (DESIGNATIONS3.DES_LNG_ID IS NULL OR DESIGNATIONS3.DES_LNG_ID = 37)
AND (DESIGNATIONS4.DES_LNG_ID IS NULL OR DESIGNATIONS4.DES_LNG_ID = 37)
AND (DESIGNATIONS5.DES_LNG_ID IS NULL OR DESIGNATIONS5.DES_LNG_ID = 37)
AND SUBSTRING(la_ctm FROM 249 FOR 1)='1'

GROUP BY tof_types.typ_id, tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, TYP_PCON_START, TYP_PCON_END, TYP_CCM, TYP_KW_FROM, TYP_KW_UPTO, TYP_HP_FROM, TYP_HP_UPTO, TYP_CYLINDERS , des_texts2.tex_text, des_texts3.tex_text, des_texts4.tex_text, des_texts5.tex_text, des_texts6.tex_text ORDER BY MFA_BRAND, MOD_CDS_TEXT, TYP_CDS_TEXT, TYP_PCON_START, TYP_CCM

...

Рейтинг:

0 / 0

05.11.2014, 16:53:15

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38796562

планы где

Гость

sabbath66Доброго дня всем,

Помогите, пожалуйста, разобраться. При примерно одинаковом количестве строк на выводе, при разных входящих данных один и тот-же запрос выполняется либо 1-2 секунды, либо 25 МИНУТ! Меняется только WHERE LA_ART_ID = 1359385 на 1359418. В одиночные кавычки ставить пробовал, таблицы проиндексированы. Заранее огромное спасибо. Вот сам запрос:

поправил

Код: 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.
36.
37.
38.
39.
40.
41.
42.
43.
44.

SELECT	TYP_ID,	MFA_BRAND,	DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT,	
			TOF_DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT,	TYP_PCON_START,	TYP_PCON_END,	 
			TYP_CCM,	TYP_KW_FROM,	TYP_KW_UPTO,	TYP_HP_FROM,	TYP_HP_UPTO,	
			TYP_CYLINDERS,	string_agg(TOF_ENGINES.ENG_CODE, ', '),	
			DES_TEXTS2.TEX_TEXT AS  TYP_ENGINE_DES_TEXT, 	
			DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT, 	
			CASE WHEN DES_TEXTS4.TEX_TEXT IS NULL THEN DES_TEXTS5.TEX_TEXT ELSE DES_TEXTS4.TEX_TEXT END AS TYP_BODY_DES_TEXT, 	 
			DES_TEXTS6.TEX_TEXT AS TYP_AXLE_DES_TEXT, 	TYP_MAX_WEIGHT 

			FROM   TOF_LINK_ART 

			INNER JOIN TOF_LINK_LA_TYP ON LAT_LA_ID = LA_ID AND SUBSTRING(lat_ctm FROM 249 FOR 1)='1'
			INNER JOIN TOF_TYPES ON TYP_ID = LAT_TYP_ID AND SUBSTRING(typ_ctm FROM 249 FOR 1)='1' 
			INNER JOIN TOF_COUNTRY_DESIGNATIONS ON TOF_COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID 
			INNER JOIN TOF_DES_TEXTS ON TOF_DES_TEXTS.TEX_ID = TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID 
			INNER JOIN TOF_MODELS ON MOD_ID = TYP_MOD_ID 
			INNER JOIN TOF_MANUFACTURERS ON MFA_ID = MOD_MFA_ID 
			INNER JOIN TOF_COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID AND SUBSTRING(COUNTRY_DESIGNATIONS2.cds_ctm FROM 249 FOR 1)='1' 
			INNER JOIN TOF_DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID = COUNTRY_DESIGNATIONS2.CDS_TEX_ID 
			LEFT JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID = TYP_KV_ENGINE_DES_ID 
			LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = TOF_DESIGNATIONS.DES_TEX_ID 
			LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = TYP_KV_FUEL_DES_ID 
			LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID 
			LEFT JOIN TOF_LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID 
			LEFT JOIN TOF_ENGINES ON ENG_ID = LTE_ENG_ID 
			LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = TYP_KV_BODY_DES_ID 
			LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID 
			LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = TYP_KV_MODEL_DES_ID 
			LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID 
			LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = TYP_KV_AXLE_DES_ID 
			LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID 
			
			WHERE	LA_ART_ID = 1359385 
			
			AND	TOF_COUNTRY_DESIGNATIONS.CDS_LNG_ID = 37 
			AND	COUNTRY_DESIGNATIONS2.CDS_LNG_ID = 37 
			AND (TOF_DESIGNATIONS.DES_LNG_ID IS NULL OR TOF_DESIGNATIONS.DES_LNG_ID = 37) 
			AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = 37) 
			AND (DESIGNATIONS3.DES_LNG_ID IS NULL OR DESIGNATIONS3.DES_LNG_ID = 37) 
			AND (DESIGNATIONS4.DES_LNG_ID IS NULL OR DESIGNATIONS4.DES_LNG_ID = 37) 
			AND (DESIGNATIONS5.DES_LNG_ID IS NULL OR DESIGNATIONS5.DES_LNG_ID = 37)  
			AND SUBSTRING(la_ctm FROM 249 FOR 1)='1' 
			
			GROUP BY tof_types.typ_id, tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, TYP_PCON_START, TYP_PCON_END, TYP_CCM,	TYP_KW_FROM,	TYP_KW_UPTO,	TYP_HP_FROM,	TYP_HP_UPTO,	TYP_CYLINDERS , des_texts2.tex_text, des_texts3.tex_text, des_texts4.tex_text, des_texts5.tex_text, des_texts6.tex_text ORDER BY	MFA_BRAND,	MOD_CDS_TEXT,	TYP_CDS_TEXT,	TYP_PCON_START,	TYP_CCM

планы где

...

Рейтинг:

0 / 0

05.11.2014, 17:03:53

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38796590

sabbath66

Гость

>> планы где

В смысле?... Я не слишком большой знаток :)

...

Рейтинг:

0 / 0

05.11.2014, 17:35:39

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38796595

RTFM FM FM

Гость

sabbath66,

http://www.postgresql.org/docs/current/static/sql-explain.html

...

Рейтинг:

0 / 0

05.11.2014, 17:38:12

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38796619

sabbath66

Гость

Планы

для быстрого запроса

"Sort (cost=2139.20..2139.20 rows=1 width=315)"
" Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
" -> HashAggregate (cost=2139.17..2139.19 rows=1 width=315)"
" -> Nested Loop Left Join (cost=19.06..2139.13 rows=1 width=315)"
" -> Nested Loop Left Join (cost=18.63..2130.75 rows=1 width=282)"
" Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=18.21..2121.62 rows=1 width=282)"
" -> Nested Loop Left Join (cost=17.78..2113.24 rows=1 width=249)"
" Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=17.36..2104.10 rows=1 width=249)"
" -> Nested Loop Left Join (cost=16.93..2095.73 rows=1 width=216)"
" Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=16.50..2086.59 rows=1 width=216)"
" -> Nested Loop Left Join (cost=16.22..2078.39 rows=1 width=212)"
" -> Nested Loop Left Join (cost=15.93..2070.15 rows=1 width=208)"
" -> Nested Loop Left Join (cost=15.50..2061.77 rows=1 width=175)"
" Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=15.07..2052.63 rows=1 width=175)"
" -> Nested Loop Left Join (cost=14.65..2044.26 rows=1 width=142)"
" Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
" -> Nested Loop (cost=14.22..2035.12 rows=1 width=142)"
" -> Nested Loop (cost=13.79..2026.73 rows=1 width=109)"
" Join Filter: (tof_models.mod_mfa_id = tof_manufacturers.mfa_id)"
" -> Nested Loop (cost=13.79..2004.00 rows=1 width=102)"
" -> Nested Loop (cost=13.37..1995.62 rows=1 width=69)"
" -> Nested Loop (cost=12.93..1985.83 rows=1 width=69)"
" -> Nested Loop (cost=12.50..1975.78 rows=1 width=69)"
" -> Nested Loop (cost=12.22..1967.62 rows=1 width=67)"
" -> Nested Loop (cost=11.92..1959.38 rows=1 width=4)"
" -> Index Scan using tof_link_art_la_art_id_idx on tof_link_art (cost=0.44..405.35 rows=1 width=4)"
" Index Cond: (la_art_id = 1359385)"
" Filter: ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit")"
" -> Bitmap Heap Scan on tof_link_la_typ (cost=11.49..1554.01 rows=2 width=8)"
" Recheck Cond: (lat_la_id = tof_link_art.la_id)"
" Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
" -> Bitmap Index Scan on tof_link_la_typ_lat_la_id_idx (cost=0.00..11.49 rows=389 width=0)"
" Index Cond: (lat_la_id = tof_link_art.la_id)"
" -> Index Scan using tof_types_typ_id_idx on tof_types (cost=0.29..8.24 rows=1 width=67)"
" Index Cond: (typ_id = tof_link_la_typ.lat_typ_id)"
" Filter: ("substring"((typ_ctm)::"bit", 249, 1) = B'1'::"bit")"
" -> Index Scan using tof_models_mod_id_idx on tof_models (cost=0.29..8.15 rows=1 width=10)"
" Index Cond: (mod_id = tof_types.typ_mod_id)"
" -> Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2 (cost=0.43..10.03 rows=1 width=8)"
" Index Cond: (cds_id = tof_models.mod_cds_id)"
" Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
" -> Index Scan using tof_country_designations_cds_id_idx on tof_country_designations (cost=0.43..9.77 rows=2 width=8)"
" Index Cond: (cds_id = tof_types.typ_cds_id)"
" Filter: (cds_lng_id = 37)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
" -> Seq Scan on tof_manufacturers (cost=0.00..15.66 rows=566 width=11)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7 (cost=0.43..8.38 rows=1 width=41)"
" Index Cond: (tex_id = country_designations2.cds_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = tof_designations.des_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations2 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations2.des_tex_id)"
" -> Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng (cost=0.29..8.23 rows=1 width=8)"
" Index Cond: (lte_typ_id = tof_types.typ_id)"
" -> Index Scan using tof_engines_eng_id_idx on tof_engines (cost=0.29..8.19 rows=1 width=12)"
" Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations3 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations3.des_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations4 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations4.des_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations5 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations5.des_tex_id)"

для медленного запроса

"Sort (cost=9526.92..9526.93 rows=1 width=315)"
" Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
" -> HashAggregate (cost=9526.90..9526.91 rows=1 width=315)"
" -> Nested Loop Left Join (cost=433.74..9526.85 rows=1 width=315)"
" -> Nested Loop Left Join (cost=433.32..9518.48 rows=1 width=282)"
" Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=432.89..9509.34 rows=1 width=282)"
" -> Nested Loop Left Join (cost=432.46..9500.96 rows=1 width=249)"
" Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=432.04..9491.83 rows=1 width=249)"
" -> Nested Loop Left Join (cost=431.61..9483.45 rows=1 width=216)"
" Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=431.19..9474.31 rows=1 width=216)"
" -> Nested Loop Left Join (cost=430.90..9466.11 rows=1 width=212)"
" -> Nested Loop Left Join (cost=430.61..9457.87 rows=1 width=208)"
" -> Nested Loop Left Join (cost=430.18..9449.50 rows=1 width=175)"
" Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
" -> Nested Loop Left Join (cost=429.76..9440.36 rows=1 width=175)"
" -> Nested Loop Left Join (cost=429.33..9431.98 rows=1 width=142)"
" Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
" -> Nested Loop (cost=428.90..9422.84 rows=1 width=142)"
" -> Nested Loop (cost=428.48..9414.46 rows=1 width=109)"
" -> Nested Loop (cost=428.20..9407.55 rows=1 width=102)"
" -> Nested Loop (cost=427.77..9399.17 rows=1 width=69)"
" -> Nested Loop (cost=427.34..9389.38 rows=1 width=69)"
" -> Nested Loop (cost=426.90..9120.60 rows=32 width=73)"
" -> Nested Loop (cost=426.34..6765.70 rows=4 width=69)"
" -> Hash Join (cost=425.90..3180.04 rows=357 width=69)"
" Hash Cond: (tof_types.typ_mod_id = tof_models.mod_id)"
" -> Seq Scan on tof_types (cost=0.00..2747.44 rows=357 width=67)"
" Filter: ("substring"((typ_ctm)::"bit", 249, 1) = B'1'::"bit")"
" -> Hash (cost=289.29..289.29 rows=10929 width=10)"
" -> Seq Scan on tof_models (cost=0.00..289.29 rows=10929 width=10)"
" -> Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2 (cost=0.43..10.03 rows=1 w (...)"
" Index Cond: (cds_id = tof_models.mod_cds_id)"
" Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
" -> Index Scan using tof_link_la_typ_lat_typ_id_idx on tof_link_la_typ (cost=0.57..588.35 rows=38 width=8)"
" Index Cond: (lat_typ_id = tof_types.typ_id)"
" Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
" -> Index Scan using tof_link_art_la_id_idx on tof_link_art (cost=0.44..8.39 rows=1 width=4)"
" Index Cond: (la_id = tof_link_la_typ.lat_la_id)"
" Filter: ((la_art_id = 1359418) AND ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit"))"
" -> Index Scan using tof_country_designations_cds_id_idx on tof_country_designations (cost=0.43..9.77 rows=2 width=8)"
" Index Cond: (cds_id = tof_types.typ_cds_id)"
" Filter: (cds_lng_id = 37)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
" -> Index Scan using tof_manufacturers_mfa_id_idx on tof_manufacturers (cost=0.28..6.90 rows=1 width=11)"
" Index Cond: (mfa_id = tof_models.mod_mfa_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7 (cost=0.43..8.38 rows=1 width=41)"
" Index Cond: (tex_id = country_designations2.cds_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = tof_designations.des_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations2 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations2.des_tex_id)"
" -> Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng (cost=0.29..8.23 rows=1 width=8)"
" Index Cond: (lte_typ_id = tof_types.typ_id)"
" -> Index Scan using tof_engines_eng_id_idx on tof_engines (cost=0.29..8.19 rows=1 width=12)"
" Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations3 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations3.des_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations4 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations4.des_tex_id)"
" -> Index Scan using tof_designations_des_id_idx on tof_designations designations5 (cost=0.42..8.81 rows=26 width=10)"
" Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
" -> Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6 (cost=0.43..8.37 rows=1 width=41)"
" Index Cond: (tex_id = designations5.des_tex_id)"

...

Рейтинг:

0 / 0

05.11.2014, 17:55:15

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38796640

ещё чуток

Гость

sabbath66Планы

для быстрого запроса

поправил

Код: 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.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.

Sort  (cost=2139.20..2139.20 rows=1 width=315)
  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm
  ->  HashAggregate  (cost=2139.17..2139.19 rows=1 width=315)
        ->  Nested Loop Left Join  (cost=19.06..2139.13 rows=1 width=315)
              ->  Nested Loop Left Join  (cost=18.63..2130.75 rows=1 width=282)
                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))
                    ->  Nested Loop Left Join  (cost=18.21..2121.62 rows=1 width=282)
                          ->  Nested Loop Left Join  (cost=17.78..2113.24 rows=1 width=249)
                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))
                                ->  Nested Loop Left Join  (cost=17.36..2104.10 rows=1 width=249)
                                      ->  Nested Loop Left Join  (cost=16.93..2095.73 rows=1 width=216)
                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))
                                            ->  Nested Loop Left Join  (cost=16.50..2086.59 rows=1 width=216)
                                                  ->  Nested Loop Left Join  (cost=16.22..2078.39 rows=1 width=212)
                                                        ->  Nested Loop Left Join  (cost=15.93..2070.15 rows=1 width=208)
                                                              ->  Nested Loop Left Join  (cost=15.50..2061.77 rows=1 width=175)
                                                                    Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))
                                                                    ->  Nested Loop Left Join  (cost=15.07..2052.63 rows=1 width=175)
                                                                          ->  Nested Loop Left Join  (cost=14.65..2044.26 rows=1 width=142)
                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))
                                                                                ->  Nested Loop  (cost=14.22..2035.12 rows=1 width=142)
                                                                                      ->  Nested Loop  (cost=13.79..2026.73 rows=1 width=109)
                                                                                            Join Filter: (tof_models.mod_mfa_id = tof_manufacturers.mfa_id)
                                                                                            ->  Nested Loop  (cost=13.79..2004.00 rows=1 width=102)
                                                                                                  ->  Nested Loop  (cost=13.37..1995.62 rows=1 width=69)
                                                                                                        ->  Nested Loop  (cost=12.93..1985.83 rows=1 width=69)
                                                                                                              ->  Nested Loop  (cost=12.50..1975.78 rows=1 width=69)
                                                                                                                    ->  Nested Loop  (cost=12.22..1967.62 rows=1 width=67)
                                                                                                                          ->  Nested Loop  (cost=11.92..1959.38 rows=1 width=4)
                                                                                                                                ->  Index Scan using tof_link_art_la_art_id_idx on tof_link_art  (cost=0.44..405.35 rows=1 width=4)
                                                                                                                                      Index Cond: (la_art_id = 1359385)
                                                                                                                                      Filter: (substring((la_ctm)::bit, 249, 1) = B'1'::bit)
                                                                                                                                ->  Bitmap Heap Scan on tof_link_la_typ  (cost=11.49..1554.01 rows=2 width=8)
                                                                                                                                      Recheck Cond: (lat_la_id = tof_link_art.la_id)
                                                                                                                                      Filter: (substring((lat_ctm)::bit, 249, 1) = B'1'::bit)
                                                                                                                                      ->  Bitmap Index Scan on tof_link_la_typ_lat_la_id_idx  (cost=0.00..11.49 rows=389 width=0)
                                                                                                                                            Index Cond: (lat_la_id = tof_link_art.la_id)
                                                                                                                          ->  Index Scan using tof_types_typ_id_idx on tof_types  (cost=0.29..8.24 rows=1 width=67)
                                                                                                                                Index Cond: (typ_id = tof_link_la_typ.lat_typ_id)
                                                                                                                                Filter: (substring((typ_ctm)::bit, 249, 1) = B'1'::bit)
                                                                                                                    ->  Index Scan using tof_models_mod_id_idx on tof_models  (cost=0.29..8.15 rows=1 width=10)
                                                                                                                          Index Cond: (mod_id = tof_types.typ_mod_id)
                                                                                                              ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 width=8)
                                                                                                                    Index Cond: (cds_id = tof_models.mod_cds_id)
                                                                                                                    Filter: ((cds_lng_id = 37) AND (substring((cds_ctm)::bit, 249, 1) = B'1'::bit))
                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8)
                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)
                                                                                                              Filter: (cds_lng_id = 37)
                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41)
                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)
                                                                                            ->  Seq Scan on tof_manufacturers  (cost=0.00..15.66 rows=566 width=11)
                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41)
                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)
                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10)
                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)
                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41)
                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)
                                                                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10)
                                                                          Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)
                                                              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41)
                                                                    Index Cond: (tex_id = designations2.des_tex_id)
                                                        ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8)
                                                              Index Cond: (lte_typ_id = tof_types.typ_id)
                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12)
                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)
                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10)
                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)
                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41)
                                            Index Cond: (tex_id = designations3.des_tex_id)
                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10)
                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)
                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41)
                                Index Cond: (tex_id = designations4.des_tex_id)
                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10)
                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)
              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41)
                    Index Cond: (tex_id = designations5.des_tex_id)

для медленного запроса

поправил

Код: sql

Sort  (cost=9526.92..9526.93 rows=1 width=315)
  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm
  ->  HashAggregate  (cost=9526.90..9526.91 rows=1 width=315)
        ->  Nested Loop Left Join  (cost=433.74..9526.85 rows=1 width=315)
              ->  Nested Loop Left Join  (cost=433.32..9518.48 rows=1 width=282)
                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))
                    ->  Nested Loop Left Join  (cost=432.89..9509.34 rows=1 width=282)
                          ->  Nested Loop Left Join  (cost=432.46..9500.96 rows=1 width=249)
                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))
                                ->  Nested Loop Left Join  (cost=432.04..9491.83 rows=1 width=249)
                                      ->  Nested Loop Left Join  (cost=431.61..9483.45 rows=1 width=216)
                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))
                                            ->  Nested Loop Left Join  (cost=431.19..9474.31 rows=1 width=216)
                                                  ->  Nested Loop Left Join  (cost=430.90..9466.11 rows=1 width=212)
                                                        ->  Nested Loop Left Join  (cost=430.61..9457.87 rows=1 width=208)
                                                              ->  Nested Loop Left Join  (cost=430.18..9449.50 rows=1 width=175)
                                                                    Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))
                                                                    ->  Nested Loop Left Join  (cost=429.76..9440.36 rows=1 width=175)
                                                                          ->  Nested Loop Left Join  (cost=429.33..9431.98 rows=1 width=142)
                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))
                                                                                ->  Nested Loop  (cost=428.90..9422.84 rows=1 width=142)
                                                                                      ->  Nested Loop  (cost=428.48..9414.46 rows=1 width=109)
                                                                                            ->  Nested Loop  (cost=428.20..9407.55 rows=1 width=102)
                                                                                                  ->  Nested Loop  (cost=427.77..9399.17 rows=1 width=69)
                                                                                                        ->  Nested Loop  (cost=427.34..9389.38 rows=1 width=69)
                                                                                                              ->  Nested Loop  (cost=426.90..9120.60 rows=32 width=73)
                                                                                                                    ->  Nested Loop  (cost=426.34..6765.70 rows=4 width=69)
                                                                                                                          ->  Hash Join  (cost=425.90..3180.04 rows=357 width=69)
                                                                                                                                Hash Cond: (tof_types.typ_mod_id = tof_models.mod_id)
                                                                                                                                ->  Seq Scan on tof_types  (cost=0.00..2747.44 rows=357 width=67)
                                                                                                                                      Filter: (substring((typ_ctm)::bit, 249, 1) = B'1'::bit)
                                                                                                                                ->  Hash  (cost=289.29..289.29 rows=10929 width=10)
                                                                                                                                      ->  Seq Scan on tof_models  (cost=0.00..289.29 rows=10929 width=10)
                                                                                                                          ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 w (...)
                                                                                                                                Index Cond: (cds_id = tof_models.mod_cds_id)
                                                                                                                                Filter: ((cds_lng_id = 37) AND (substring((cds_ctm)::bit, 249, 1) = B'1'::bit))
                                                                                                                    ->  Index Scan using tof_link_la_typ_lat_typ_id_idx on tof_link_la_typ  (cost=0.57..588.35 rows=38 width=8)
                                                                                                                          Index Cond: (lat_typ_id = tof_types.typ_id)
                                                                                                                          Filter: (substring((lat_ctm)::bit, 249, 1) = B'1'::bit)
                                                                                                              ->  Index Scan using tof_link_art_la_id_idx on tof_link_art  (cost=0.44..8.39 rows=1 width=4)
                                                                                                                    Index Cond: (la_id = tof_link_la_typ.lat_la_id)
                                                                                                                    Filter: ((la_art_id = 1359418) AND (substring((la_ctm)::bit, 249, 1) = B'1'::bit))
                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8)
                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)
                                                                                                              Filter: (cds_lng_id = 37)
                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41)
                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)
                                                                                            ->  Index Scan using tof_manufacturers_mfa_id_idx on tof_manufacturers  (cost=0.28..6.90 rows=1 width=11)
                                                                                                  Index Cond: (mfa_id = tof_models.mod_mfa_id)
                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41)
                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)
                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10)
                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)
                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41)
                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)
                                                                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10)
                                                                          Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)
                                                              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41)
                                                                    Index Cond: (tex_id = designations2.des_tex_id)
                                                        ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8)
                                                              Index Cond: (lte_typ_id = tof_types.typ_id)
                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12)
                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)
                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10)
                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)
                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41)
                                            Index Cond: (tex_id = designations3.des_tex_id)
                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10)
                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)
                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41)
                                Index Cond: (tex_id = designations4.des_tex_id)
                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10)
                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)
              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41)
                    Index Cond: (tex_id = designations5.des_tex_id)

приведите explain analyze

для медленного -- попробуйте запретить перед выполнением

Код: sql

SET "enable_hashjoin" TO OFF;

...

Рейтинг:

0 / 0

05.11.2014, 18:09:12

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38796719

sabbath66

Гость

explain analyze

для быстрого запроса (Total runtime: 3211.937 ms)

Код: sql

"Sort  (cost=2139.20..2139.20 rows=1 width=315) (actual time=3202.135..3208.528 rows=4954 loops=1)"
"  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
"  Sort Method: external merge  Disk: 672kB"
"  ->  HashAggregate  (cost=2139.17..2139.19 rows=1 width=315) (actual time=3122.165..3125.814 rows=4954 loops=1)"
"        ->  Nested Loop Left Join  (cost=19.06..2139.13 rows=1 width=315) (actual time=3.698..3096.331 rows=7746 loops=1)"
"              ->  Nested Loop Left Join  (cost=18.63..2130.75 rows=1 width=282) (actual time=3.690..3087.626 rows=7746 loops=1)"
"                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
"                    Rows Removed by Filter: 4488"
"                    ->  Nested Loop Left Join  (cost=18.21..2121.62 rows=1 width=282) (actual time=3.684..3077.233 rows=7746 loops=1)"
"                          ->  Nested Loop Left Join  (cost=17.78..2113.24 rows=1 width=249) (actual time=3.679..3069.414 rows=7746 loops=1)"
"                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
"                                Rows Removed by Filter: 4488"
"                                ->  Nested Loop Left Join  (cost=17.36..2104.10 rows=1 width=249) (actual time=3.672..3058.891 rows=7746 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=16.93..2095.73 rows=1 width=216) (actual time=3.608..3029.255 rows=7746 loops=1)"
"                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
"                                            Rows Removed by Filter: 251130"
"                                            ->  Nested Loop Left Join  (cost=16.50..2086.59 rows=1 width=216) (actual time=3.497..2904.597 rows=7746 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=16.22..2078.39 rows=1 width=212) (actual time=3.489..2871.977 rows=7746 loops=1)"
"                                                        ->  Nested Loop Left Join  (cost=15.93..2070.15 rows=1 width=208) (actual time=3.458..2843.024 rows=6154 loops=1)"
"                                                              ->  Nested Loop Left Join  (cost=15.50..2061.77 rows=1 width=175) (actual time=3.446..2821.880 rows=6154 loops=1)"
"                                                                    Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
"                                                                    Rows Removed by Filter: 203016"
"                                                                    ->  Nested Loop Left Join  (cost=15.07..2052.63 rows=1 width=175) (actual time=3.392..2722.860 rows=6154 loops=1)"
"                                                                          ->  Nested Loop Left Join  (cost=14.65..2044.26 rows=1 width=142) (actual time=3.349..2700.522 rows=6154 loops=1)"
"                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
"                                                                                Rows Removed by Filter: 203082"
"                                                                                ->  Nested Loop  (cost=14.22..2035.12 rows=1 width=142) (actual time=3.222..2599.490 rows=6154 loops=1)"
"                                                                                      ->  Nested Loop  (cost=13.79..2026.73 rows=1 width=109) (actual time=2.897..2568.207 rows=6154 loops=1)"
"                                                                                            Join Filter: (tof_models.mod_mfa_id = tof_manufacturers.mfa_id)"
"                                                                                            Rows Removed by Join Filter: 3477010"
"                                                                                            ->  Nested Loop  (cost=13.79..2004.00 rows=1 width=102) (actual time=2.752..1676.075 rows=6154 loops=1)"
"                                                                                                  ->  Nested Loop  (cost=13.37..1995.62 rows=1 width=69) (actual time=2.694..1638.228 rows=6154 loops=1)"
"                                                                                                        ->  Nested Loop  (cost=12.93..1985.83 rows=1 width=69) (actual time=2.592..271.413 rows=6080 loops=1)"
"                                                                                                              ->  Nested Loop  (cost=12.50..1975.78 rows=1 width=69) (actual time=2.435..179.324 rows=4492 loops=1)"
"                                                                                                                    ->  Nested Loop  (cost=12.22..1967.62 rows=1 width=67) (actual time=2.390..160.265 rows=4492 loops=1)"
"                                                                                                                          ->  Nested Loop  (cost=11.92..1959.38 rows=1 width=4) (actual time=0.121..119.546 rows=4686 loops=1)"
"                                                                                                                                ->  Index Scan using tof_link_art_la_art_id_idx on tof_link_art  (cost=0.44..405.35 rows=1 width=4) (actual time=0.069..2.118 ro (...)"
"                                                                                                                                      Index Cond: (la_art_id = 1359385)"
"                                                                                                                                      Filter: ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                ->  Bitmap Heap Scan on tof_link_la_typ  (cost=11.49..1554.01 rows=2 width=8) (actual time=0.046..0.101 rows=4 loops=1120)"
"                                                                                                                                      Recheck Cond: (lat_la_id = tof_link_art.la_id)"
"                                                                                                                                      Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                      ->  Bitmap Index Scan on tof_link_la_typ_lat_la_id_idx  (cost=0.00..11.49 rows=389 width=0) (actual time=0.006..0.006 rows (...)"
"                                                                                                                                            Index Cond: (lat_la_id = tof_link_art.la_id)"
"                                                                                                                          ->  Index Scan using tof_types_typ_id_idx on tof_types  (cost=0.29..8.24 rows=1 width=67) (actual time=0.007..0.007 rows=1 loops=4686)"
"                                                                                                                                Index Cond: (typ_id = tof_link_la_typ.lat_typ_id)"
"                                                                                                                                Filter: ("substring"((typ_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                Rows Removed by Filter: 0"
"                                                                                                                    ->  Index Scan using tof_models_mod_id_idx on tof_models  (cost=0.29..8.15 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=4492)"
"                                                                                                                          Index Cond: (mod_id = tof_types.typ_mod_id)"
"                                                                                                              ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 width=8) (act (...)"
"                                                                                                                    Index Cond: (cds_id = tof_models.mod_cds_id)"
"                                                                                                                    Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                    Rows Removed by Filter: 45"
"                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8) (actual time=0.223..0.224 rows=1  (...)"
"                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)"
"                                                                                                              Filter: (cds_lng_id = 37)"
"                                                                                                              Rows Removed by Filter: 28"
"                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=6154)"
"                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
"                                                                                            ->  Seq Scan on tof_manufacturers  (cost=0.00..15.66 rows=566 width=11) (actual time=0.002..0.068 rows=566 loops=6154)"
"                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=6154)"
"                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)"
"                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10) (actual time=0.003..0.010 rows=34 loops=6154)"
"                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
"                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41) (actual time=0.002..0.003 rows=1 loops=6154)"
"                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)"
"                                                                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10) (actual time=0.003..0.010 rows=34 loops=6154)"
"                                                                          Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
"                                                              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41) (actual time=0.002..0.003 rows=1 loops=6154)"
"                                                                    Index Cond: (tex_id = designations2.des_tex_id)"
"                                                        ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=6154)"
"                                                              Index Cond: (lte_typ_id = tof_types.typ_id)"
"                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=7746)"
"                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
"                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10) (actual time=0.003..0.010 rows=33 loops=7746)"
"                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
"                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=7746)"
"                                            Index Cond: (tex_id = designations3.des_tex_id)"
"                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10) (actual time=0.000..0.000 rows=1 loops=7746)"
"                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
"                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=7746)"
"                                Index Cond: (tex_id = designations4.des_tex_id)"
"                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10) (actual time=0.000..0.000 rows=1 loops=7746)"
"                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
"              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=7746)"
"                    Index Cond: (tex_id = designations5.des_tex_id)"
"Total runtime: 3211.937 ms"

для медленного запроса (Total runtime: 2515331.458 ms)

Код: sql

"Sort  (cost=9617.36..9617.36 rows=1 width=315) (actual time=2515319.947..2515327.117 rows=5143 loops=1)"
"  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
"  Sort Method: external merge  Disk: 704kB"
"  ->  HashAggregate  (cost=9617.34..9617.35 rows=1 width=315) (actual time=2515232.253..2515235.955 rows=5143 loops=1)"
"        ->  Nested Loop Left Join  (cost=2770.70..9617.29 rows=1 width=315) (actual time=56400.776..2515161.491 rows=7217 loops=1)"
"              ->  Nested Loop Left Join  (cost=2770.27..9608.91 rows=1 width=282) (actual time=56400.772..2515140.997 rows=7217 loops=1)"
"                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
"                    Rows Removed by Filter: 4653"
"                    ->  Nested Loop Left Join  (cost=2769.85..9599.78 rows=1 width=282) (actual time=56400.768..2515115.229 rows=7217 loops=1)"
"                          ->  Nested Loop Left Join  (cost=2769.42..9591.40 rows=1 width=249) (actual time=56400.765..2515095.557 rows=7217 loops=1)"
"                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
"                                Rows Removed by Filter: 4653"
"                                ->  Nested Loop Left Join  (cost=2769.00..9582.26 rows=1 width=249) (actual time=56400.754..2515066.013 rows=7217 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=2768.57..9573.89 rows=1 width=216) (actual time=56400.222..2514990.633 rows=7217 loops=1)"
"                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
"                                            Rows Removed by Filter: 233508"
"                                            ->  Nested Loop Left Join  (cost=2768.14..9564.75 rows=1 width=216) (actual time=56400.167..2514811.970 rows=7217 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=2767.86..9556.55 rows=1 width=212) (actual time=56400.138..2512323.507 rows=7217 loops=1)"
"                                                        ->  Nested Loop Left Join  (cost=2767.57..9548.31 rows=1 width=208) (actual time=56400.110..2510200.349 rows=5682 loops=1)"
"                                                              ->  Nested Loop Left Join  (cost=2767.14..9539.93 rows=1 width=175) (actual time=56400.091..2510154.587 rows=5682 loops=1)"
"                                                                    Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
"                                                                    Rows Removed by Filter: 187440"
"                                                                    ->  Nested Loop Left Join  (cost=2766.71..9530.79 rows=1 width=175) (actual time=56400.037..2510010.039 rows=5682 loops=1)"
"                                                                          ->  Nested Loop Left Join  (cost=2766.29..9522.42 rows=1 width=142) (actual time=56400.011..2509951.582 rows=5682 loops=1)"
"                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
"                                                                                Rows Removed by Filter: 187506"
"                                                                                ->  Nested Loop  (cost=2765.86..9513.28 rows=1 width=142) (actual time=56399.955..2509792.104 rows=5682 loops=1)"
"                                                                                      ->  Nested Loop  (cost=2765.43..9504.89 rows=1 width=109) (actual time=56399.924..2509111.755 rows=5682 loops=1)"
"                                                                                            ->  Nested Loop  (cost=2765.16..9497.99 rows=1 width=102) (actual time=56399.876..2509047.978 rows=5682 loops=1)"
"                                                                                                  ->  Nested Loop  (cost=2764.73..9489.61 rows=1 width=69) (actual time=56399.837..2507992.854 rows=5682 loops=1)"
"                                                                                                        ->  Nested Loop  (cost=2764.30..9479.82 rows=1 width=69) (actual time=56399.785..2502911.294 rows=5607 loops=1)"
"                                                                                                              ->  Nested Loop  (cost=2763.86..9211.04 rows=32 width=73) (actual time=123.243..200571.128 rows=148270092 loops=1)"
"                                                                                                                    ->  Nested Loop  (cost=2763.29..6856.14 rows=4 width=69) (actual time=122.372..2548.661 rows=53228 loops=1)"
"                                                                                                                          ->  Merge Join  (cost=2762.86..3270.47 rows=357 width=69) (actual time=122.297..950.369 rows=47531 loops=1)"
"                                                                                                                                Merge Cond: (tof_models.mod_id = tof_types.typ_mod_id)"
"                                                                                                                                ->  Index Scan using tof_models_mod_id_idx on tof_models  (cost=0.29..475.22 rows=10929 width=10) (actual time=0.016..19.778 row (...)"
"                                                                                                                                ->  Sort  (cost=2762.58..2763.47 rows=357 width=67) (actual time=122.271..768.263 rows=47531 loops=1)"
"                                                                                                                                      Sort Key: tof_types.typ_mod_id"
"                                                                                                                                      Sort Method: external sort  Disk: 3304kB"
"                                                                                                                                      ->  Seq Scan on tof_types  (cost=0.00..2747.44 rows=357 width=67) (actual time=0.010..51.546 rows=47531 loops=1)"
"                                                                                                                                            Filter: ("substring"((typ_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                            Rows Removed by Filter: 23965"
"                                                                                                                          ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 w (...)"
"                                                                                                                                Index Cond: (cds_id = tof_models.mod_cds_id)"
"                                                                                                                                Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                                Rows Removed by Filter: 36"
"                                                                                                                    ->  Index Scan using tof_link_la_typ_lat_typ_id_idx on tof_link_la_typ  (cost=0.57..588.35 rows=38 width=8) (actual time=0.791..2.937 rows=2 (...)"
"                                                                                                                          Index Cond: (lat_typ_id = tof_types.typ_id)"
"                                                                                                                          Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                          Rows Removed by Filter: 45"
"                                                                                                              ->  Index Scan using tof_link_art_la_id_idx on tof_link_art  (cost=0.44..8.39 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=148270092)"
"                                                                                                                    Index Cond: (la_id = tof_link_la_typ.lat_la_id)"
"                                                                                                                    Filter: ((la_art_id = 1359418) AND ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                    Rows Removed by Filter: 1"
"                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8) (actual time=0.899..0.903 rows=1  (...)"
"                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)"
"                                                                                                              Filter: (cds_lng_id = 37)"
"                                                                                                              Rows Removed by Filter: 28"
"                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41) (actual time=0.181..0.182 rows=1 loops=5682)"
"                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
"                                                                                            ->  Index Scan using tof_manufacturers_mfa_id_idx on tof_manufacturers  (cost=0.28..6.90 rows=1 width=11) (actual time=0.007..0.008 rows=1 loops=5682)"
"                                                                                                  Index Cond: (mfa_id = tof_models.mod_mfa_id)"
"                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41) (actual time=0.116..0.117 rows=1 loops=5682)"
"                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)"
"                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10) (actual time=0.010..0.018 rows=34 loops=5682)"
"                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
"                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41) (actual time=0.007..0.008 rows=1 loops=5682)"
"                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)"
"                                                                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10) (actual time=0.008..0.016 rows=34 loops=5682)"
"                                                                          Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
"                                                              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41) (actual time=0.005..0.006 rows=1 loops=5682)"
"                                                                    Index Cond: (tex_id = designations2.des_tex_id)"
"                                                        ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8) (actual time=0.370..0.371 rows=1 loops=5682)"
"                                                              Index Cond: (lte_typ_id = tof_types.typ_id)"
"                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12) (actual time=0.342..0.342 rows=1 loops=7217)"
"                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
"                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10) (actual time=0.008..0.016 rows=33 loops=7217)"
"                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
"                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41) (actual time=0.008..0.008 rows=1 loops=7217)"
"                                            Index Cond: (tex_id = designations3.des_tex_id)"
"                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10) (actual time=0.001..0.001 rows=1 loops=7217)"
"                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
"                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=7217)"
"                                Index Cond: (tex_id = designations4.des_tex_id)"
"                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10) (actual time=0.001..0.001 rows=1 loops=7217)"
"                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
"              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=7217)"
"                    Index Cond: (tex_id = designations5.des_tex_id)"
"Total runtime: 2515331.458 ms"

...

Рейтинг:

0 / 0

05.11.2014, 20:00:27

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38796730

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

sabbath66,

Код: sql

create index on tof_types using btree(typ_id) where SUBSTRING(typ_ctm FROM 249 FOR 1)='1';

такой индекс должен помочь планнеру выбрать более оптимальный план, а не бегать в цикле 150М раз.

...

Рейтинг:

0 / 0

05.11.2014, 20:31:36

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38797002

sabbath66

Гость

Спасибо, но увы, не помогло :( ...

...

Рейтинг:

0 / 0

06.11.2014, 10:08:49

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38797016

планы где

Гость

sabbath66Спасибо, но увы, не помогло :( ...а план где ?

да, приведите план analyze и без SET enable-xxx TO OFF; (т.е. после RESET ALL;)

И как -то расставьте везде в связках и т.п. табличные префиксы (алиасы), -- лениво, знаете ли, за вас высчитывать "кто на ком стоял".

(есть идея, что там у вас агрегируется всего одно поле, и можно все прочее прикрутить после агрегирования, а не напрягать планер попусту вариантами.
но читать вашу кашу бесконечно лениво).

если условие 47 (или 73) -- константное -- ещё и условные индесы по нему могут сгодиццо, а где-то еше и по смеси 37 и декомпозиций типа AND SUBSTRING(la_ctm FROM 249 FOR 1)='1' .

...

Рейтинг:

0 / 0

06.11.2014, 10:19:23

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38797072

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

sabbath66,

странно. если сильно не вникать в запрос, можно еще посоветовать выкрутить для запроса:

Код: sql

1.
2.

set join_collapse_limit = 16;
set work_mem = '32MB';

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

...

Рейтинг:

0 / 0

06.11.2014, 11:15:44

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38797450

sabbath66

Гость

Вот, вроде все расставил...

Запрос:

Код: 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.
36.
37.
38.
39.
40.
41.
42.
43.
44.

SELECT	TYP_ID,	MFA_BRAND,	DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT,	
TOF_DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT,	TYP_PCON_START,	TYP_PCON_END,	
TYP_CCM,	TYP_KW_FROM,	TYP_KW_UPTO,	TYP_HP_FROM,	TYP_HP_UPTO,	
TYP_CYLINDERS,	string_agg(TOF_ENGINES.ENG_CODE, ', '),	
DES_TEXTS2.TEX_TEXT AS TYP_ENGINE_DES_TEXT, 
DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT, 
CASE WHEN DES_TEXTS4.TEX_TEXT IS NULL THEN DES_TEXTS5.TEX_TEXT ELSE DES_TEXTS4.TEX_TEXT END AS TYP_BODY_DES_TEXT, 
DES_TEXTS6.TEX_TEXT AS TYP_AXLE_DES_TEXT, TYP_MAX_WEIGHT 

FROM TOF_LINK_ART 

INNER JOIN TOF_LINK_LA_TYP ON TOF_LINK_LA_TYP.LAT_LA_ID = TOF_LINK_ART.LA_ID AND SUBSTRING(TOF_LINK_LA_TYP.lat_ctm FROM 249 FOR 1)='1'
INNER JOIN TOF_TYPES ON TOF_TYPES.TYP_ID = TOF_LINK_LA_TYP.LAT_TYP_ID AND SUBSTRING(TOF_TYPES.typ_ctm FROM 249 FOR 1)='1' 
INNER JOIN TOF_COUNTRY_DESIGNATIONS ON TOF_COUNTRY_DESIGNATIONS.CDS_ID = TOF_TYPES.TYP_CDS_ID 
INNER JOIN TOF_DES_TEXTS ON TOF_DES_TEXTS.TEX_ID = TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID 
INNER JOIN TOF_MODELS ON TOF_MODELS.MOD_ID = TOF_TYPES.TYP_MOD_ID 
INNER JOIN TOF_MANUFACTURERS ON TOF_MANUFACTURERS.MFA_ID = TOF_MODELS.MOD_MFA_ID 
INNER JOIN TOF_COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON COUNTRY_DESIGNATIONS2.CDS_ID = TOF_MODELS.MOD_CDS_ID AND SUBSTRING(COUNTRY_DESIGNATIONS2.cds_ctm FROM 249 FOR 1)='1' 
INNER JOIN TOF_DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID = COUNTRY_DESIGNATIONS2.CDS_TEX_ID 
LEFT JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID = TOF_TYPES.TYP_KV_ENGINE_DES_ID 
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = TOF_DESIGNATIONS.DES_TEX_ID 
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = TOF_TYPES.TYP_KV_FUEL_DES_ID 
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID 
LEFT JOIN TOF_LINK_TYP_ENG ON TOF_LINK_TYP_ENG.LTE_TYP_ID = TOF_TYPES.TYP_ID 
LEFT JOIN TOF_ENGINES ON TOF_ENGINES.ENG_ID = TOF_LINK_TYP_ENG.LTE_ENG_ID 
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = TOF_TYPES.TYP_KV_BODY_DES_ID 
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID 
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = TOF_TYPES.TYP_KV_MODEL_DES_ID 
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID 
LEFT JOIN TOF_DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = TOF_TYPES.TYP_KV_AXLE_DES_ID 
LEFT JOIN TOF_DES_TEXTS AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID 

WHERE	LA_ART_ID = 1359385 

AND	TOF_COUNTRY_DESIGNATIONS.CDS_LNG_ID = 37 
AND	COUNTRY_DESIGNATIONS2.CDS_LNG_ID = 37 
AND (TOF_DESIGNATIONS.DES_LNG_ID IS NULL OR TOF_DESIGNATIONS.DES_LNG_ID = 37) 
AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = 37) 
AND (DESIGNATIONS3.DES_LNG_ID IS NULL OR DESIGNATIONS3.DES_LNG_ID = 37) 
AND (DESIGNATIONS4.DES_LNG_ID IS NULL OR DESIGNATIONS4.DES_LNG_ID = 37) 
AND (DESIGNATIONS5.DES_LNG_ID IS NULL OR DESIGNATIONS5.DES_LNG_ID = 37) 
AND SUBSTRING(TOF_LINK_ART.la_ctm FROM 249 FOR 1)='1' 

GROUP BY tof_types.typ_id, tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, TYP_PCON_START, TYP_PCON_END, TYP_CCM,	TYP_KW_FROM,	TYP_KW_UPTO, TYP_HP_FROM,	TYP_HP_UPTO,	TYP_CYLINDERS , des_texts2.tex_text, des_texts3.tex_text, des_texts4.tex_text, des_texts5.tex_text, des_texts6.tex_text ORDER BY	MFA_BRAND, MOD_CDS_TEXT,	TYP_CDS_TEXT,	TYP_PCON_START,	TYP_CCM

план - быстрый - reset all

Код: sql

"Sort  (cost=2139.17..2139.18 rows=1 width=315) (actual time=5527.235..5533.606 rows=4954 loops=1)"
"  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
"  Sort Method: external merge  Disk: 672kB"
"  ->  HashAggregate  (cost=2139.15..2139.16 rows=1 width=315) (actual time=5447.917..5451.501 rows=4954 loops=1)"
"        ->  Nested Loop Left Join  (cost=19.06..2139.11 rows=1 width=315) (actual time=3.523..5405.388 rows=7746 loops=1)"
"              ->  Nested Loop Left Join  (cost=18.63..2130.73 rows=1 width=282) (actual time=3.519..5394.243 rows=7746 loops=1)"
"                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
"                    Rows Removed by Filter: 4488"
"                    ->  Nested Loop Left Join  (cost=18.21..2121.59 rows=1 width=282) (actual time=3.516..5380.331 rows=7746 loops=1)"
"                          ->  Nested Loop Left Join  (cost=17.78..2113.22 rows=1 width=249) (actual time=3.514..5369.179 rows=7746 loops=1)"
"                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
"                                Rows Removed by Filter: 4488"
"                                ->  Nested Loop Left Join  (cost=17.35..2104.08 rows=1 width=249) (actual time=3.511..5355.105 rows=7746 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=16.93..2095.70 rows=1 width=216) (actual time=3.495..5315.943 rows=7746 loops=1)"
"                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
"                                            Rows Removed by Filter: 251130"
"                                            ->  Nested Loop Left Join  (cost=16.50..2086.56 rows=1 width=216) (actual time=3.475..5171.779 rows=7746 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=16.21..2078.36 rows=1 width=212) (actual time=3.473..5119.469 rows=7746 loops=1)"
"                                                        ->  Nested Loop Left Join  (cost=15.79..2069.99 rows=1 width=179) (actual time=3.468..5085.841 rows=7746 loops=1)"
"                                                              Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
"                                                              Rows Removed by Filter: 255552"
"                                                              ->  Nested Loop Left Join  (cost=15.36..2060.85 rows=1 width=179) (actual time=3.452..4942.330 rows=7746 loops=1)"
"                                                                    ->  Nested Loop Left Join  (cost=15.07..2052.61 rows=1 width=175) (actual time=3.444..4896.245 rows=6154 loops=1)"
"                                                                          ->  Nested Loop Left Join  (cost=14.64..2044.23 rows=1 width=142) (actual time=3.436..4863.874 rows=6154 loops=1)"
"                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
"                                                                                Rows Removed by Filter: 203082"
"                                                                                ->  Nested Loop  (cost=14.22..2035.10 rows=1 width=142) (actual time=3.412..4740.554 rows=6154 loops=1)"
"                                                                                      ->  Nested Loop  (cost=13.79..2026.71 rows=1 width=109) (actual time=3.391..4671.321 rows=6154 loops=1)"
"                                                                                            Join Filter: (tof_models.mod_mfa_id = tof_manufacturers.mfa_id)"
"                                                                                            Rows Removed by Join Filter: 3477010"
"                                                                                            ->  Nested Loop  (cost=13.79..2003.97 rows=1 width=102) (actual time=3.348..3653.082 rows=6154 loops=1)"
"                                                                                                  ->  Nested Loop  (cost=13.36..1995.60 rows=1 width=69) (actual time=3.328..3595.294 rows=6154 loops=1)"
"                                                                                                        ->  Nested Loop  (cost=12.93..1985.80 rows=1 width=69) (actual time=2.862..2231.235 rows=6080 loops=1)"
"                                                                                                              ->  Nested Loop  (cost=12.50..1975.76 rows=1 width=69) (actual time=2.518..2004.939 rows=4492 loops=1)"
"                                                                                                                    ->  Nested Loop  (cost=12.21..1967.60 rows=1 width=67) (actual time=2.488..1970.196 rows=4492 loops=1)"
"                                                                                                                          ->  Nested Loop  (cost=11.92..1959.38 rows=1 width=4) (actual time=1.287..400.332 rows=4686 loops=1)"
"                                                                                                                                ->  Index Scan using tof_link_art_la_art_id_idx on tof_link_art  (cost=0.44..405.35 rows=1 width=4) (actual time=0.517..4.395 ro (...)"
"                                                                                                                                      Index Cond: (la_art_id = 1359385)"
"                                                                                                                                      Filter: ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                ->  Bitmap Heap Scan on tof_link_la_typ  (cost=11.49..1554.01 rows=2 width=8) (actual time=0.181..0.346 rows=4 loops=1120)"
"                                                                                                                                      Recheck Cond: (lat_la_id = tof_link_art.la_id)"
"                                                                                                                                      Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                      ->  Bitmap Index Scan on tof_link_la_typ_lat_la_id_idx  (cost=0.00..11.49 rows=389 width=0) (actual time=0.011..0.011 rows (...)"
"                                                                                                                                            Index Cond: (lat_la_id = tof_link_art.la_id)"
"                                                                                                                          ->  Index Scan using tof_types_typ_id249_idx on tof_types  (cost=0.29..8.21 rows=1 width=67) (actual time=0.331..0.332 rows=1 loops=46 (...)"
"                                                                                                                                Index Cond: (typ_id = tof_link_la_typ.lat_typ_id)"
"                                                                                                                    ->  Index Scan using tof_models_mod_id_idx on tof_models  (cost=0.29..8.15 rows=1 width=10) (actual time=0.005..0.006 rows=1 loops=4492)"
"                                                                                                                          Index Cond: (mod_id = tof_types.typ_mod_id)"
"                                                                                                              ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 width=8) (act (...)"
"                                                                                                                    Index Cond: (cds_id = tof_models.mod_cds_id)"
"                                                                                                                    Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                    Rows Removed by Filter: 45"
"                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8) (actual time=0.220..0.223 rows=1  (...)"
"                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)"
"                                                                                                              Filter: (cds_lng_id = 37)"
"                                                                                                              Rows Removed by Filter: 28"
"                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=6154)"
"                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
"                                                                                            ->  Seq Scan on tof_manufacturers  (cost=0.00..15.66 rows=566 width=11) (actual time=0.003..0.082 rows=566 loops=6154)"
"                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41) (actual time=0.009..0.010 rows=1 loops=6154)"
"                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)"
"                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10) (actual time=0.005..0.012 rows=34 loops=6154)"
"                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
"                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41) (actual time=0.003..0.004 rows=1 loops=6154)"
"                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)"
"                                                                    ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=6154)"
"                                                                          Index Cond: (lte_typ_id = tof_types.typ_id)"
"                                                              ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10) (actual time=0.004..0.011 rows=34 loops=7746)"
"                                                                    Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
"                                                        ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=7746)"
"                                                              Index Cond: (tex_id = designations2.des_tex_id)"
"                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=7746)"
"                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
"                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10) (actual time=0.004..0.011 rows=33 loops=7746)"
"                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
"                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41) (actual time=0.003..0.004 rows=1 loops=7746)"
"                                            Index Cond: (tex_id = designations3.des_tex_id)"
"                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10) (actual time=0.000..0.000 rows=1 loops=7746)"
"                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
"                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=7746)"
"                                Index Cond: (tex_id = designations4.des_tex_id)"
"                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10) (actual time=0.000..0.000 rows=1 loops=7746)"
"                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
"              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=7746)"
"                    Index Cond: (tex_id = designations5.des_tex_id)"
"Total runtime: 5535.936 ms"

план - быстрый - hashjoin off

Код: sql

"Sort  (cost=2139.17..2139.18 rows=1 width=315) (actual time=1749.217..1755.486 rows=4954 loops=1)"
"  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
"  Sort Method: external merge  Disk: 672kB"
"  ->  HashAggregate  (cost=2139.15..2139.16 rows=1 width=315) (actual time=1667.610..1671.258 rows=4954 loops=1)"
"        ->  Nested Loop Left Join  (cost=19.06..2139.11 rows=1 width=315) (actual time=0.350..1647.043 rows=7746 loops=1)"
"              ->  Nested Loop Left Join  (cost=18.63..2130.73 rows=1 width=282) (actual time=0.349..1640.651 rows=7746 loops=1)"
"                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
"                    Rows Removed by Filter: 4488"
"                    ->  Nested Loop Left Join  (cost=18.21..2121.59 rows=1 width=282) (actual time=0.347..1631.989 rows=7746 loops=1)"
"                          ->  Nested Loop Left Join  (cost=17.78..2113.22 rows=1 width=249) (actual time=0.346..1625.592 rows=7746 loops=1)"
"                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
"                                Rows Removed by Filter: 4488"
"                                ->  Nested Loop Left Join  (cost=17.35..2104.08 rows=1 width=249) (actual time=0.344..1616.541 rows=7746 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=16.93..2095.70 rows=1 width=216) (actual time=0.338..1590.598 rows=7746 loops=1)"
"                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
"                                            Rows Removed by Filter: 251130"
"                                            ->  Nested Loop Left Join  (cost=16.50..2086.56 rows=1 width=216) (actual time=0.319..1472.249 rows=7746 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=16.21..2078.36 rows=1 width=212) (actual time=0.318..1445.136 rows=7746 loops=1)"
"                                                        ->  Nested Loop Left Join  (cost=15.79..2069.99 rows=1 width=179) (actual time=0.314..1421.418 rows=7746 loops=1)"
"                                                              Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
"                                                              Rows Removed by Filter: 255552"
"                                                              ->  Nested Loop Left Join  (cost=15.36..2060.85 rows=1 width=179) (actual time=0.301..1301.918 rows=7746 loops=1)"
"                                                                    ->  Nested Loop Left Join  (cost=15.07..2052.61 rows=1 width=175) (actual time=0.294..1279.493 rows=6154 loops=1)"
"                                                                          ->  Nested Loop Left Join  (cost=14.64..2044.23 rows=1 width=142) (actual time=0.288..1260.228 rows=6154 loops=1)"
"                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
"                                                                                Rows Removed by Filter: 203082"
"                                                                                ->  Nested Loop  (cost=14.22..2035.10 rows=1 width=142) (actual time=0.266..1165.231 rows=6154 loops=1)"
"                                                                                      ->  Nested Loop  (cost=13.79..2026.71 rows=1 width=109) (actual time=0.260..1141.968 rows=6154 loops=1)"
"                                                                                            Join Filter: (tof_models.mod_mfa_id = tof_manufacturers.mfa_id)"
"                                                                                            Rows Removed by Join Filter: 3477010"
"                                                                                            ->  Nested Loop  (cost=13.79..2003.97 rows=1 width=102) (actual time=0.222..257.127 rows=6154 loops=1)"
"                                                                                                  ->  Nested Loop  (cost=13.36..1995.60 rows=1 width=69) (actual time=0.213..232.934 rows=6154 loops=1)"
"                                                                                                        ->  Nested Loop  (cost=12.93..1985.80 rows=1 width=69) (actual time=0.186..154.096 rows=6080 loops=1)"
"                                                                                                              ->  Nested Loop  (cost=12.50..1975.76 rows=1 width=69) (actual time=0.155..82.574 rows=4492 loops=1)"
"                                                                                                                    ->  Nested Loop  (cost=12.21..1967.60 rows=1 width=67) (actual time=0.148..67.469 rows=4492 loops=1)"
"                                                                                                                          ->  Nested Loop  (cost=11.92..1959.38 rows=1 width=4) (actual time=0.110..44.185 rows=4686 loops=1)"
"                                                                                                                                ->  Index Scan using tof_link_art_la_art_id_idx on tof_link_art  (cost=0.44..405.35 rows=1 width=4) (actual time=0.079..1.375 ro (...)"
"                                                                                                                                      Index Cond: (la_art_id = 1359385)"
"                                                                                                                                      Filter: ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                ->  Bitmap Heap Scan on tof_link_la_typ  (cost=11.49..1554.01 rows=2 width=8) (actual time=0.013..0.035 rows=4 loops=1120)"
"                                                                                                                                      Recheck Cond: (lat_la_id = tof_link_art.la_id)"
"                                                                                                                                      Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                                      ->  Bitmap Index Scan on tof_link_la_typ_lat_la_id_idx  (cost=0.00..11.49 rows=389 width=0) (actual time=0.004..0.004 rows (...)"
"                                                                                                                                            Index Cond: (lat_la_id = tof_link_art.la_id)"
"                                                                                                                          ->  Index Scan using tof_types_typ_id249_idx on tof_types  (cost=0.29..8.21 rows=1 width=67) (actual time=0.003..0.004 rows=1 loops=46 (...)"
"                                                                                                                                Index Cond: (typ_id = tof_link_la_typ.lat_typ_id)"
"                                                                                                                    ->  Index Scan using tof_models_mod_id_idx on tof_models  (cost=0.29..8.15 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=4492)"
"                                                                                                                          Index Cond: (mod_id = tof_types.typ_mod_id)"
"                                                                                                              ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 width=8) (act (...)"
"                                                                                                                    Index Cond: (cds_id = tof_models.mod_cds_id)"
"                                                                                                                    Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                    Rows Removed by Filter: 45"
"                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8) (actual time=0.011..0.012 rows=1  (...)"
"                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)"
"                                                                                                              Filter: (cds_lng_id = 37)"
"                                                                                                              Rows Removed by Filter: 28"
"                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=6154)"
"                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
"                                                                                            ->  Seq Scan on tof_manufacturers  (cost=0.00..15.66 rows=566 width=11) (actual time=0.001..0.068 rows=566 loops=6154)"
"                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=6154)"
"                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)"
"                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10) (actual time=0.003..0.009 rows=34 loops=6154)"
"                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
"                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=6154)"
"                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)"
"                                                                    ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=6154)"
"                                                                          Index Cond: (lte_typ_id = tof_types.typ_id)"
"                                                              ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10) (actual time=0.003..0.009 rows=34 loops=7746)"
"                                                                    Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
"                                                        ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=7746)"
"                                                              Index Cond: (tex_id = designations2.des_tex_id)"
"                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=7746)"
"                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
"                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10) (actual time=0.003..0.009 rows=33 loops=7746)"
"                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
"                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41) (actual time=0.002..0.003 rows=1 loops=7746)"
"                                            Index Cond: (tex_id = designations3.des_tex_id)"
"                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10) (actual time=0.000..0.000 rows=1 loops=7746)"
"                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
"                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=7746)"
"                                Index Cond: (tex_id = designations4.des_tex_id)"
"                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10) (actual time=0.000..0.000 rows=1 loops=7746)"
"                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
"              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=7746)"
"                    Index Cond: (tex_id = designations5.des_tex_id)"
"Total runtime: 1757.845 ms"

план - медленный - reset all

Код: sql

"Sort  (cost=6802.91..6802.92 rows=1 width=315) (actual time=1515801.861..1515808.933 rows=5143 loops=1)"
"  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
"  Sort Method: external merge  Disk: 704kB"
"  ->  HashAggregate  (cost=6802.89..6802.90 rows=1 width=315) (actual time=1515714.127..1515717.974 rows=5143 loops=1)"
"        ->  Nested Loop Left Join  (cost=434.03..6802.84 rows=1 width=315) (actual time=8875.764..1515641.176 rows=7217 loops=1)"
"              ->  Nested Loop Left Join  (cost=433.61..6794.47 rows=1 width=282) (actual time=8875.761..1515620.567 rows=7217 loops=1)"
"                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
"                    Rows Removed by Filter: 4653"
"                    ->  Nested Loop Left Join  (cost=433.18..6785.33 rows=1 width=282) (actual time=8875.756..1515595.210 rows=7217 loops=1)"
"                          ->  Nested Loop Left Join  (cost=432.75..6776.95 rows=1 width=249) (actual time=8875.753..1515576.693 rows=7217 loops=1)"
"                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
"                                Rows Removed by Filter: 4653"
"                                ->  Nested Loop Left Join  (cost=432.33..6767.82 rows=1 width=249) (actual time=8875.749..1515551.473 rows=7217 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=431.90..6759.44 rows=1 width=216) (actual time=8875.724..1515475.270 rows=7217 loops=1)"
"                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
"                                            Rows Removed by Filter: 233508"
"                                            ->  Nested Loop Left Join  (cost=431.48..6750.30 rows=1 width=216) (actual time=8875.677..1515289.474 rows=7217 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=431.19..6742.10 rows=1 width=212) (actual time=8875.653..1513162.508 rows=7217 loops=1)"
"                                                        ->  Nested Loop Left Join  (cost=430.90..6733.86 rows=1 width=208) (actual time=8875.642..1513084.259 rows=5682 loops=1)"
"                                                              ->  Nested Loop Left Join  (cost=430.47..6725.49 rows=1 width=175) (actual time=8875.623..1513034.014 rows=5682 loops=1)"
"                                                                    Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
"                                                                    Rows Removed by Filter: 187440"
"                                                                    ->  Nested Loop Left Join  (cost=430.05..6716.35 rows=1 width=175) (actual time=8875.586..1512888.727 rows=5682 loops=1)"
"                                                                          ->  Nested Loop Left Join  (cost=429.62..6707.97 rows=1 width=142) (actual time=8875.554..1512826.204 rows=5682 loops=1)"
"                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
"                                                                                Rows Removed by Filter: 187506"
"                                                                                ->  Nested Loop  (cost=429.19..6698.83 rows=1 width=142) (actual time=8875.484..1512661.694 rows=5682 loops=1)"
"                                                                                      ->  Nested Loop  (cost=428.77..6690.45 rows=1 width=109) (actual time=8874.993..1510657.172 rows=5682 loops=1)"
"                                                                                            ->  Nested Loop  (cost=428.49..6683.54 rows=1 width=102) (actual time=8874.975..1510586.098 rows=5682 loops=1)"
"                                                                                                  ->  Nested Loop  (cost=428.06..6675.16 rows=1 width=69) (actual time=8874.921..1508573.962 rows=5682 loops=1)"
"                                                                                                        ->  Nested Loop  (cost=427.63..6665.37 rows=1 width=69) (actual time=8874.357..1507231.694 rows=5607 loops=1)"
"                                                                                                              ->  Nested Loop  (cost=427.19..6396.59 rows=32 width=73) (actual time=7.791..153103.451 rows=148270092 loops=1)"
"                                                                                                                    ->  Nested Loop  (cost=426.63..4041.69 rows=4 width=69) (actual time=7.756..9224.102 rows=53228 loops=1)"
"                                                                                                                          ->  Hash Join  (cost=426.19..456.03 rows=357 width=69) (actual time=7.301..351.346 rows=47531 loops=1)"
"                                                                                                                                Hash Cond: (tof_types.typ_mod_id = tof_models.mod_id)"
"                                                                                                                                ->  Index Scan using tof_types_typ_id249_idx on tof_types  (cost=0.29..23.43 rows=357 width=67) (actual time=0.018..99.348 rows= (...)"
"                                                                                                                                ->  Hash  (cost=289.29..289.29 rows=10929 width=10) (actual time=7.266..7.266 rows=10929 loops=1)"
"                                                                                                                                      Buckets: 2048  Batches: 1  Memory Usage: 470kB"
"                                                                                                                                      ->  Seq Scan on tof_models  (cost=0.00..289.29 rows=10929 width=10) (actual time=0.006..4.448 rows=10929 loops=1)"
"                                                                                                                          ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 w (...)"
"                                                                                                                                Index Cond: (cds_id = tof_models.mod_cds_id)"
"                                                                                                                                Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                                Rows Removed by Filter: 36"
"                                                                                                                    ->  Index Scan using tof_link_la_typ_lat_typ_id_idx on tof_link_la_typ  (cost=0.57..588.35 rows=38 width=8) (actual time=0.076..1.944 rows=2 (...)"
"                                                                                                                          Index Cond: (lat_typ_id = tof_types.typ_id)"
"                                                                                                                          Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                          Rows Removed by Filter: 45"
"                                                                                                              ->  Index Scan using tof_link_art_la_id_idx on tof_link_art  (cost=0.44..8.39 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=148270092)"
"                                                                                                                    Index Cond: (la_id = tof_link_la_typ.lat_la_id)"
"                                                                                                                    Filter: ((la_art_id = 1359418) AND ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                    Rows Removed by Filter: 1"
"                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8) (actual time=0.233..0.236 rows=1  (...)"
"                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)"
"                                                                                                              Filter: (cds_lng_id = 37)"
"                                                                                                              Rows Removed by Filter: 28"
"                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41) (actual time=0.350..0.351 rows=1 loops=5682)"
"                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
"                                                                                            ->  Index Scan using tof_manufacturers_mfa_id_idx on tof_manufacturers  (cost=0.28..6.90 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=5682)"
"                                                                                                  Index Cond: (mfa_id = tof_models.mod_mfa_id)"
"                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41) (actual time=0.350..0.350 rows=1 loops=5682)"
"                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)"
"                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10) (actual time=0.011..0.019 rows=34 loops=5682)"
"                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
"                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41) (actual time=0.008..0.009 rows=1 loops=5682)"
"                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)"
"                                                                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10) (actual time=0.008..0.017 rows=34 loops=5682)"
"                                                                          Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
"                                                              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=5682)"
"                                                                    Index Cond: (tex_id = designations2.des_tex_id)"
"                                                        ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=5682)"
"                                                              Index Cond: (lte_typ_id = tof_types.typ_id)"
"                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12) (actual time=0.292..0.292 rows=1 loops=7217)"
"                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
"                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10) (actual time=0.010..0.017 rows=33 loops=7217)"
"                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
"                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41) (actual time=0.008..0.008 rows=1 loops=7217)"
"                                            Index Cond: (tex_id = designations3.des_tex_id)"
"                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10) (actual time=0.001..0.001 rows=1 loops=7217)"
"                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
"                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=7217)"
"                                Index Cond: (tex_id = designations4.des_tex_id)"
"                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10) (actual time=0.001..0.001 rows=1 loops=7217)"
"                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
"              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=7217)"
"                    Index Cond: (tex_id = designations5.des_tex_id)"
"Total runtime: 1515812.246 ms"

план - медленный - hashjoin off

Код: sql

"Sort  (cost=6893.35..6893.35 rows=1 width=315) (actual time=1984001.322..1984008.582 rows=5143 loops=1)"
"  Sort Key: tof_manufacturers.mfa_brand, des_texts7.tex_text, tof_des_texts.tex_text, tof_types.typ_pcon_start, tof_types.typ_ccm"
"  Sort Method: external merge  Disk: 704kB"
"  ->  HashAggregate  (cost=6893.33..6893.34 rows=1 width=315) (actual time=1983915.546..1983919.349 rows=5143 loops=1)"
"        ->  Nested Loop Left Join  (cost=46.69..6893.28 rows=1 width=315) (actual time=30677.904..1983842.659 rows=7217 loops=1)"
"              ->  Nested Loop Left Join  (cost=46.26..6884.90 rows=1 width=282) (actual time=30677.899..1983818.462 rows=7217 loops=1)"
"                    Filter: ((designations5.des_lng_id IS NULL) OR (designations5.des_lng_id = 37))"
"                    Rows Removed by Filter: 4653"
"                    ->  Nested Loop Left Join  (cost=45.84..6875.77 rows=1 width=282) (actual time=30677.894..1983794.018 rows=7217 loops=1)"
"                          ->  Nested Loop Left Join  (cost=45.41..6867.39 rows=1 width=249) (actual time=30677.889..1983773.815 rows=7217 loops=1)"
"                                Filter: ((designations4.des_lng_id IS NULL) OR (designations4.des_lng_id = 37))"
"                                Rows Removed by Filter: 4653"
"                                ->  Nested Loop Left Join  (cost=44.99..6858.25 rows=1 width=249) (actual time=30677.882..1983746.966 rows=7217 loops=1)"
"                                      ->  Nested Loop Left Join  (cost=44.56..6849.88 rows=1 width=216) (actual time=30677.843..1983681.855 rows=7217 loops=1)"
"                                            Filter: ((designations3.des_lng_id IS NULL) OR (designations3.des_lng_id = 37))"
"                                            Rows Removed by Filter: 233508"
"                                            ->  Nested Loop Left Join  (cost=44.13..6840.74 rows=1 width=216) (actual time=30677.743..1983511.102 rows=7217 loops=1)"
"                                                  ->  Nested Loop Left Join  (cost=43.85..6832.54 rows=1 width=212) (actual time=30677.691..1981988.618 rows=7217 loops=1)"
"                                                        ->  Nested Loop Left Join  (cost=43.56..6824.30 rows=1 width=208) (actual time=30677.641..1980786.668 rows=5682 loops=1)"
"                                                              ->  Nested Loop Left Join  (cost=43.13..6815.92 rows=1 width=175) (actual time=30677.603..1980743.118 rows=5682 loops=1)"
"                                                                    Filter: ((designations2.des_lng_id IS NULL) OR (designations2.des_lng_id = 37))"
"                                                                    Rows Removed by Filter: 187440"
"                                                                    ->  Nested Loop Left Join  (cost=42.70..6806.78 rows=1 width=175) (actual time=30677.502..1980605.362 rows=5682 loops=1)"
"                                                                          ->  Nested Loop Left Join  (cost=42.28..6798.41 rows=1 width=142) (actual time=30677.450..1980549.274 rows=5682 loops=1)"
"                                                                                Filter: ((tof_designations.des_lng_id IS NULL) OR (tof_designations.des_lng_id = 37))"
"                                                                                Rows Removed by Filter: 187506"
"                                                                                ->  Nested Loop  (cost=41.85..6789.27 rows=1 width=142) (actual time=30677.319..1980390.236 rows=5682 loops=1)"
"                                                                                      ->  Nested Loop  (cost=41.42..6780.88 rows=1 width=109) (actual time=30677.268..1978805.586 rows=5682 loops=1)"
"                                                                                            ->  Nested Loop  (cost=41.15..6773.98 rows=1 width=102) (actual time=30677.215..1978746.608 rows=5682 loops=1)"
"                                                                                                  ->  Nested Loop  (cost=40.72..6765.60 rows=1 width=69) (actual time=30677.145..1978147.760 rows=5682 loops=1)"
"                                                                                                        ->  Nested Loop  (cost=40.29..6755.81 rows=1 width=69) (actual time=30677.061..1976006.452 rows=5607 loops=1)"
"                                                                                                              ->  Nested Loop  (cost=39.85..6487.03 rows=32 width=73) (actual time=132.966..191097.416 rows=148270092 loops=1)"
"                                                                                                                    ->  Nested Loop  (cost=39.28..4132.13 rows=4 width=69) (actual time=132.302..2067.061 rows=53228 loops=1)"
"                                                                                                                          ->  Merge Join  (cost=38.85..546.46 rows=357 width=69) (actual time=132.225..479.362 rows=47531 loops=1)"
"                                                                                                                                Merge Cond: (tof_models.mod_id = tof_types.typ_mod_id)"
"                                                                                                                                ->  Index Scan using tof_models_mod_id_idx on tof_models  (cost=0.29..475.22 rows=10929 width=10) (actual time=0.015..21.466 row (...)"
"                                                                                                                                ->  Sort  (cost=38.57..39.46 rows=357 width=67) (actual time=132.199..295.242 rows=47531 loops=1)"
"                                                                                                                                      Sort Key: tof_types.typ_mod_id"
"                                                                                                                                      Sort Method: external sort  Disk: 3304kB"
"                                                                                                                                      ->  Index Scan using tof_types_typ_id249_idx on tof_types  (cost=0.29..23.43 rows=357 width=67) (actual time=0.012..51.666 (...)"
"                                                                                                                          ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations country_designations2  (cost=0.43..10.03 rows=1 w (...)"
"                                                                                                                                Index Cond: (cds_id = tof_models.mod_cds_id)"
"                                                                                                                                Filter: ((cds_lng_id = 37) AND ("substring"((cds_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                                Rows Removed by Filter: 36"
"                                                                                                                    ->  Index Scan using tof_link_la_typ_lat_typ_id_idx on tof_link_la_typ  (cost=0.57..588.35 rows=38 width=8) (actual time=0.648..2.796 rows=2 (...)"
"                                                                                                                          Index Cond: (lat_typ_id = tof_types.typ_id)"
"                                                                                                                          Filter: ("substring"((lat_ctm)::"bit", 249, 1) = B'1'::"bit")"
"                                                                                                                          Rows Removed by Filter: 45"
"                                                                                                              ->  Index Scan using tof_link_art_la_id_idx on tof_link_art  (cost=0.44..8.39 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=148270092)"
"                                                                                                                    Index Cond: (la_id = tof_link_la_typ.lat_la_id)"
"                                                                                                                    Filter: ((la_art_id = 1359418) AND ("substring"((la_ctm)::"bit", 249, 1) = B'1'::"bit"))"
"                                                                                                                    Rows Removed by Filter: 1"
"                                                                                                        ->  Index Scan using tof_country_designations_cds_id_idx on tof_country_designations  (cost=0.43..9.77 rows=2 width=8) (actual time=0.375..0.379 rows=1  (...)"
"                                                                                                              Index Cond: (cds_id = tof_types.typ_cds_id)"
"                                                                                                              Filter: (cds_lng_id = 37)"
"                                                                                                              Rows Removed by Filter: 28"
"                                                                                                  ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts  (cost=0.43..8.37 rows=1 width=41) (actual time=0.101..0.101 rows=1 loops=5682)"
"                                                                                                        Index Cond: (tex_id = tof_country_designations.cds_tex_id)"
"                                                                                            ->  Index Scan using tof_manufacturers_mfa_id_idx on tof_manufacturers  (cost=0.28..6.90 rows=1 width=11) (actual time=0.007..0.007 rows=1 loops=5682)"
"                                                                                                  Index Cond: (mfa_id = tof_models.mod_mfa_id)"
"                                                                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts7  (cost=0.43..8.38 rows=1 width=41) (actual time=0.276..0.276 rows=1 loops=5682)"
"                                                                                            Index Cond: (tex_id = country_designations2.cds_tex_id)"
"                                                                                ->  Index Scan using tof_designations_des_id_idx on tof_designations  (cost=0.42..8.81 rows=26 width=10) (actual time=0.010..0.018 rows=34 loops=5682)"
"                                                                                      Index Cond: (des_id = tof_types.typ_kv_engine_des_id)"
"                                                                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts2  (cost=0.43..8.37 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=5682)"
"                                                                                Index Cond: (tex_id = tof_designations.des_tex_id)"
"                                                                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations2  (cost=0.42..8.81 rows=26 width=10) (actual time=0.007..0.015 rows=34 loops=5682)"
"                                                                          Index Cond: (des_id = tof_types.typ_kv_fuel_des_id)"
"                                                              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts3  (cost=0.43..8.37 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=5682)"
"                                                                    Index Cond: (tex_id = designations2.des_tex_id)"
"                                                        ->  Index Scan using tof_link_typ_eng_lte_typ_id_idx on tof_link_typ_eng  (cost=0.29..8.23 rows=1 width=8) (actual time=0.207..0.208 rows=1 loops=5682)"
"                                                              Index Cond: (lte_typ_id = tof_types.typ_id)"
"                                                  ->  Index Scan using tof_engines_eng_id_idx on tof_engines  (cost=0.29..8.19 rows=1 width=12) (actual time=0.208..0.208 rows=1 loops=7217)"
"                                                        Index Cond: (eng_id = tof_link_typ_eng.lte_eng_id)"
"                                            ->  Index Scan using tof_designations_des_id_idx on tof_designations designations3  (cost=0.42..8.81 rows=26 width=10) (actual time=0.008..0.015 rows=33 loops=7217)"
"                                                  Index Cond: (des_id = tof_types.typ_kv_body_des_id)"
"                                      ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts4  (cost=0.43..8.37 rows=1 width=41) (actual time=0.006..0.007 rows=1 loops=7217)"
"                                            Index Cond: (tex_id = designations3.des_tex_id)"
"                                ->  Index Scan using tof_designations_des_id_idx on tof_designations designations4  (cost=0.42..8.81 rows=26 width=10) (actual time=0.001..0.001 rows=1 loops=7217)"
"                                      Index Cond: (des_id = tof_types.typ_kv_model_des_id)"
"                          ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts5  (cost=0.43..8.37 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=7217)"
"                                Index Cond: (tex_id = designations4.des_tex_id)"
"                    ->  Index Scan using tof_designations_des_id_idx on tof_designations designations5  (cost=0.42..8.81 rows=26 width=10) (actual time=0.001..0.001 rows=1 loops=7217)"
"                          Index Cond: (des_id = tof_types.typ_kv_axle_des_id)"
"              ->  Index Scan using tof_des_texts_tex_id_idx on tof_des_texts des_texts6  (cost=0.43..8.37 rows=1 width=41) (actual time=0.001..0.001 rows=0 loops=7217)"
"                    Index Cond: (tex_id = designations5.des_tex_id)"
"Total runtime: 1984014.096 ms"

Alexius
>>а по-хорошему - нужно, конечно, переписать запрос в более понятном человеку и планировщику виде (используя CTE, подзапросы).

Да в принципе запрос нормально работает в 99.99% случаев уже больше года, исполняясь сотни раз в день, такие тормоза - единичны, но все-таки иногда напрягают. А насчет переписать - тут еще подучиться надо ;)

З.Ы.

Код: sql

1.
2.

set join_collapse_limit = 16;
set work_mem = '32MB';

Тоже не помогло...

...

Рейтинг:

0 / 0

06.11.2014, 16:09:18

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38797522

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

sabbath66,

Что-то со статистиками.

Для быстро запроса ошибка по записям где-то в районе 4-8 тысяч, что в вашем случае не критично:

Код: sql

Nested Loop (cost=12.21..1,967.60 rows=1 width=67) (actual time=2.488..1,970.196 rows=4,492 loops=1)

Для медленного ошибка очень большая:

Код: sql

Nested Loop (cost=426.63..4,041.69 rows=4 width=69) (actual time=7.756..9,224.102 rows=53,228 loops=1)

1. Какая версия Postgres?
2. Включен ли autovacuum? `SELECT name,setting FROM pg_settings WHERE name ~ 'autovacuum';`
3. Как настроена база? `SELECT name,setting FROM pg_settings WHERE source NOT IN ('default','override');`
4. Как давно анализировались/vacuum-ировались таблицы из запроса? `SELECT * FROM pg_stat_user_tables WHERE relname ~ '^tof';`

...

Рейтинг:

0 / 0

06.11.2014, 17:00:42

| Ответить | Цитировать | Написать

Огромная разница по времени выполнения запроса

#38797553

планы где

Гость

vyegorov,

там у него по 150 000 000 loops-ов кое-где (кстати выводит планы скорее всего в пгадмине, а длина текстового поля ограничена примерно 255 -- самого интересного кое-где и не видно).

ну и читать исходный запрос -- не подарок.

...

Рейтинг:

0 / 0

06.11.2014, 17:19:19

| Ответить | Цитировать | Написать

14 сообщений из 14, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Огромная разница по времени выполнения запроса

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&fpage=120&tid=1998373]:	0ms
get settings:	7ms
get forum list:	13ms
check forum access:	2ms
check topic access:	2ms
track hit:	16ms
get topic data:	8ms
get forum data:	2ms
get page messages:	45ms
get tp. blocked users:	1ms
others:	233ms

total:	329ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы