В запросе не используются индексы при добавлении еще одного join / PostgreSQL

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

Гость

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

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

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

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / В запросе не используются индексы при добавлении еще одного join

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

В запросе не используются индексы при добавлении еще одного join

#39472459

Ilnur26

Гость

Есть запрос, работающий более 3 минут

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

SELECT t0.PatientID, t0.FirstName, t0.LastName, t0.FatherName, t0.DocSeries, t0.DocNumber, t0.SNILS, t0.FIO, t0.Birthday
, t0.UniqueId, t0.DocIssueDate, t0.DocIssueOrg, t0.Sex, t0.DocType, t0.SocialStatus, t0.AddressReal, t0.AddressLegal
, t2.Name
, t3.Name
, t4.Name
, t5.FreeAddressString, t5.House, t5.City, t5.Street, t5.Block, t5.Flat, t5.ZIPCode, t5.District
, t6.Name, t6.CityType, t6.Rajon, t6.Region, t6.Country, t6.ZIPCode
, t7.Name
FROM AKUZ.T_PATIENT t0 
LEFT JOIN AKUZ.T_SNILS t1 ON t1.Snils = t0.SNILS
LEFT JOIN VCLib.T_ENUMS t2 ON t2.EnumID = t0.Sex
LEFT JOIN VCLib.T_ENUMS t3 ON t3.EnumID = t0.DocType
LEFT JOIN AKUZ.T_SOCIAL_STATUS t4 ON t4.SocialStatusID = t0.SocialStatus 
LEFT JOIN AKUZ.T_PATIENT_ADDRESS t5 ON t5.AddressID = t0.AddressReal
LEFT JOIN AKUZ.T_CITY t6 ON t6.CityID = t5.City
LEFT JOIN AKUZ.T_RAJON t7 ON t7.RajonID = t6.Rajon
WHERE EXISTS 
(
	SELECT * FROM AKUZ.T_POLICY t21  
	WHERE t21.type_ IN (536) 
	AND t0.PatientID = t21.Patient 
	AND t21.Number = '1111111111111111'
	AND t21.IsGuardian = 'f'
)
ORDER BY t0.UniqueId DESC NULLS LAST, t0.PatientID DESC NULLS LAST 
LIMIT 151;

План запроса:

Limit (cost=1528642.54..1528642.58 rows=17 width=638) (actual time=200363.127..200363.128 rows=2 loops=1)
-> Sort (cost=1528642.54..1528642.58 rows=17 width=638) (actual time=200363.121..200363.122 rows=2 loops=1)
Sort Key: t0.uniqueid DESC NULLS LAST, t0.patientid DESC NULLS LAST
Sort Method: quicksort Memory: 26kB
-> Hash Semi Join (cost=584102.06..1528642.19 rows=17 width=638) (actual time=152070.928..200363.067 rows=2 loops=1)
Hash Cond: (t0.patientid = t21.patient)
-> Hash Left Join (cost=584029.22..1517493.13 rows=4219313 width=638) (actual time=45679.395..199480.433 rows=4219445 loops=1)
Hash Cond: (t0.socialstatus = t4.socialstatusid)
-> Hash Left Join (cost=584027.92..1459476.28 rows=4219313 width=611) (actual time=45679.376..196890.418 rows=4219445 loops=1)
Hash Cond: (t0.doctype = t3.enumid)
-> Hash Left Join (cost=584004.98..1401437.79 rows=4219313 width=573) (actual time=45679.227..194369.779 rows=4219445 loops=1)
Hash Cond: (t0.sex = t2.enumid)
-> Hash Left Join (cost=583982.04..1343399.29 rows=4219313 width=535) (actual time=45679.035..191583.424 rows=4219445 loops=1)
Hash Cond: (t0.addressreal = t5.addressid)
-> Seq Scan on t_patient t0 (cost=0.00..174727.13 rows=4219313 width=310) (actual time=0.008..56407.078 rows=4219445 loops=1)
-> Hash (cost=364487.55..364487.55 rows=4802599 width=241) (actual time=45632.079..45632.079 rows=4802258 loops=1)
Buckets: 16384 Batches: 512 Memory Usage: 1415kB
-> Hash Left Join (cost=14436.52..364487.55 rows=4802599 width=241) (actual time=183.706..39856.521 rows=4802258 loops=1)
Hash Cond: (t5.city = t6.cityid)
-> Seq Scan on t_patient_address t5 (cost=0.00..129958.99 rows=4802599 width=141) (actual time=9.886..38149.040 rows=4802258 loops=1)
-> Hash (cost=8569.73..8569.73 rows=195023 width=116) (actual time=173.554..173.554 rows=195023 loops=1)
Buckets: 32768 Batches: 8 Memory Usage: 3828kB
-> Hash Left Join (cost=73.06..8569.73 rows=195023 width=116) (actual time=1.335..88.063 rows=195023 loops=1)
Hash Cond: (t6.rajon = t7.rajonid)
-> Seq Scan on t_city t6 (cost=0.00..5826.23 rows=195023 width=93) (actual time=0.010..20.104 rows=195023 loops=1)
-> Hash (cost=48.58..48.58 rows=1958 width=39) (actual time=1.300..1.300 rows=1958 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 153kB
-> Seq Scan on t_rajon t7 (cost=0.00..48.58 rows=1958 width=39) (actual time=0.006..0.551 rows=1958 loops=1)
-> Hash (cost=14.64..14.64 rows=664 width=42) (actual time=0.166..0.166 rows=664 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 58kB
-> Seq Scan on t_enums t2 (cost=0.00..14.64 rows=664 width=42) (actual time=0.006..0.078 rows=664 loops=1)
-> Hash (cost=14.64..14.64 rows=664 width=42) (actual time=0.140..0.140 rows=664 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 58kB
-> Seq Scan on t_enums t3 (cost=0.00..14.64 rows=664 width=42) (actual time=0.002..0.059 rows=664 loops=1)
-> Hash (cost=1.13..1.13 rows=13 width=43) (actual time=0.009..0.009 rows=13 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t_social_status t4 (cost=0.00..1.13 rows=13 width=43) (actual time=0.002..0.003 rows=13 loops=1)
-> Hash (cost=72.63..72.63 rows=17 width=16) (actual time=0.116..0.116 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan Backward using ix_t_policy_number_patient on t_policy t21 (cost=0.56..72.63 rows=17 width=16) (actual time=0.096..0.104 rows=2 loops=1)
Index Cond: (((number)::text = '1111111111111111'::text) AND (type_ = 536))
Filter: (NOT isguardian)
Planning time: 76.482 ms
Execution time: 200364.881 ms

Закомменчиваю один join

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

SELECT t0.PatientID, t0.FirstName, t0.LastName, t0.FatherName, t0.DocSeries, t0.DocNumber, t0.SNILS, t0.FIO, t0.Birthday
, t0.UniqueId, t0.DocIssueDate, t0.DocIssueOrg, t0.Sex, t0.DocType, t0.SocialStatus, t0.AddressReal, t0.AddressLegal
, t2.Name
, t3.Name
, t4.Name
, t5.FreeAddressString, t5.House, t5.City, t5.Street, t5.Block, t5.Flat, t5.ZIPCode, t5.District
, t6.Name, t6.CityType, t6.Rajon, t6.Region, t6.Country, t6.ZIPCode
--, t7.Name
FROM AKUZ.T_PATIENT t0 
LEFT JOIN AKUZ.T_SNILS t1 ON t1.Snils = t0.SNILS
LEFT JOIN VCLib.T_ENUMS t2 ON t2.EnumID = t0.Sex
LEFT JOIN VCLib.T_ENUMS t3 ON t3.EnumID = t0.DocType
LEFT JOIN AKUZ.T_SOCIAL_STATUS t4 ON t4.SocialStatusID = t0.SocialStatus 
LEFT JOIN AKUZ.T_PATIENT_ADDRESS t5 ON t5.AddressID = t0.AddressReal
LEFT JOIN AKUZ.T_CITY t6 ON t6.CityID = t5.City
--LEFT JOIN AKUZ.T_RAJON t7 ON t7.RajonID = t6.Rajon
WHERE EXISTS 
(
	SELECT * FROM AKUZ.T_POLICY t21  
	WHERE t21.type_ IN (536) 
	AND t0.PatientID = t21.Patient 
	AND t21.Number = '1111111111111111'
	AND t21.IsGuardian = 'f'
)
ORDER BY t0.UniqueId DESC NULLS LAST, t0.PatientID DESC NULLS LAST 
LIMIT 151;

Запрос начинает использовать индексы и выполняется мгновенно:

Limit (cost=238.70..238.74 rows=17 width=615) (actual time=0.239..0.241 rows=2 loops=1)
-> Sort (cost=238.70..238.74 rows=17 width=615) (actual time=0.238..0.238 rows=2 loops=1)
Sort Key: t0.uniqueid DESC NULLS LAST, t0.patientid DESC NULLS LAST
Sort Method: quicksort Memory: 26kB
-> Nested Loop Left Join (cost=74.64..238.35 rows=17 width=615) (actual time=0.155..0.201 rows=2 loops=1)
-> Nested Loop Left Join (cost=74.22..230.68 rows=17 width=538) (actual time=0.152..0.196 rows=2 loops=1)
-> Nested Loop Left Join (cost=73.79..221.24 rows=17 width=413) (actual time=0.140..0.172 rows=2 loops=1)
-> Nested Loop Left Join (cost=73.65..218.48 rows=17 width=386) (actual time=0.133..0.160 rows=2 loops=1)
-> Nested Loop Left Join (cost=73.38..213.33 rows=17 width=348) (actual time=0.127..0.148 rows=2 loops=1)
-> Nested Loop (cost=73.10..208.19 rows=17 width=310) (actual time=0.116..0.130 rows=2 loops=1)
-> HashAggregate (cost=72.67..72.83 rows=16 width=16) (actual time=0.098..0.099 rows=2 loops=1)
Group Key: t21.patient
-> Index Scan Backward using ix_t_policy_number_patient on t_policy t21 (cost=0.56..72.63 rows=17 width=16) (actual time=0.077..0.083 rows=2 loops=1)
Index Cond: (((number)::text = '1111111111111111'::text) AND (type_ = 536))
Filter: (NOT isguardian)
-> Index Scan using t_patient_pkey on t_patient t0 (cost=0.43..8.45 rows=1 width=310) (actual time=0.011..0.012 rows=1 loops=2)
Index Cond: (patientid = t21.patient)
-> Index Scan using t_enums_pkey on t_enums t2 (cost=0.28..0.29 rows=1 width=42) (actual time=0.007..0.007 rows=1 loops=2)
Index Cond: (enumid = t0.sex)
-> Index Scan using t_enums_pkey on t_enums t3 (cost=0.28..0.29 rows=1 width=42) (actual time=0.004..0.005 rows=1 loops=2)
Index Cond: (enumid = t0.doctype)
-> Index Scan using t_social_status_pkey on t_social_status t4 (cost=0.14..0.15 rows=1 width=43) (actual time=0.004..0.005 rows=1 loops=2)
Index Cond: (socialstatusid = t0.socialstatus)
-> Index Scan using t_patient_address_pkey on t_patient_address t5 (cost=0.43..0.55 rows=1 width=141) (actual time=0.010..0.011 rows=1 loops=2)
Index Cond: (addressid = t0.addressreal)
-> Index Scan using t_city_pkey on t_city t6 (cost=0.42..0.44 rows=1 width=93) (actual time=0.001..0.001 rows=0 loops=2)
Index Cond: (cityid = t5.city)
Planning time: 12.030 ms
Execution time: 0.785 ms

Почему индексы игнорируются в 1-м случае?

Версия - PostgreSQL 9.5.5, compiled by Visual C++ build 1800, 64-bit

...

Рейтинг:

0 / 0

15.06.2017, 16:27

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

В запросе не используются индексы при добавлении еще одного join

#39472469

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Ilnur26,

Вероятнее всего не хватает умолчательного join_collapse_limit = 8 и/или from_collapse_limit=8
Поднимите до 10-12 и попробуйте.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

15.06.2017, 16:35

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

В запросе не используются индексы при добавлении еще одного join

#39472521

Ilnur26

Гость

Maxim Boguk,

Спасибо, помогло.

А насколько сильно усложнится жизнь планировщика, если выставить join_collapse_limit, например в 25?
Часто бываю запросы с множеством соединений.
Как определить оптимальную настройку для join_collapse_limit и geqo_threshold?
Не знаете где об этом можно почитать поподробнее?

...

Рейтинг:

0 / 0

15.06.2017, 17:15

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

В запросе не используются индексы при добавлении еще одного join

#39472540

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Ilnur26Maxim Boguk,

Спасибо, помогло.

А насколько сильно усложнится жизнь планировщика, если выставить join_collapse_limit, например в 25?
Часто бываю запросы с множеством соединений.
Как определить оптимальную настройку для join_collapse_limit и geqo_threshold?
Не знаете где об этом можно почитать поподробнее?

Есть одна проблема - время планирования растет как N! от количества таблиц.
Поэтому 25 поставить конечно можно но оптимальным образом запрос с 25 join будет планироваться до конца жизни вселенной.
:)
Выше 14 я бы не ставил да и то если вы готовы что время планирования для запросов с 14 и более таблицами будет больше секунды.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

15.06.2017, 17:30

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=53&msg=39472469&tid=1996446]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	3ms
check topic access:	3ms
track hit:	35ms
get topic data:	11ms
get forum data:	2ms
get page messages:	47ms
get tp. blocked users:	1ms
others:	326ms

total:	451ms