explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dH6e

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 520,733.100 ↑ 1.3 76 1

Limit (cost=4,939.32..7,064.20 rows=100 width=386) (actual time=281.233..520,733.100 rows=76 loops=1)

2.          

Initplan (forLimit)

3. 0.028 0.127 ↑ 1,478,142.0 1 1

Nested Loop Anti Join (cost=0.28..942,815.73 rows=1,478,142 width=0) (actual time=0.127..0.127 rows=1 loops=1)

4. 0.021 0.021 ↑ 75,802.1 39 1

Seq Scan on deal_row (cost=0.00..76,837.83 rows=2,956,283 width=32) (actual time=0.017..0.021 rows=39 loops=1)

5. 0.078 0.078 ↑ 1.0 1 39

Index Only Scan using product_article_code_pk on product_article (cost=0.28..0.29 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=39)

  • Index Cond: (code = deal_row.code)
  • Heap Fetches: 38
6. 24.022 65.233 ↑ 1.0 1 1

Aggregate (cost=4,933.50..4,933.51 rows=1 width=32) (actual time=65.233..65.233 rows=1 loops=1)

7. 41.211 41.211 ↑ 1.1 173,574 1

Seq Scan on deal_customer deal_customer_1 (cost=0.00..4,439.98 rows=197,406 width=4) (actual time=0.008..41.211 rows=173,574 loops=1)

  • Filter: (customer_id IS NOT NULL)
  • Rows Removed by Filter: 24824
8. 0.361 520,733.050 ↑ 1,048.6 76 1

Nested Loop Left Join (cost=4.90..1,693,471.38 rows=79,697 width=386) (actual time=281.232..520,733.050 rows=76 loops=1)

9. 0.257 520,732.461 ↑ 1,048.6 76 1

Nested Loop Left Join (cost=4.46..1,349,197.66 rows=79,697 width=381) (actual time=281.086..520,732.461 rows=76 loops=1)

10. 64.276 520,731.368 ↑ 1,048.6 76 1

Merge Join (cost=4.03..783,253.67 rows=79,697 width=377) (actual time=281.066..520,731.368 rows=76 loops=1)

  • Merge Cond: (deal.id = deal_state.deal_id)
11. 348.255 519,854.823 ↓ 1.5 118,111 1

Nested Loop Left Join (cost=3.60..690,781.43 rows=80,500 width=216) (actual time=0.105..519,854.823 rows=118,111 loops=1)

12. 113.783 9,030.826 ↓ 7.3 118,111 1

Nested Loop Left Join (cost=2.16..467,794.99 rows=16,100 width=116) (actual time=0.069..9,030.826 rows=118,111 loops=1)

13. 77.640 8,562.710 ↓ 7.3 118,111 1

Nested Loop Left Join (cost=1.73..353,465.74 rows=16,100 width=112) (actual time=0.057..8,562.710 rows=118,111 loops=1)

14. 124.167 8,130.737 ↓ 7.3 118,111 1

Nested Loop Left Join (cost=1.29..239,136.49 rows=16,100 width=108) (actual time=0.054..8,130.737 rows=118,111 loops=1)

15. 227.218 7,652.237 ↓ 7.3 118,111 1

Nested Loop Left Join (cost=0.86..169,587.99 rows=16,100 width=104) (actual time=0.049..7,652.237 rows=118,111 loops=1)

16. 185.324 5,535.243 ↓ 7.3 118,111 1

Nested Loop Left Join (cost=0.43..100,039.49 rows=16,100 width=100) (actual time=0.038..5,535.243 rows=118,111 loops=1)

  • Join Filter: (unhandled_deals.deal_id = deal.id)
  • Rows Removed by Join Filter: 118110
17. 5,349.919 5,349.919 ↓ 7.3 118,111 1

Index Scan Backward using deal_pkey on deal (cost=0.43..99,796.70 rows=16,100 width=96) (actual time=0.023..5,349.919 rows=118,111 loops=1)

  • Filter: ((office_id = 8) OR ((type <> 'AGENT'::text) AND (sender_office_id = 8)) OR ((type <> 'AGENT'::text) AND (receiver_office_id = 8)))
  • Rows Removed by Filter: 961057
18. 0.000 0.000 ↑ 1.0 1 118,111

Materialize (cost=0.00..1.29 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=118,111)

19. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on unhandled_deals (cost=0.00..1.29 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1)

  • Filter: (office_id = 8)
  • Rows Removed by Filter: 22
20. 1,889.776 1,889.776 ↑ 2.0 1 118,111

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_offer_sent_table (cost=0.43..8.21 rows=2 width=8) (actual time=0.016..0.016 rows=1 loops=118,111)

  • Index Cond: ((deal_id = deal.id) AND (action = 'OFFER_SENT'::text))
21. 354.333 354.333 ↑ 2.0 1 118,111

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_paid_table (cost=0.43..8.21 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=118,111)

  • Index Cond: ((deal_id = deal.id) AND (action = 'PAID'::text))
22. 354.333 354.333 ↓ 0.0 0 118,111

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_buy_sent_table (cost=0.43..7.10 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=118,111)

  • Index Cond: ((deal_id = deal.id) AND (action = 'BUY_SENT'::text))
23. 354.333 354.333 ↓ 0.0 0 118,111

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_buy_received_table (cost=0.43..7.10 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=118,111)

  • Index Cond: ((deal_id = deal.id) AND (action = 'BUY_RECEIVED'::text))
24. 294,140.125 510,475.742 ↓ 0.0 0 118,111

Hash Right Join (cost=1.45..15.25 rows=5 width=104) (actual time=3.546..4.322 rows=0 loops=118,111)

  • Hash Cond: (list_customer_by_id.id = deal_customer.customer_id)
25. 215,508.840 215,508.840 ↓ 43.1 43,091 50,165

Function Scan on list_customer_by_id (cost=0.25..10.25 rows=1,000 width=68) (actual time=0.006..4.296 rows=43,091 loops=50,165)

26. 236.222 826.777 ↓ 0.0 0 118,111

Hash (cost=1.18..1.18 rows=1 width=40) (actual time=0.007..0.007 rows=0 loops=118,111)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 590.555 590.555 ↓ 0.0 0 118,111

Index Scan using deal_customer_deal_id_ndx on deal_customer (cost=0.42..1.18 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=118,111)

  • Index Cond: (deal_id = deal.id)
28. 812.269 812.269 ↑ 1,991.7 538 1

Index Scan Backward using deal_state_pkey on deal_state (cost=0.43..88,795.17 rows=1,071,541 width=165) (actual time=0.039..812.269 rows=538 loops=1)

  • Filter: ((last_status_state <> 'DONE'::text) AND (last_status_state <> 'DISCARDED'::text))
  • Rows Removed by Filter: 1081799
29. 0.836 0.836 ↓ 0.0 0 76

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_sell_sent_table (cost=0.43..7.10 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=76)

  • Index Cond: ((deal_id = deal.id) AND (action = 'SELL_SENT'::text))
30. 0.228 0.228 ↓ 0.0 0 76

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_sell_received_table (cost=0.43..8.21 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=76)

  • Index Cond: ((deal_id = deal.id) AND (action = 'SELL_RECEIVED'::text))