explain.depesz.com

PostgreSQL's explain analyze made readable

Result: chy4 : Unique (cost=9823242.54..9849203.40 rows=576908 width=477) CTE deals_and_reports -> Unique (cost=4723463.82..4994934.09 rows=18098018 width=129) -> Sort (cost=4723463.82..4768708.87 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=178593.23..1658116.03 rows=18098018 width=129) -> Hash Right Join (cost=178593.23..474884.41 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..272538.07 rows=9049009 width=9) -> Hash (cost=130217.30..130217.30 rows=3870030 width=57) -> Seq Scan on bo_contract_hardwood_deal chd (cost=0.00..130217.30 rows=3870030 width=57) -> Hash Join (cost=178592.67..1002251.44 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=130217.30..130217.30 rows=3870030 width=57) -> Seq Scan on bo_contract_hardwood_deal chd_1 (cost=0.00..130217.30 rows=3870030 width=57) -> Sort (cost=4828308.45..4829750.72 rows=576908 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 Left Join (cost=4421424.81..4773104.17 rows=576908 width=477) 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) -> Hash Left Join (cost=4421421.90..4649498.72 rows=576908 width=452) Hash Cond: (h.created_by = users.login) -> Hash Join (cost=4412225.00..4638787.39 rows=576908 width=366) 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=4124218.80..4226247.06 rows=769211 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) -> Sort (cost=696910.65..697213.16 rows=121007 width=85) Sort Key: h.object_key -> Index Scan using bo_history_host_idx on bo_history h (cost=0.57..686694.79 rows=121007 width=85) Index Cond: (host = ANY ('{91.218.223.170,94.241.10.214,178.218.23.173,5.143.166.27,91.188.190.225,91.188.190.70,176.114.215.153,95.106.139.136,5.143.167.80,82.140.254.237,82.140.238.96,82.140.207.145,95.106.142.32,83.220.238.190,82.140.221.83,83.220.238.237,95.106.135.72,95.106.133.37,2.94.96.44,2.93.176.130,213.87.159.147,82.140.242.251,95.31.178.252,83.136.240.162,2.93.111.246,178.218.26.179,128.73.165.144,213.87.134.232,82.140.209.130,82.140.248.29,213.87.145.174,82.140.236.177,95.106.143.18,95.106.138.215,213.87.149.174,128.73.105.178,95.31.171.75,213.87.148.9,78.25.123.247,213.87.162.95,78.25.120.137,91.218.223.171,78.25.123.243,213.87.134.18,78.25.120.185,176.14.144.7,176.14.159.222,213.87.146.119,78.25.120.138,204.97.97.42,213.87.147.122,78.25.121.123,78.25.120.113,95.31.187.202,213.87.135.248,95.31.165.139,78.25.121.95,178.252.97.133,95.31.166.118,78.25.122.99,95.31.178.57,95.31.176.25,176.14.146.50,176.14.143.155,78.25.121.134,213.87.161.16,213.87.147.232,213.87.146.232,95.31.169.109,78.25.120.89,95.31.172.175,213.87.132.144,176.14.151.74,78.25.120.161,213.87.144.103,213.87.145.245,213.87.146.230,78.25.123.201,213.87.147.98,95.31.180.205,213.87.133.130,95.31.188.232,213.87.132.222,213.87.146.164,78.25.121.138,176.14.152.128,78.25.123.110,176.14.149.0,78.25.123.44,213.87.130.131,78.25.120.207,193.160.231.89,213.87.139.94,213.87.147.88,193.160.231.198,109.172.104.50,95.31.160.15,176.14.131.130,78.25.121.237,176.14.152.187,78.25.123.98,78.25.120.188,176.14.132.38,213.87.130.46,213.87.134.125,176.14.134.25,176.14.139.251,176.14.138.23,78.25.123.212,213.87.131.205,176.14.131.33,213.87.147.132,95.31.173.176,31.42.230.203,185.63.216.230,78.25.121.156,78.25.122.10,213.87.145.205,213.87.135.177,78.25.123.237,213.87.152.208,213.87.145.244,78.25.120.22,188.162.65.50,213.87.135.186,78.25.122.77,213.87.150.91,213.87.151.27,213.87.129.230,213.87.161.32,78.25.122.52,78.25.120.182,213.87.139.132,213.87.156.237,213.87.134.81,78.25.121.30,78.25.122.9,77.56.36.15,78.25.121.234,78.25.123.158,193.106.73.18,78.25.120.163,78.25.122.240,78.25.121.255,84.42.23.35,31.42.235.250,78.25.120.35,78.25.121.245,185.63.219.184,78.37.137.119,78.25.123.30,178.71.68.68,78.25.122.152,78.25.122.60,78.25.123.111,78.25.122.127,178.71.196.164,78.25.123.189,78.25.123.150,78.25.122.231,78.25.121.81,78.25.121.48,178.71.78.205,78.25.121.82,78.37.138.57,178.69.36.65,78.25.121.87,78.25.121.80,78.37.128.6,92.100.253.36,94.142.19.120,95.55.124.249,188.170.73.120,84.47.168.7,188.170.72.208,188.170.74.148,84.47.168.4,188.170.73.236,188.170.73.249,188.170.75.179,188.170.72.16,188.170.72.18,188.170.83.201,185.63.218.230,188.170.72.130,188.170.82.230,188.170.81.84,77.94.164.125,109.248.60.1,188.170.72.174,213.87.161.148,213.87.150.38,188.170.72.214,188.170.75.83,188.170.82.17,213.87.150.140,188.170.73.13,213.87.137.139,188.170.73.46,94.25.170.193,188.170.73.35,193.106.73.124,80.246.94.202,82.140.246.50,213.87.162.152,188.170.80.191,82.140.206.205,213.87.157.233,188.170.82.177,176.14.226.252,213.87.131.64,176.14.239.98,188.170.83.213,188.170.81.249,188.170.74.11,213.87.139.11,213.87.133.56,213.87.158.213,84.42.24.27,188.170.81.95,188.170.80.133,213.87.147.42,213.87.146.243,213.87.148.59,188.170.81.34,213.87.130.253,85.140.114.90,213.87.131.254,188.170.83.238,213.87.161.7,176.14.236.229,213.87.132.146,213.87.129.102,213.87.149.6,213.87.148.229,213.87.148.159,213.87.138.0,213.87.131.134,213.87.161.59,188.170.75.175,188.170.75.214,176.14.226.247,95.106.157.5,213.87.157.170,213.87.135.199,213.87.146.179,213.87.148.205,213.87.161.68,188.170.75.205,94.25.228.212,188.170.74.4,188.170.74.65,188.170.75.52,213.87.163.114,213.87.160.184,188.170.75.54,85.246.86.163,213.87.148.108,213.87.163.160,188.170.75.48,213.87.128.88,188.170.73.100,176.14.235.109,188.170.81.10,213.87.131.8,188.170.74.242,213.87.136.66,213.87.132.245,188.170.75.40,213.87.150.33,176.14.228.68,188.162.65.96,178.218.24.179,94.25.229.100,188.170.80.52,188.162.65.90,188.162.65.83,213.87.156.25,94.25.229.225,213.87.139.134,213.87.133.66,188.170.80.51,213.87.135.30,188.170.80.33,213.87.147.230,213.87.128.140,213.87.148.230,213.87.150.53,213.87.147.234,213.87.161.41,213.87.152.16,213.87.159.227,213.87.159.251,213.87.144.250,213.87.137.255,213.87.130.87,213.87.152.69,213.87.162.52,213.87.136.40,213.87.158.148,188.170.83.37,213.87.155.162,188.170.81.126,176.14.229.245,188.170.83.49,213.87.139.183,188.170.75.246,188.170.72.251,213.87.130.161,213.87.130.36,188.170.83.153,146.185.157.230,188.170.82.25,176.14.234.68,213.87.156.100,198.211.126.107,213.87.137.80,213.87.155.82,188.170.82.53,188.170.73.192,213.87.148.89,213.87.151.88,188.170.73.78,213.87.150.190,146.185.157.243,213.87.136.174,188.170.83.113,188.170.82.81,213.87.129.159,80.246.94.186,188.226.220.27,213.87.161.247,95.106.144.165,188.170.81.116,94.241.43.11,176.14.235.62,213.87.155.146,213.87.161.157,188.170.83.240,213.87.136.12,80.246.94.131,213.87.149.2,213.87.159.47,213.87.137.95,213.87.155.141,213.87.163.111,213.87.145.97,213.87.134.187,176.14.229.63,213.87.128.155,213.87.151.236,213.87.128.52,213.87.151.190,188.162.64.223,213.87.160.239,188.162.65.192,192.81.223.221,213.87.144.139,188.162.64.58,94.25.228.198,94.25.228.221,213.87.149.89,94.25.228.245,188.170.72.177,94.25.228.184,213.87.158.34,188.162.64.108,188.162.65.187,188.162.64.106,213.87.136.128,188.162.64.204,94.25.228.186,213.87.138.45,213.87.155.160,94.25.228.183,213.87.160.114,213.87.151.25,213.87.131.191,188.162.65.197,213.87.163.217,188.162.65.66,188.162.65.16,188.170.83.120,188.162.64.4,94.25.229.200,94.25.228.35,188.162.64.111,94.25.228.25,188.162.64.30,82.196.1.179,178.62.237.202,213.87.133.116,213.87.134.87,213.87.132.72,188.226.226.40,213.87.161.243,213.87.156.82,188.170.81.59,213.87.128.79,188.170.81.144,213.87.151.116,213.87.156.200,213.87.138.217,188.170.81.140,80.246.94.152,188.170.72.253,213.87.132.241,213.87.161.200,213.87.149.120,213.87.146.240,213.87.152.219,198.16.70.28,213.87.144.215,213.87.151.185,188.170.73.43,188.170.81.107,213.87.161.207,213.87.131.125,213.87.133.118,213.87.139.173,198.16.66.44,188.170.75.167,198.16.66.123,213.87.153.138,198.16.74.204,188.170.73.144,213.87.158.150,213.87.151.209,213.87.132.195,188.170.72.246,213.87.152.89,188.170.73.240,213.87.148.49,50.7.93.85,188.170.75.159,188.170.74.184,185.218.15.131,213.87.137.2,188.170.75.230,198.16.66.139,213.87.144.63,188.170.82.74,213.87.149.108,188.170.81.43,188.170.81.130,213.87.157.176,188.170.72.222,198.16.74.44,213.87.137.221,213.87.130.174,213.87.137.171,198.16.66.155,188.170.82.124,213.87.138.150,85.192.189.44,188.170.82.136,85.192.189.82,213.87.151.17,213.87.135.142,95.71.202.142,188.170.74.142,178.45.200.82,213.87.128.151,188.170.80.146,213.87.149.121,85.192.188.33,188.170.75.147,50.7.93.28,213.87.146.44,213.87.138.147,188.170.73.220,213.87.145.140,178.44.186.79,213.87.160.130,188.162.64.103,94.25.228.151,213.87.135.76,94.25.238.47,188.170.75.194,94.25.228.28,213.87.148.75,94.25.238.8,176.14.228.99,213.87.155.118,94.25.228.7,188.170.72.170,94.25.238.127,213.87.133.189,94.25.238.101,213.87.152.73,213.87.163.14,188.170.83.144,213.87.130.60,188.170.80.90,213.87.159.129,176.14.238.226,213.87.156.24,213.87.128.132,213.87.136.95,213.87.151.166,213.87.144.213,188.170.74.194,188.170.72.150,213.87.144.177,188.170.80.172,213.87.134.254,188.170.75.75,213.87.129.65,213.87.146.51,198.16.76.28,213.87.145.34,188.170.74.46,213.87.135.26,91.193.179.82,188.170.82.149,198.16.70.52,213.87.129.208,188.170.73.69,109.194.66.97,94.25.238.66,94.25.238.198,188.170.83.35,188.170.81.69,91.193.179.117,198.16.66.141,83.220.237.207,83.220.238.116,94.25.238.124,78.31.204.20,91.193.178.209,198.16.66.140,94.25.238.250,91.193.177.157,83.220.236.110,94.25.238.92,94.25.238.34,94.25.238.121,94.25.238.206,94.25.238.84,151.124.47.182,94.25.238.202,151.124.47.59,151.124.47.89,198.16.66.195,91.193.178.238,200.120.184.237,91.193.179.42,189.108.214.85,213.87.160.126,83.220.238.233,83.220.238.114,37.112.76.26,213.87.151.0,94.25.238.162,193.109.85.6,198.16.74.205,213.87.148.190,91.193.177.156,83.220.238.183,91.193.178.171,5.165.132.17,198.16.66.197,83.220.239.205,91.193.178.40,50.7.142.181,5.165.128.38,83.220.239.140,198.16.66.43,91.193.177.169,37.112.68.22,91.193.176.190,109.94.68.161,83.220.238.235}'::text[])) Filter: ((object_type = ANY ('{CHD,CHDR}'::text[])) AND (host <> ALL ('{10.0.17.80,10.0.17.51}'::text[]))) -> Hash (cost=179703.65..179703.65 rows=2475484 width=226) -> Hash Join (cost=1.09..179703.65 rows=2475484 width=226) Hash Cond: (party.party_type_fkey = party_type.id) -> Seq Scan on bo_party party (cost=0.00..161858.45 rows=3300645 width=188) -> Hash (cost=1.05..1.05 rows=3 width=42) -> Seq Scan on lu_party_type party_type (cost=0.00..1.05 rows=3 width=42) Filter: (id <> 2) -> Hash (cost=7189.18..7189.18 rows=160618 width=102)

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))