explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3kur

Settings
# exclusive inclusive rows x rows loops node
1. 0.855 252,074.299 ↓ 2.0 2 1

Nested Loop (cost=1,173,255.45..3,917,790.00 rows=1 width=1,014) (actual time=233,119.275..252,074.299 rows=2 loops=1)

2.          

CTE param

3. 0.023 0.023 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.008..0.023 rows=1 loops=1)

4. 0.070 252,073.378 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,173,255.29..3,917,789.81 rows=1 width=2,667) (actual time=233,118.417..252,073.378 rows=2 loops=1)

  • Join Filter: (((details.buyer_deal_id)::text = buyer_details.deal_id) OR ((details.seller_deal_id)::text = seller_details.deal_id))
  • Filter: (details.deal_main_deal_number IS NULL)
5. 0.083 252,073.268 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,173,255.29..3,917,778.31 rows=1 width=2,667) (actual time=233,118.367..252,073.268 rows=2 loops=1)

6. 0.097 252,071.201 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,173,255.16..3,917,777.96 rows=1 width=2,638) (actual time=233,116.359..252,071.201 rows=2 loops=1)

7. 0.097 252,071.032 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,173,255.01..3,917,777.80 rows=1 width=2,634) (actual time=233,116.248..252,071.032 rows=2 loops=1)

8. 5,575.993 252,069.777 ↓ 2.0 2 1

Nested Loop (cost=1,173,254.87..3,917,777.63 rows=1 width=2,630) (actual time=233,115.060..252,069.777 rows=2 loops=1)

  • Join Filter: ((seller_details.deal_base_id = param.base_id) OR (buyer_details.deal_base_id = param.base_id))
  • Rows Removed by Join Filter: 685596
9. 0.058 0.058 ↑ 1.0 1 1

CTE Scan on param (cost=0.00..0.02 rows=1 width=32) (actual time=0.028..0.058 rows=1 loops=1)

10. 23,893.937 246,493.726 ↓ 5.4 685,598 1

Hash Full Join (cost=1,173,254.87..3,915,889.37 rows=125,883 width=2,630) (actual time=122,495.889..246,493.726 rows=685,598 loops=1)

  • Hash Cond: (seller_details.deal_deal_pair_fkey = buyer_details.deal_parent_fkey)
  • Join Filter: ((NOT COALESCE(seller_details.is_buyer_side, false)) AND COALESCE(buyer_details.is_buyer_side, true) AND ((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: 15162548
  • Filter: ((NOT COALESCE(seller_details.is_buyer_side, false)) AND COALESCE(buyer_details.is_buyer_side, true))
  • Rows Removed by Filter: 711958
11. 102,192.339 102,192.339 ↓ 1.4 711,958 1

Foreign Scan on v_plain_an_deal_details seller_details (cost=528,946.17..581,465.56 rows=503,531 width=890) (actual time=2,088.348..102,192.339 rows=711,958 loops=1)

12. 9,725.842 120,407.450 ↓ 1.4 711,958 1

Hash (cost=581,465.56..581,465.56 rows=503,531 width=890) (actual time=120,407.443..120,407.450 rows=711,958 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 1024 (originally 128) Memory Usage: 4033kB
13. 110,681.608 110,681.608 ↓ 1.4 711,958 1

Foreign Scan on v_plain_an_deal_details buyer_details (cost=528,946.17..581,465.56 rows=503,531 width=890) (actual time=445.290..110,681.608 rows=711,958 loops=1)

14. 1.158 1.158 ↑ 1.0 1 2

Index Scan using rel_wood_breed_bars_wood_class_lu_wood_class_fkey_key on rel_wood_breed_bars_wood_class rel_okpd (cost=0.14..0.16 rows=1 width=6) (actual time=0.575..0.579 rows=1 loops=2)

  • Index Cond: (lu_wood_class_fkey = COALESCE(seller_details.deal_details_wood_class_id, buyer_details.deal_details_wood_class_id))
15. 0.072 0.072 ↓ 0.0 0 2

Index Scan using rel_wood_breed_bars_tnved_class_lu_tnved_class_fkey_key on rel_wood_breed_bars_tnved_class rel_tnved (cost=0.14..0.16 rows=1 width=6) (actual time=0.033..0.036 rows=0 loops=2)

  • Index Cond: (lu_tnved_class_fkey = COALESCE(seller_details.deal_details_wood_class_id, buyer_details.deal_details_wood_class_id))
16. 1.984 1.984 ↑ 1.0 1 2

Index Scan using lu_wood_breed_bars_pkey on lu_wood_breed_bars deal_details_wood_breed (cost=0.14..0.34 rows=1 width=37) (actual time=0.988..0.992 rows=1 loops=2)

  • Index Cond: (id = COALESCE(rel_tnved.lu_wood_breed_bars, rel_okpd.lu_wood_breed_bars))
17. 0.040 0.040 ↓ 0.0 0 2

Seq Scan on pl_analytical_deal_details details (cost=0.00..10.60 rows=60 width=96) (actual time=0.016..0.020 rows=0 loops=2)

18. 0.066 0.066 ↑ 1.0 1 2

Index Scan using rel_sort_name_by_okp_code_pkey on rel_sort_name_by_okp_code bars_sort (cost=0.14..0.16 rows=1 width=106) (actual time=0.029..0.033 rows=1 loops=2)

  • Index Cond: (okp_code = COALESCE(seller_details.deal_details_wood_class_okp_code, buyer_details.deal_details_wood_class_okp_code))
Planning time : 340.187 ms
Execution time : 252,110.581 ms