explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oMgj : Unique (cost=26230098.66..26230125.88 rows=605 width=477) CTE deals_and_reports -> Unique (cost=4723372.80..4994843.07 rows=18098018 width=129) -> Sort (cost=4723372.80..4768617.85 rows=18098018 width=129) Sort Key: chd.id, chd.main_deal_number, chd.is_buyer, chd.bo_party_seller_fkey, chd.bo_party_buyer_fkey -> Append (cost=178553.72..1658025.01 rows=18098018 width=129) -> Hash Right Join (cost=178553.72..474832.90 rows=9049009 width=57) Hash Cond: ((rep.bo_contract_hardwood_fkey)::text = (chd.id)::text) -> Index Only Scan using bo_hardwood_deal_report__contract__side on bo_hardwood_deal_report rep (cost=0.56..272526.07 rows=9049009 width=9) -> Hash (cost=130199.74..130199.74 rows=3868274 width=57) -> Seq Scan on bo_contract_hardwood_deal chd (cost=0.00..130199.74 rows=3868274 width=57) -> Hash Right Join (cost=178553.16..1002211.93 rows=9049009 width=57) Hash Cond: ((rep_1.bo_contract_hardwood_fkey)::text = (chd_1.id)::text) -> Seq Scan on bo_hardwood_deal_report rep_1 (cost=0.00..799905.09 rows=9049009 width=18) Filter: (id IS NOT NULL) -> Hash (cost=130199.74..130199.74 rows=3868274 width=57) -> Seq Scan on bo_contract_hardwood_deal chd_1 (cost=0.00..130199.74 rows=3868274 width=57) -> Sort (cost=21235255.59..21235257.10 rows=605 width=477) Sort Key: dr.main_deal_number, (CASE WHEN ((dr.bo_party_buyer_fkey)::text = (party.id)::text) THEN 'Покупатель'::text ELSE 'Продавец'::text END), (ltrim(concat_ws(' '::text, party.party_name, party.person_last_name, party.person_first_name, party.person_mid_name))), party.inn, party_type.type, users.login, (((((users.last_name || ' '::text) || users.first_name) || ' '::text) || users.middle_name)), users.create_date, users.email, users.phone_num, h.host, (CASE WHEN ("substring"(party.inn, 1, 2) = '01'::text) THEN 'Республика Адыгея'::text WHEN ("substring"(party.inn, 1, 2) = '02'::text) THEN 'Республика Башкортостан'::text WHEN ("substring"(party.inn, 1, 2) = '03'::text) THEN 'Республика Бурятия'::text WHEN ("substring"(party.inn, 1, 2) = '04'::text) THEN 'Республика Алтай'::text WHEN ("substring"(party.inn, 1, 2) = '05'::text) THEN 'Республика Дагестан'::text WHEN ("substring"(party.inn, 1, 2) = '06'::text) THEN 'Республика Ингушетия'::text WHEN ("substring"(party.inn, 1, 2) = '07'::text) THEN 'Кабардино-Балкарская республика'::text WHEN ("substring"(party.inn, 1, 2) = '08'::text) THEN 'Республика Калмыкия'::text WHEN ("substring"(party.inn, 1, 2) = '09'::text) THEN 'Карачаево-Черкесская республика'::text WHEN ("substring"(party.inn, 1, 2) = '80'::text) THEN 'Забайкальский край'::text WHEN ("substring"(party.inn, 1, 2) = '81'::text) THEN 'Пермский край'::text WHEN ("substring"(party.inn, 1, 2) = '82'::text) THEN 'Республика Крым'::text WHEN ("substring"(party.inn, 1, 2) = '83'::text) THEN 'Ненецкий автономный округ'::text WHEN ("substring"(party.inn, 1, 2) = '84'::text) THEN 'Красноярский край'::text WHEN ("substring"(party.inn, 1, 2) = '85'::text) THEN 'Иркутская область'::text WHEN ("substring"(party.inn, 1, 2) = '86'::text) THEN 'Ханты-Мансийский автономный округ — Югра'::text WHEN ("substring"(party.inn, 1, 2) = '89'::text) THEN 'Ямало-Ненецкий автономный округ'::text WHEN ("substring"(party.inn, 1, 2) = '91'::text) THEN 'Калининградская область'::text WHEN ("substring"(party.inn, 1, 2) = '92'::text) THEN 'г. Севастополь'::text WHEN ("substring"(party.inn, 1, 2) = '97'::text) THEN 'г. Москва'::text ELSE ce.name END), party.physical_address, h.id, (to_char(h.create_date, 'YYYY-MM-DD HH24:MI:SS'::text)), (CASE WHEN ((h.object_type = 'CHD'::text) AND (h.action_type = 'create'::text)) THEN 'Создание (сделка)'::text WHEN ((h.object_type = 'CHD'::text) AND (h.action_type = 'signed'::text)) THEN 'Подписание (сделка)'::text WHEN ((h.object_type = 'CHD'::text) AND (h.action_type = 'edit'::text)) THEN 'Редактирование (сделка)'::text WHEN ((h.object_type = 'CHD'::text) AND (h.action_type = 'delete'::text)) THEN 'Удаление (сделка)'::text WHEN ((h.object_type = 'CHD'::text) AND (h.action_type = 'restore'::text)) THEN 'Восстановление (сделка)'::text WHEN ((h.object_type = 'CHD'::text) AND (h.action_type = 'update'::text)) THEN 'Обновление (сделка)'::text WHEN ((h.object_type = 'CHDR'::text) AND (h.action_type = 'create'::text)) THEN 'Создание (отчёт)'::text WHEN ((h.object_type = 'CHDR'::text) AND (h.action_type = 'signed'::text)) THEN 'Подписание (отчёт)'::text WHEN ((h.object_type = 'CHDR'::text) AND (h.action_type = 'edit'::text)) THEN 'Редактирование (сделка)'::text WHEN ((h.object_type = 'CHDR'::text) AND (h.action_type = 'delete'::text)) THEN 'Удаление (сделка)'::text WHEN ((h.object_type = 'CHDR'::text) AND (h.action_type = 'restore'::text)) THEN 'Восстановление (сделка)'::text WHEN ((h.object_type = 'CHDR'::text) AND (h.action_type = 'update'::text)) THEN 'Обновление (сделка)'::text ELSE NULL::text END), h.message -> Hash Join (cost=18143878.29..21235227.63 rows=605 width=477) Hash Cond: (party.party_type_fkey = party_type.id) -> Hash Left Join (cost=18143877.20..21235114.37 rows=605 width=451) Hash Cond: (CASE WHEN ("substring"(party.inn, 1, 2) = '01'::text) THEN '1'::text WHEN ("substring"(party.inn, 1, 2) = '02'::text) THEN '2'::text WHEN ("substring"(party.inn, 1, 2) = '03'::text) THEN '3'::text WHEN ("substring"(party.inn, 1, 2) = '04'::text) THEN '4'::text WHEN ("substring"(party.inn, 1, 2) = '05'::text) THEN '5'::text WHEN ("substring"(party.inn, 1, 2) = '06'::text) THEN '6'::text WHEN ("substring"(party.inn, 1, 2) = '07'::text) THEN '7'::text WHEN ("substring"(party.inn, 1, 2) = '08'::text) THEN '8'::text WHEN ("substring"(party.inn, 1, 2) = '09'::text) THEN '9'::text ELSE "substring"(party.inn, 1, 2) END = (ce.id)::text) -> Nested Loop Left Join (cost=18143874.28..21235090.74 rows=605 width=414) -> Hash Join (cost=18143873.86..21233051.06 rows=605 width=328) Hash Cond: ((CASE WHEN (NOT dr.is_buyer) THEN dr.bo_party_seller_fkey ELSE dr.bo_party_buyer_fkey END)::text = (party.id)::text) -> Merge Right Join (cost=18143830.10..20796142.02 rows=166424656 width=173) Merge Cond: ((dr.id)::text = h.object_key) -> Sort (cost=3427308.15..3472553.20 rows=18098018 width=129) Sort Key: dr.id -> CTE Scan on deals_and_reports dr (cost=0.00..361960.36 rows=18098018 width=129) -> Materialize (cost=14716521.95..14847425.92 rows=26180794 width=85) -> Sort (cost=14716521.95..14781973.93 rows=26180794 width=85) Sort Key: h.object_key -> Bitmap Heap Scan on bo_history h (cost=464551.59..10595933.10 rows=26180794 width=85) Recheck Cond: (object_type = ANY ('{CHD,CHDR}'::text[])) Filter: (host <> ALL ('{10.0.17.80,10.0.17.51}'::text[])) -> Bitmap Index Scan on bo_history__object_type__object_key (cost=0.00..458006.40 rows=28714567 width=0) Index Cond: (object_type = ANY ('{CHD,CHDR}'::text[])) -> Hash (cost=43.61..43.61 rows=12 width=188) -> Index Scan using bo_party_inn_idx on bo_party party (cost=0.43..43.61 rows=12 width=188) Index Cond: (inn = ANY ('{2709016413,2727001900,2723127073,2703082018,2709007440,2722051287,2460205089,7536160697}'::text[])) -> Index Scan using user_login_unique_key on bo_user users (cost=0.42..3.37 rows=1 width=102) Index Cond: (h.created_by = login) -> Hash (cost=1.85..1.85 rows=85 width=39) -> Seq Scan on bo_constituent_entity ce (cost=0.00..1.85 rows=85 width=39) -> Hash (cost=1.04..1.04 rows=4 width=42) -> Seq Scan on lu_party_type party_type (cost=0.00..1.04 rows=4 width=42)

Settings
# exclusive inclusive rows x rows loops node
1. 0.158 55.012 ↑ 11.4 71 1

Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)

  • Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
  • Sort Method: quicksort Memory: 43kB
2. 1.378 54.854 ↑ 11.4 71 1

Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)

  • Hash Cond: (p.pronamespace = n.oid)
3. 53.465 53.465 ↓ 3.0 2,402 1

Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2,402 loops=1)

  • Filter: pg_function_is_visible(oid)
4. 0.004 0.011 ↑ 1.0 4 1

Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
5. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)

  • Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))