explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Loy

Settings
# exclusive inclusive rows x rows loops node
1. 23,424.351 114,893.876 ↓ 0.0 0 1

Hash Full Join (cost=1,955,459.12..173,751,279.43 rows=94,778 width=1,156) (actual time=114,893.876..114,893.876 rows=0 loops=1)

  • Hash Cond: ((seller_details.deal_deal_pair_fkey)::text = (buyer_details.deal_parent_fkey)::text)
  • Join Filter: ((seller_details.deal_details_wood_class_id = buyer_details.deal_details_wood_class_id) OR (seller_details.deal_details_tnved_id = buyer_details.deal_details_tnved_id))
  • Rows Removed by Join Filter: 18957007
  • Filter: ((NOT COALESCE(seller_details.is_buyer_side, false)) AND COALESCE(buyer_details.is_buyer_side, true) AND (((seller_details.deal_base_id)::text = 'P_1833869'::text) OR ((buyer_details.deal_base_id)::text = 'P_1833869'::text)))
  • Rows Removed by Filter: 6974862
  • Buffers: shared hit=61154031, temp read=1682253 written=1682216
2.          

CTE details

3. 0.000 56,991.660 ↓ 5.3 4,630,138 1

Gather (cost=215,241.00..1,820,527.50 rows=872,942 width=997) (actual time=3,018.493..56,991.660 rows=4,630,138 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=61154031, temp read=108736 written=108730
4. 3,452.635 68,684.731 ↓ 4.2 1,543,379 3

Hash Join (cost=214,241.00..1,729,687.22 rows=363,726 width=997) (actual time=3,173.224..68,684.731 rows=1,543,379 loops=3)

  • Hash Cond: (party_buyer.party_type_fkey = party_buyer_type.id)
  • Buffers: shared hit=61154031, temp read=108736 written=108730
5. 975.002 65,232.053 ↓ 4.2 1,543,379 3

Hash Join (cost=214,239.91..1,725,901.10 rows=363,726 width=1,050) (actual time=3,172.995..65,232.053 rows=1,543,379 loops=3)

  • Hash Cond: (party_seller.party_type_fkey = party_seller_type.id)
  • Buffers: shared hit=61153934, temp read=108736 written=108730
6. 715.697 64,257.040 ↓ 4.2 1,543,379 3

Hash Left Join (cost=214,238.82..1,723,933.61 rows=363,726 width=1,010) (actual time=3,172.970..64,257.040 rows=1,543,379 loops=3)

  • Hash Cond: (deal_details_tnved_class.lu_timber_fkey = deal_details_timber_tnved.id)
  • Buffers: shared hit=61153931, temp read=108736 written=108730
7. 797.081 63,541.332 ↓ 4.2 1,543,379 3

Hash Left Join (cost=214,237.78..1,721,532.63 rows=363,726 width=976) (actual time=3,172.938..63,541.332 rows=1,543,379 loops=3)

  • Hash Cond: (deal_details.lu_tnved_class_fkey = deal_details_tnved_class.id)
  • Buffers: shared hit=61153928, temp read=108736 written=108730
8. 1,096.636 62,743.947 ↓ 4.2 1,543,379 3

Hash Left Join (cost=214,216.16..1,720,555.75 rows=363,726 width=844) (actual time=3,172.612..62,743.947 rows=1,543,379 loops=3)

  • Hash Cond: (deal_details.wood_class_fkey = deal_details_wood_class.id)
  • Buffers: shared hit=61153883, temp read=108736 written=108730
9. 11,141.705 61,647.006 ↓ 4.2 1,543,379 3

Nested Loop (cost=214,199.82..1,715,677.85 rows=363,726 width=736) (actual time=3,172.289..61,647.006 rows=1,543,379 loops=3)

  • Buffers: shared hit=61153853, temp read=108736 written=108730
10. 662.068 50,505.291 ↓ 4.5 1,001,220 3

Hash Left Join (cost=214,199.39..1,583,468.67 rows=223,665 width=714) (actual time=3,172.242..50,505.291 rows=1,001,220 loops=3)

  • Hash Cond: (buyer_subject.bo_federal_district_fkey = buyer_federal_district.id)
  • Buffers: shared hit=48926723, temp read=108736 written=108730
11. 1,407.084 49,843.210 ↓ 4.5 1,001,220 3

Hash Left Join (cost=214,198.19..1,582,603.87 rows=223,665 width=657) (actual time=3,172.220..49,843.210 rows=1,001,220 loops=3)

  • Hash Cond: ("substring"(party_buyer.inn, 1, 2) = regexp_replace((buyer_subject.id)::text, '^(\d)$'::text, '0\1'::text))
  • Buffers: shared hit=48926720, temp read=108736 written=108730
12. 2,535.806 48,435.981 ↓ 4.5 1,001,220 3

Hash Left Join (cost=214,195.28..1,579,693.31 rows=223,665 width=616) (actual time=3,172.054..48,435.981 rows=1,001,220 loops=3)

  • Hash Cond: ("substring"(party_seller.inn, 1, 2) = regexp_replace((seller_subject.id)::text, '^(\d)$'::text, '0\1'::text))
  • Buffers: shared hit=48926717, temp read=108736 written=108730
13. 6,702.769 45,899.880 ↓ 4.5 1,001,220 3

Nested Loop (cost=214,190.84..1,576,781.22 rows=223,665 width=518) (actual time=3,171.726..45,899.880 rows=1,001,220 loops=3)

  • Buffers: shared hit=48926711, temp read=108736 written=108730
14. 9,875.430 39,197.105 ↓ 4.5 1,001,220 3

Nested Loop (cost=214,190.41..1,457,513.79 rows=223,665 width=335) (actual time=3,171.701..39,197.105 rows=1,001,220 loops=3)

  • Buffers: shared hit=36896773, temp read=108736 written=108730
15. 6,856.211 29,321.666 ↓ 4.5 1,001,220 3

Nested Loop (cost=214,189.98..1,338,095.92 rows=223,665 width=334) (actual time=3,171.662..29,321.666 rows=1,001,220 loops=3)

  • Buffers: shared hit=24866847, temp read=108736 written=108730
16. 11,752.161 22,465.449 ↓ 4.5 1,001,220 3

Nested Loop (cost=214,189.55..1,218,828.49 rows=223,665 width=151) (actual time=3,171.640..22,465.449 rows=1,001,220 loops=3)

  • Buffers: shared hit=12841574, temp read=108736 written=108730
17. 3,978.619 10,713.278 ↓ 4.5 1,001,221 3

Hash Join (cost=214,189.12..1,099,410.63 rows=223,665 width=150) (actual time=3,171.566..10,713.278 rows=1,001,221 loops=3)

  • Hash Cond: ((base.id)::text = (deal.bo_document_fkey)::text)
  • Buffers: shared hit=816307, temp read=108736 written=108730
18. 3,571.726 3,571.726 ↓ 1.8 3,262,005 3

Parallel Seq Scan on bo_document_base base (cost=0.00..783,196.68 rows=1,846,108 width=45) (actual time=0.020..3,571.726 rows=3,262,005 loops=3)

  • 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: 5469674
  • Buffers: shared hit=564862
19. 1,644.146 3,162.933 ↓ 1.0 3,174,282 3

Hash (cost=118,712.63..118,712.63 rows=3,174,279 width=115) (actual time=3,162.933..3,162.933 rows=3,174,282 loops=3)

  • Buckets: 4194304 Batches: 2 Memory Usage: 238091kB
  • Buffers: shared hit=251277, temp written=69852
20. 1,518.787 1,518.787 ↓ 1.0 3,174,282 3

Seq Scan on bo_contract_hardwood_deal deal (cost=0.00..118,712.63 rows=3,174,279 width=115) (actual time=0.036..1,518.787 rows=3,174,282 loops=3)

  • Filter: actual
  • Rows Removed by Filter: 320618
  • Buffers: shared hit=251277
21. 0.010 0.010 ↑ 1.0 1 3,003,662

Index Scan using bo_party_pkey on bo_party party_buyer_no_golden (cost=0.43..0.53 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=3,003,662)

  • Index Cond: ((id)::text = (deal.bo_party_buyer_fkey)::text)
  • Buffers: shared hit=12025267
22. 0.006 0.006 ↑ 1.0 1 3,003,661

Index Scan using bo_party_pkey on bo_party party_buyer (cost=0.43..0.53 rows=1 width=203) (actual time=0.006..0.006 rows=1 loops=3,003,661)

  • Index Cond: ((id)::text = (party_buyer_no_golden.bo_party_golden_record_fkey)::text)
  • Buffers: shared hit=12025273
23. 0.009 0.009 ↑ 1.0 1 3,003,661

Index Scan using bo_party_pkey on bo_party party_seller_no_golden (cost=0.43..0.53 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=3,003,661)

  • Index Cond: ((id)::text = (deal.bo_party_seller_fkey)::text)
  • Buffers: shared hit=12029926
24. 0.006 0.006 ↑ 1.0 1 3,003,661

Index Scan using bo_party_pkey on bo_party party_seller (cost=0.43..0.53 rows=1 width=203) (actual time=0.006..0.006 rows=1 loops=3,003,661)

  • Index Cond: ((id)::text = (party_seller_no_golden.bo_party_golden_record_fkey)::text)
  • Buffers: shared hit=12029938
25. 0.207 0.295 ↑ 1.0 85 3

Hash (cost=3.38..3.38 rows=85 width=98) (actual time=0.295..0.295 rows=85 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=6
26. 0.042 0.088 ↑ 1.0 85 3

Hash Left Join (cost=1.20..3.38 rows=85 width=98) (actual time=0.059..0.088 rows=85 loops=3)

  • Hash Cond: (seller_subject.bo_federal_district_fkey = seller_federal_district.id)
  • Buffers: shared hit=6
27. 0.026 0.026 ↑ 1.0 85 3

Seq Scan on bo_constituent_entity seller_subject (cost=0.00..1.85 rows=85 width=41) (actual time=0.020..0.026 rows=85 loops=3)

  • Buffers: shared hit=3
28. 0.007 0.020 ↑ 1.0 9 3

Hash (cost=1.09..1.09 rows=9 width=59) (actual time=0.020..0.020 rows=9 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
29. 0.013 0.013 ↑ 1.0 9 3

Seq Scan on bo_federal_district seller_federal_district (cost=0.00..1.09 rows=9 width=59) (actual time=0.011..0.013 rows=9 loops=3)

  • Buffers: shared hit=3
30. 0.124 0.145 ↑ 1.0 85 3

Hash (cost=1.85..1.85 rows=85 width=41) (actual time=0.145..0.145 rows=85 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=3
31. 0.021 0.021 ↑ 1.0 85 3

Seq Scan on bo_constituent_entity buyer_subject (cost=0.00..1.85 rows=85 width=41) (actual time=0.007..0.021 rows=85 loops=3)

  • Buffers: shared hit=3
32. 0.005 0.013 ↑ 1.0 9 3

Hash (cost=1.09..1.09 rows=9 width=59) (actual time=0.013..0.013 rows=9 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
33. 0.008 0.008 ↑ 1.0 9 3

Seq Scan on bo_federal_district buyer_federal_district (cost=0.00..1.09 rows=9 width=59) (actual time=0.007..0.008 rows=9 loops=3)

  • Buffers: shared hit=3
34. 0.010 0.010 ↑ 1.5 2 3,003,661

Index Scan using bo_contract_hardwood_deal__bo_contract_hardwood_fkey on bo_hardwood_deal_details deal_details (cost=0.43..0.56 rows=3 width=31) (actual time=0.009..0.010 rows=2 loops=3,003,661)

  • Index Cond: ((bo_contract_hardwood_fkey)::text = (deal.id)::text)
  • Buffers: shared hit=12227130
35. 0.102 0.305 ↑ 1.0 220 3

Hash (cost=13.59..13.59 rows=220 width=110) (actual time=0.305..0.305 rows=220 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
  • Buffers: shared hit=30
36. 0.095 0.203 ↑ 1.0 220 3

Hash Left Join (cost=1.04..13.59 rows=220 width=110) (actual time=0.058..0.203 rows=220 loops=3)

  • Hash Cond: (deal_details_wood_class.lu_timber_fkey = deal_details_timber_okpd.id)
  • Buffers: shared hit=30
37. 0.086 0.086 ↑ 1.0 220 3

Seq Scan on lu_wood_class deal_details_wood_class (cost=0.00..11.20 rows=220 width=76) (actual time=0.020..0.086 rows=220 loops=3)

  • Buffers: shared hit=27
38. 0.007 0.022 ↑ 1.0 2 3

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.022..0.022 rows=2 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
39. 0.015 0.015 ↑ 1.0 2 3

Seq Scan on lu_timber deal_details_timber_okpd (cost=0.00..1.02 rows=2 width=36) (actual time=0.014..0.015 rows=2 loops=3)

  • Buffers: shared hit=3
40. 0.122 0.304 ↑ 1.0 294 3

Hash (cost=17.94..17.94 rows=294 width=134) (actual time=0.304..0.304 rows=294 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
  • Buffers: shared hit=45
41. 0.182 0.182 ↑ 1.0 294 3

Seq Scan on lu_tnved_class deal_details_tnved_class (cost=0.00..17.94 rows=294 width=134) (actual time=0.030..0.182 rows=294 loops=3)

  • Buffers: shared hit=45
42. 0.005 0.011 ↑ 1.0 2 3

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.011..0.011 rows=2 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
43. 0.006 0.006 ↑ 1.0 2 3

Seq Scan on lu_timber deal_details_timber_tnved (cost=0.00..1.02 rows=2 width=36) (actual time=0.006..0.006 rows=2 loops=3)

  • Buffers: shared hit=3
44. 0.005 0.011 ↑ 1.0 4 3

Hash (cost=1.04..1.04 rows=4 width=42) (actual time=0.011..0.011 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
45. 0.006 0.006 ↑ 1.0 4 3

Seq Scan on lu_party_type party_seller_type (cost=0.00..1.04 rows=4 width=42) (actual time=0.005..0.006 rows=4 loops=3)

  • Buffers: shared hit=3
46. 0.010 0.043 ↑ 1.0 4 3

Hash (cost=1.04..1.04 rows=4 width=42) (actual time=0.043..0.043 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
47. 0.033 0.033 ↑ 1.0 4 3

Seq Scan on lu_party_type party_buyer_type (cost=0.00..1.04 rows=4 width=42) (actual time=0.032..0.033 rows=4 loops=3)

  • Buffers: shared hit=3
48. 5,466.844 5,466.844 ↓ 5.3 4,630,138 1

CTE Scan on details seller_details (cost=0.00..17,458.84 rows=872,942 width=973) (actual time=0.029..5,466.844 rows=4,630,138 loops=1)

  • Buffers: temp read=520732 written=1
49. 11,425.369 86,002.681 ↓ 5.3 4,630,138 1

Hash (cost=17,458.84..17,458.84 rows=872,942 width=973) (actual time=86,002.681..86,002.681 rows=4,630,138 loops=1)

  • Buckets: 524288 (originally 524288) Batches: 16 (originally 2) Memory Usage: 507905kB
  • Buffers: shared hit=61154031, temp read=108736 written=1120485
50. 74,577.312 74,577.312 ↓ 5.3 4,630,138 1

CTE Scan on details buyer_details (cost=0.00..17,458.84 rows=872,942 width=973) (actual time=3,018.501..74,577.312 rows=4,630,138 loops=1)

  • Buffers: shared hit=61154031, temp read=108736 written=629460
Planning time : 89.999 ms
Execution time : 115,235.636 ms