explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TDaT

Settings
# exclusive inclusive rows x rows loops node
1. 141.929 59,836.215 ↓ 2.0 4 1

Hash Right Join (cost=40,000,726,665.54..40,004,982,903.12 rows=2 width=920) (actual time=12,042.605..59,836.215 rows=4 loops=1)

  • Hash Cond: ((deal.deal_pair_fkey)::text = (deal_1.parent_fkey)::text)
2. 430.898 58,250.555 ↓ 1.4 711,958 1

Hash Left Join (cost=20,000,349,404.16..20,004,603,124.05 rows=503,531 width=949) (actual time=3,941.936..58,250.555 rows=711,958 loops=1)

  • Hash Cond: (deal_details_wood_class.lu_timber_fkey = deal_details_timber_okpd.id)
3. 415.624 57,819.650 ↓ 1.4 711,958 1

Hash Left Join (cost=20,000,349,197.29..20,004,601,586.46 rows=503,531 width=951) (actual time=3,941.919..57,819.650 rows=711,958 loops=1)

  • Hash Cond: (deal_details.wood_class_fkey = deal_details_wood_class.id)
4. 1,251.835 57,403.854 ↓ 1.4 711,958 1

Hash Left Join (cost=20,000,349,125.33..20,004,600,168.73 rows=503,531 width=877) (actual time=3,941.739..57,403.854 rows=711,958 loops=1)

  • Hash Cond: ("substring"(party_seller.inn, 1, 2) = regexp_replace((seller_subject.id)::text, '^(\d)$'::text, '0\1'::text))
5. 292.463 56,151.762 ↓ 1.4 711,958 1

Hash Left Join (cost=10,000,349,109.72..10,004,593,607.21 rows=503,531 width=779) (actual time=3,941.468..56,151.762 rows=711,958 loops=1)

  • Hash Cond: (deal_details.lu_tnved_class_fkey = deal_details_tnved_class.id)
6. 600.882 55,857.830 ↓ 1.4 711,958 1

Hash Join (cost=10,000,348,775.93..10,004,591,226.73 rows=503,531 width=615) (actual time=3,939.989..55,857.830 rows=711,958 loops=1)

  • Hash Cond: ((deal.bo_party_seller_fkey)::text = (party_seller.id)::text)
7. 681.059 51,322.605 ↓ 1.4 711,958 1

Nested Loop (cost=10,000,000,014.22..10,004,234,912.20 rows=503,531 width=433) (actual time=4.121..51,322.605 rows=711,958 loops=1)

8. 344.927 46,913.006 ↓ 1.4 372,854 1

Nested Loop (cost=10,000,000,013.80..10,003,475,808.78 rows=260,897 width=413) (actual time=3.213..46,913.006 rows=372,854 loops=1)

  • Join Filter: (party_buyer.party_type_fkey = party_buyer_type.id)
  • Rows Removed by Join Filter: 600412
9. 509.256 46,568.079 ↓ 1.4 372,854 1

Nested Loop (cost=10,000,000,013.67..10,003,462,490.83 rows=260,897 width=373) (actual time=3.200..46,568.079 rows=372,854 loops=1)

10. 31,563.611 40,095.523 ↑ 1.0 425,950 1

Nested Loop Left Join (cost=10,000,000,013.23..10,002,308,346.15 rows=425,958 width=337) (actual time=1.626..40,095.523 rows=425,950 loops=1)

  • Join Filter: (regexp_replace((buyer_subject.id)::text, '^(\d)$'::text, '0\1'::text) = "substring"(party_buyer.inn, 1, 2))
  • Rows Removed by Join Filter: 35878935
11. 421.892 6,402.162 ↑ 1.0 425,950 1

Nested Loop (cost=0.85..1,403,170.63 rows=425,958 width=239) (actual time=1.472..6,402.162 rows=425,950 loops=1)

12. 868.798 868.798 ↑ 1.0 425,956 1

Index Scan using bo_contract_hardwood_deal_bo_party_seller_fkey_idx on bo_contract_hardwood_deal deal (cost=0.42..374,482.43 rows=425,958 width=98) (actual time=0.016..868.798 rows=425,956 loops=1)

  • Filter: actual
  • Rows Removed by Filter: 13
13. 5,111.472 5,111.472 ↑ 1.0 1 425,956

Index Scan using bo_party_pkey on bo_party party_buyer (cost=0.43..2.41 rows=1 width=160) (actual time=0.012..0.012 rows=1 loops=425,956)

  • Index Cond: ((id)::text = (deal.bo_party_buyer_fkey)::text)
14. 2,129.669 2,129.750 ↑ 1.0 85 425,950

Materialize (cost=10,000,000,012.38..10,000,000,014.98 rows=85 width=98) (actual time=0.000..0.005 rows=85 loops=425,950)

15. 0.044 0.081 ↑ 1.0 85 1

Hash Left Join (cost=10,000,000,012.38..10,000,000,014.56 rows=85 width=98) (actual time=0.041..0.081 rows=85 loops=1)

  • Hash Cond: (buyer_subject.bo_federal_district_fkey = buyer_federal_district.id)
16. 0.024 0.024 ↑ 1.0 85 1

Seq Scan on bo_constituent_entity buyer_subject (cost=10,000,000,000.00..10,000,000,001.85 rows=85 width=41) (actual time=0.015..0.024 rows=85 loops=1)

17. 0.004 0.013 ↑ 1.0 9 1

Hash (cost=12.27..12.27 rows=9 width=59) (actual time=0.013..0.013 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.009 0.009 ↑ 1.0 9 1

Index Scan using bo_fed_district_pkey on bo_federal_district buyer_federal_district (cost=0.14..12.27 rows=9 width=59) (actual time=0.006..0.009 rows=9 loops=1)

19. 5,963.300 5,963.300 ↑ 1.0 1 425,950

Index Scan using bo_document_pkey on bo_document_base base (cost=0.43..2.71 rows=1 width=45) (actual time=0.014..0.014 rows=1 loops=425,950)

  • Index Cond: ((id)::text = (deal.bo_document_fkey)::text)
  • Filter: ((contract_num <> 'HLSF-1949'::text) AND (status = 2) AND ((id)::text <> ALL ('{G_45791622,G_168914525,G_254018099,P_2655484}'::text[])))
  • Rows Removed by Filter: 0
20. 0.000 0.000 ↑ 1.3 3 372,854

Materialize (cost=0.13..12.21 rows=4 width=42) (actual time=0.000..0.000 rows=3 loops=372,854)

21. 0.015 0.015 ↑ 1.0 4 1

Index Scan using lu_party_type_pkey on lu_party_type party_buyer_type (cost=0.13..12.19 rows=4 width=42) (actual time=0.007..0.015 rows=4 loops=1)

22. 3,728.540 3,728.540 ↑ 1.0 2 372,854

Index Scan using bo_contract_hardwood_deal__bo_contract_hardwood_fkey on bo_hardwood_deal_details deal_details (cost=0.42..2.89 rows=2 width=29) (actual time=0.010..0.010 rows=2 loops=372,854)

  • Index Cond: ((bo_contract_hardwood_fkey)::text = (deal.id)::text)
23. 906.685 3,934.343 ↑ 1.0 1,951,198 1

Hash (cost=324,371.70..324,371.70 rows=1,951,201 width=200) (actual time=3,934.342..3,934.343 rows=1,951,198 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 507013kB
24. 386.367 3,027.658 ↑ 1.0 1,951,198 1

Nested Loop (cost=7,351.01..324,371.70 rows=1,951,201 width=200) (actual time=13.774..3,027.658 rows=1,951,198 loops=1)

25. 0.019 0.019 ↑ 1.0 4 1

Index Scan using lu_party_type_pkey on lu_party_type party_seller_type (cost=0.13..12.19 rows=4 width=42) (actual time=0.009..0.019 rows=4 loops=1)

26. 2,415.104 2,641.272 ↑ 1.0 487,800 4

Bitmap Heap Scan on bo_party party_seller (cost=7,350.88..76,211.88 rows=487,800 width=160) (actual time=59.942..660.318 rows=487,800 loops=4)

  • Recheck Cond: (party_type_fkey = party_seller_type.id)
  • Heap Blocks: exact=82702
27. 226.168 226.168 ↑ 1.0 487,800 4

Bitmap Index Scan on bo_party_party_type_fkey (cost=0.00..7,228.93 rows=487,800 width=0) (actual time=56.542..56.542 rows=487,800 loops=4)

  • Index Cond: (party_type_fkey = party_seller_type.id)
28. 0.090 1.469 ↑ 1.0 294 1

Hash (cost=330.11..330.11 rows=294 width=166) (actual time=1.468..1.469 rows=294 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 63kB
29. 0.075 1.379 ↑ 1.0 294 1

Nested Loop Left Join (cost=0.42..330.11 rows=294 width=166) (actual time=0.014..1.379 rows=294 loops=1)

30. 1.010 1.010 ↑ 1.0 294 1

Index Scan using lu_tnved_class_pkey on lu_tnved_class deal_details_tnved_class (cost=0.27..126.14 rows=294 width=134) (actual time=0.009..1.010 rows=294 loops=1)

31. 0.294 0.294 ↑ 1.0 1 294

Index Scan using lu_timber_pkey on lu_timber deal_details_timber_tnved (cost=0.15..0.70 rows=1 width=34) (actual time=0.001..0.001 rows=1 loops=294)

  • Index Cond: (id = deal_details_tnved_class.lu_timber_fkey)
32. 0.184 0.257 ↑ 1.0 85 1

Hash (cost=10,000,000,014.56..10,000,000,014.56 rows=85 width=98) (actual time=0.257..0.257 rows=85 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
33. 0.049 0.073 ↑ 1.0 85 1

Hash Left Join (cost=10,000,000,012.38..10,000,000,014.56 rows=85 width=98) (actual time=0.022..0.073 rows=85 loops=1)

  • Hash Cond: (seller_subject.bo_federal_district_fkey = seller_federal_district.id)
34. 0.015 0.015 ↑ 1.0 85 1

Seq Scan on bo_constituent_entity seller_subject (cost=10,000,000,000.00..10,000,000,001.85 rows=85 width=41) (actual time=0.007..0.015 rows=85 loops=1)

35. 0.003 0.009 ↑ 1.0 9 1

Hash (cost=12.27..12.27 rows=9 width=59) (actual time=0.009..0.009 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.006 0.006 ↑ 1.0 9 1

Index Scan using bo_fed_district_pkey on bo_federal_district seller_federal_district (cost=0.14..12.27 rows=9 width=59) (actual time=0.004..0.006 rows=9 loops=1)

37. 0.047 0.172 ↑ 1.0 220 1

Hash (cost=69.20..69.20 rows=220 width=76) (actual time=0.171..0.172 rows=220 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
38. 0.125 0.125 ↑ 1.0 220 1

Index Scan using lu_wood_class_pkey on lu_wood_class deal_details_wood_class (cost=0.14..69.20 rows=220 width=76) (actual time=0.009..0.125 rows=220 loops=1)

39. 0.002 0.007 ↑ 195.0 2 1

Hash (cost=202.00..202.00 rows=390 width=34) (actual time=0.007..0.007 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.005 0.005 ↑ 195.0 2 1

Index Scan using lu_timber_pkey on lu_timber deal_details_timber_okpd (cost=0.15..202.00 rows=390 width=34) (actual time=0.005..0.005 rows=2 loops=1)

41. 0.006 1,443.731 ↑ 1.0 2 1

Hash (cost=20,000,377,261.36..20,000,377,261.36 rows=2 width=1,128) (actual time=1,443.731..1,443.731 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
42. 0.003 1,443.725 ↑ 1.0 2 1

Nested Loop (cost=20,000,000,003.38..20,000,377,261.36 rows=2 width=1,128) (actual time=0.225..1,443.725 rows=2 loops=1)

43. 0.001 1,443.720 ↑ 1.0 2 1

Nested Loop (cost=20,000,000,003.25..20,000,377,261.05 rows=2 width=1,088) (actual time=0.222..1,443.720 rows=2 loops=1)

44. 0.003 1,443.713 ↑ 1.0 2 1

Nested Loop Left Join (cost=20,000,000,003.12..20,000,377,260.75 rows=2 width=1,048) (actual time=0.217..1,443.713 rows=2 loops=1)

45. 0.004 1,443.710 ↑ 1.0 2 1

Nested Loop Left Join (cost=20,000,000,002.97..20,000,377,259.36 rows=2 width=1,016) (actual time=0.215..1,443.710 rows=2 loops=1)

46. 0.004 1,443.706 ↑ 1.0 2 1

Nested Loop Left Join (cost=20,000,000,002.70..20,000,377,257.71 rows=2 width=884) (actual time=0.213..1,443.706 rows=2 loops=1)

47. 0.002 1,443.696 ↑ 1.0 2 1

Nested Loop Left Join (cost=20,000,000,002.55..20,000,377,256.21 rows=2 width=852) (actual time=0.206..1,443.696 rows=2 loops=1)

48. 0.003 1,443.686 ↑ 1.0 2 1

Nested Loop (cost=20,000,000,002.40..20,000,377,254.99 rows=2 width=778) (actual time=0.199..1,443.686 rows=2 loops=1)

49. 0.002 1,443.668 ↑ 1.0 1 1

Nested Loop Left Join (cost=20,000,000,001.98..20,000,377,242.54 rows=1 width=757) (actual time=0.185..1,443.668 rows=1 loops=1)

50. 0.075 1,443.664 ↑ 1.0 1 1

Nested Loop Left Join (cost=20,000,000,001.85..20,000,377,242.21 rows=1 width=700) (actual time=0.182..1,443.664 rows=1 loops=1)

  • Join Filter: (regexp_replace((buyer_subject_1.id)::text, '^(\d)$'::text, '0\1'::text) = "substring"(party_buyer_1.inn, 1, 2))
  • Rows Removed by Join Filter: 84
51. 0.003 1,443.578 ↑ 1.0 1 1

Nested Loop (cost=10,000,000,001.84..10,000,377,238.45 rows=1 width=659) (actual time=0.139..1,443.578 rows=1 loops=1)

52. 0.002 1,443.563 ↑ 1.0 1 1

Nested Loop Left Join (cost=10,000,000,001.42..10,000,377,230.00 rows=1 width=446) (actual time=0.126..1,443.563 rows=1 loops=1)

53. 0.087 1,443.557 ↑ 1.0 1 1

Nested Loop Left Join (cost=10,000,000,001.28..10,000,377,229.68 rows=1 width=389) (actual time=0.120..1,443.557 rows=1 loops=1)

  • Join Filter: (regexp_replace((seller_subject_1.id)::text, '^(\d)$'::text, '0\1'::text) = "substring"(party_seller_1.inn, 1, 2))
  • Rows Removed by Join Filter: 84
54. 0.003 1,443.454 ↑ 1.0 1 1

Nested Loop (cost=1.28..377,225.92 rows=1 width=348) (actual time=0.059..1,443.454 rows=1 loops=1)

55. 0.003 1,443.436 ↑ 1.0 1 1

Nested Loop (cost=0.85..377,217.47 rows=1 width=134) (actual time=0.043..1,443.436 rows=1 loops=1)

56. 1,443.414 1,443.414 ↑ 1.0 1 1

Index Scan using bo_contract_hardwood_deal_220_parent_fkey_id on bo_contract_hardwood_deal deal_1 (cost=0.42..377,209.00 rows=1 width=98) (actual time=0.023..1,443.414 rows=1 loops=1)

  • Filter: (actual AND ((bo_document_fkey)::text = 'P_1833869'::text))
  • Rows Removed by Filter: 425968
57. 0.019 0.019 ↑ 1.0 1 1

Index Scan using bo_document_base_id_status_idx on bo_document_base base_1 (cost=0.43..8.46 rows=1 width=45) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: (((id)::text = 'P_1833869'::text) AND (status = 2))
  • Filter: (contract_num <> 'HLSF-1949'::text)
58. 0.015 0.015 ↑ 1.0 1 1

Index Scan using bo_party_pkey on bo_party party_seller_1 (cost=0.43..8.45 rows=1 width=232) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: ((id)::text = (deal_1.bo_party_seller_fkey)::text)
59. 0.016 0.016 ↑ 1.0 85 1

Seq Scan on bo_constituent_entity seller_subject_1 (cost=10,000,000,000.00..10,000,000,001.85 rows=85 width=41) (actual time=0.010..0.016 rows=85 loops=1)

60. 0.004 0.004 ↑ 1.0 1 1

Index Scan using bo_fed_district_pkey on bo_federal_district seller_federal_district_1 (cost=0.14..0.29 rows=1 width=59) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (id = seller_subject_1.bo_federal_district_fkey)
61. 0.012 0.012 ↑ 1.0 1 1

Index Scan using bo_party_pkey on bo_party party_buyer_1 (cost=0.43..8.45 rows=1 width=232) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((id)::text = (deal_1.bo_party_buyer_fkey)::text)
62. 0.011 0.011 ↑ 1.0 85 1

Seq Scan on bo_constituent_entity buyer_subject_1 (cost=10,000,000,000.00..10,000,000,001.85 rows=85 width=41) (actual time=0.005..0.011 rows=85 loops=1)

63. 0.002 0.002 ↑ 1.0 1 1

Index Scan using bo_fed_district_pkey on bo_federal_district buyer_federal_district_1 (cost=0.14..0.29 rows=1 width=59) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (id = buyer_subject_1.bo_federal_district_fkey)
64. 0.015 0.015 ↑ 1.0 2 1

Index Scan using bo_contract_hardwood_deal__bo_contract_hardwood_fkey on bo_hardwood_deal_details deal_details_1 (cost=0.42..12.44 rows=2 width=29) (actual time=0.012..0.015 rows=2 loops=1)

  • Index Cond: ((bo_contract_hardwood_fkey)::text = (deal_1.id)::text)
65. 0.008 0.008 ↑ 1.0 1 2

Index Scan using lu_wood_class_pkey on lu_wood_class deal_details_wood_class_1 (cost=0.14..0.61 rows=1 width=76) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: (id = deal_details_1.wood_class_fkey)
66. 0.006 0.006 ↑ 1.0 1 2

Index Scan using lu_timber_pkey on lu_timber deal_details_timber_okpd_1 (cost=0.15..0.75 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (id = deal_details_wood_class_1.lu_timber_fkey)
67. 0.000 0.000 ↓ 0.0 0 2

Index Scan using lu_tnved_class_pkey on lu_tnved_class deal_details_tnved_class_1 (cost=0.27..0.82 rows=1 width=134) (actual time=0.000..0.000 rows=0 loops=2)

  • Index Cond: (id = deal_details_1.lu_tnved_class_fkey)
68. 0.000 0.000 ↓ 0.0 0 2

Index Scan using lu_timber_pkey on lu_timber deal_details_timber_tnved_1 (cost=0.15..0.70 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=2)

  • Index Cond: (id = deal_details_tnved_class_1.lu_timber_fkey)
69. 0.006 0.006 ↑ 1.0 1 2

Index Scan using lu_party_type_pkey on lu_party_type party_seller_type_1 (cost=0.13..0.15 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (id = party_seller_1.party_type_fkey)
70. 0.002 0.002 ↑ 1.0 1 2

Index Scan using lu_party_type_pkey on lu_party_type party_buyer_type_1 (cost=0.13..0.15 rows=1 width=42) (actual time=0.001..0.001 rows=1 loops=2)

  • Index Cond: (id = party_buyer_1.party_type_fkey)
Planning time : 777.315 ms
Execution time : 59,840.785 ms