explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YMbn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,968.214 ↑ 1.3 76 1

Limit (cost=4,939.32..6,753.87 rows=100 width=386) (actual time=240.640..3,968.214 rows=76 loops=1)

2.          

Initplan (forLimit)

3. 0.008 0.149 ↑ 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.149..0.149 rows=1 loops=1)

4. 0.024 0.024 ↑ 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.021..0.024 rows=39 loops=1)

5. 0.117 0.117 ↑ 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.003..0.003 rows=1 loops=39)

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

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

7. 44.016 44.016 ↑ 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.010..44.016 rows=173,574 loops=1)

  • Filter: (customer_id IS NOT NULL)
  • Rows Removed by Filter: 24824
8. 97.247 3,968.177 ↑ 1,048.6 76 1

Merge Left Join (cost=4.90..1,446,111.93 rows=79,695 width=386) (actual time=240.640..3,968.177 rows=76 loops=1)

  • Merge Cond: (deal.id = action_sell_received_table.deal_id)
9. 99.459 3,344.670 ↑ 1,048.6 76 1

Merge Left Join (cost=4.46..1,171,324.12 rows=79,695 width=381) (actual time=239.699..3,344.670 rows=76 loops=1)

  • Merge Cond: (deal.id = action_sell_sent_table.deal_id)
10. 52.339 2,644.144 ↑ 1,048.6 76 1

Merge Left Join (cost=4.03..896,723.81 rows=79,695 width=377) (actual time=238.868..2,644.144 rows=76 loops=1)

  • Merge Cond: (deal.id = action_buy_received_table.deal_id)
11. 0.373 1,944.490 ↑ 1,048.6 76 1

Nested Loop Left Join (cost=3.60..631,506.65 rows=79,695 width=373) (actual time=237.064..1,944.490 rows=76 loops=1)

12. 0.128 1,181.077 ↑ 209.7 76 1

Nested Loop Left Join (cost=2.15..410,750.06 rows=15,939 width=273) (actual time=13.550..1,181.077 rows=76 loops=1)

13. 0.112 1,180.797 ↑ 209.7 76 1

Nested Loop Left Join (cost=1.72..297,564.10 rows=15,939 width=269) (actual time=13.547..1,180.797 rows=76 loops=1)

14. 0.150 1,180.457 ↑ 209.7 76 1

Nested Loop Left Join (cost=1.29..228,711.09 rows=15,939 width=265) (actual time=13.543..1,180.457 rows=76 loops=1)

15. 0.063 1,178.787 ↑ 209.7 76 1

Nested Loop Left Join (cost=0.85..159,858.07 rows=15,939 width=261) (actual time=13.517..1,178.787 rows=76 loops=1)

  • Join Filter: (unhandled_deals.deal_id = deal.id)
16. 18.965 1,178.648 ↑ 209.7 76 1

Nested Loop (cost=0.85..159,617.70 rows=15,939 width=257) (actual time=13.501..1,178.648 rows=76 loops=1)

17. 801.876 801.876 ↓ 7.4 119,269 1

Index Scan using deal_pkey on deal (cost=0.43..99,796.70 rows=16,100 width=96) (actual time=0.524..801.876 rows=119,269 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: 963068
18. 357.807 357.807 ↓ 0.0 0 119,269

Index Scan using deal_state_pkey on deal_state (cost=0.43..3.72 rows=1 width=165) (actual time=0.003..0.003 rows=0 loops=119,269)

  • Index Cond: (deal_id = deal.id)
  • Filter: ((last_status_state <> 'DONE'::text) AND (last_status_state <> 'DISCARDED'::text))
  • Rows Removed by Filter: 1
19. 0.063 0.076 ↓ 0.0 0 76

Materialize (cost=0.00..1.29 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=76)

20. 0.013 0.013 ↓ 0.0 0 1

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

  • Filter: (office_id = 15)
  • Rows Removed by Filter: 23
21. 1.520 1.520 ↑ 2.0 1 76

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.020..0.020 rows=1 loops=76)

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

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=0 loops=76)

  • Index Cond: ((deal_id = deal.id) AND (action = 'PAID'::text))
23. 0.152 0.152 ↓ 0.0 0 76

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.002..0.002 rows=0 loops=76)

  • Index Cond: ((deal_id = deal.id) AND (action = 'BUY_SENT'::text))
24. 306.871 763.040 ↑ 5.0 1 76

Hash Right Join (cost=1.45..15.25 rows=5 width=104) (actual time=8.569..10.040 rows=1 loops=76)

  • Hash Cond: (list_customer_by_id.id = deal_customer.customer_id)
25. 454.725 454.725 ↓ 43.1 43,091 75

Function Scan on list_customer_by_id (cost=0.25..10.25 rows=1,000 width=68) (actual time=2.952..6.063 rows=43,091 loops=75)

26. 0.228 1.444 ↑ 1.0 1 76

Hash (cost=1.18..1.18 rows=1 width=40) (actual time=0.019..0.019 rows=1 loops=76)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 1.216 1.216 ↑ 1.0 1 76

Index Scan using deal_customer_deal_id_ndx on deal_customer (cost=0.42..1.18 rows=1 width=40) (actual time=0.016..0.016 rows=1 loops=76)

  • Index Cond: (deal_id = deal.id)
28. 647.315 647.315 ↑ 1.0 466,905 1

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_buy_received_table (cost=0.43..263,498.60 rows=469,457 width=8) (actual time=0.027..647.315 rows=466,905 loops=1)

  • Index Cond: (action = 'BUY_RECEIVED'::text)
29. 601.067 601.067 ↑ 1.0 976,638 1

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_sell_sent_table (cost=0.43..271,230.24 rows=979,765 width=8) (actual time=0.009..601.067 rows=976,638 loops=1)

  • Index Cond: (action = 'SELL_SENT'::text)
30. 526.260 526.260 ↑ 1.0 973,705 1

Index Scan using deal_actions_deal_id_action_uindex on deal_actions action_sell_received_table (cost=0.43..271,384.69 rows=989,978 width=8) (actual time=0.006..526.260 rows=973,705 loops=1)

  • Index Cond: (action = 'SELL_RECEIVED'::text)