explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XA32

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 4,840.185 ↑ 1.3 76 1

Limit (cost=730,771.94..730,772.19 rows=100 width=386) (actual time=4,840.165..4,840.185 rows=76 loops=1)

2.          

Initplan (forLimit)

3. 0.003 0.100 ↑ 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.100..0.100 rows=1 loops=1)

4. 0.019 0.019 ↑ 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.016..0.019 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. 19.724 53.188 ↑ 1.0 1 1

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

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

  • Filter: (customer_id IS NOT NULL)
  • Rows Removed by Filter: 24824
8. 0.114 4,840.175 ↑ 1,048.6 76 1

Sort (cost=725,837.52..726,036.76 rows=79,695 width=386) (actual time=4,840.164..4,840.175 rows=76 loops=1)

  • Sort Key: deal.created DESC
  • Sort Method: quicksort Memory: 39kB
9. 109.924 4,840.061 ↑ 1,048.6 76 1

Hash Left Join (cost=511,816.13..722,791.64 rows=79,695 width=386) (actual time=4,389.093..4,840.061 rows=76 loops=1)

  • Hash Cond: (deal.id = action_sell_received_table.deal_id)
10. 107.082 3,773.387 ↑ 1,048.6 76 1

Hash Left Join (cost=388,549.72..586,711.08 rows=79,695 width=381) (actual time=3,431.602..3,773.387 rows=76 loops=1)

  • Hash Cond: (deal.id = action_sell_sent_table.deal_id)
11. 46.240 2,722.755 ↑ 1,048.6 76 1

Hash Left Join (cost=265,450.97..450,838.18 rows=79,695 width=377) (actual time=2,479.148..2,722.755 rows=76 loops=1)

  • Hash Cond: (deal.id = action_buy_received_table.deal_id)
12. 0.077 1,927.859 ↑ 1,048.6 76 1

Hash Left Join (cost=150,725.07..325,488.13 rows=79,695 width=373) (actual time=1,722.699..1,927.859 rows=76 loops=1)

  • Hash Cond: (deal_customer.customer_id = list_customer_by_id.id)
13. 0.073 1,643.429 ↑ 209.7 76 1

Nested Loop Left Join (cost=150,702.32..322,636.20 rows=15,939 width=309) (actual time=1,438.335..1,643.429 rows=76 loops=1)

14. 0.086 1,643.204 ↑ 209.7 76 1

Nested Loop Left Join (cost=150,701.88..253,783.19 rows=15,939 width=305) (actual time=1,438.330..1,643.204 rows=76 loops=1)

15. 17.634 1,641.978 ↑ 209.7 76 1

Hash Join (cost=150,701.45..184,930.17 rows=15,939 width=301) (actual time=1,438.299..1,641.978 rows=76 loops=1)

  • Hash Cond: (deal_state.deal_id = deal.id)
16. 200.110 200.110 ↑ 1,991.7 538 1

Seq Scan on deal_state (cost=0.00..30,051.06 rows=1,071,541 width=165) (actual time=0.149..200.110 rows=538 loops=1)

  • Filter: ((last_status_state <> 'DONE'::text) AND (last_status_state <> 'DISCARDED'::text))
  • Rows Removed by Filter: 1081799
17. 47.098 1,424.234 ↓ 7.4 119,269 1

Hash (cost=150,500.20..150,500.20 rows=16,100 width=140) (actual time=1,424.234..1,424.234 rows=119,269 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3585kB
18. 259.483 1,377.136 ↓ 7.4 119,269 1

Hash Right Join (cost=41,653.92..150,500.20 rows=16,100 width=140) (actual time=507.933..1,377.136 rows=119,269 loops=1)

  • Hash Cond: (action_buy_sent_table.deal_id = deal.id)
19. 610.182 610.182 ↓ 1.0 468,485 1

Seq Scan on deal_actions action_buy_sent_table (cost=0.00..107,023.69 rows=467,481 width=8) (actual time=0.038..610.182 rows=468,485 loops=1)

  • Filter: (action = 'BUY_SENT'::text)
  • Rows Removed by Filter: 4473170
20. 49.237 507.471 ↓ 7.4 119,269 1

Hash (cost=41,452.67..41,452.67 rows=16,100 width=136) (actual time=507.471..507.471 rows=119,269 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3585kB
21. 160.171 458.234 ↓ 7.4 119,269 1

Hash Right Join (cost=36,239.19..41,452.67 rows=16,100 width=136) (actual time=258.527..458.234 rows=119,269 loops=1)

  • Hash Cond: (deal_customer.deal_id = deal.id)
22. 40.012 40.012 ↑ 1.0 198,398 1

Seq Scan on deal_customer (cost=0.00..4,439.98 rows=198,398 width=40) (actual time=0.020..40.012 rows=198,398 loops=1)

23. 50.693 258.051 ↓ 7.4 119,269 1

Hash (cost=36,037.94..36,037.94 rows=16,100 width=100) (actual time=258.051..258.051 rows=119,269 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3585kB
24. 41.573 207.358 ↓ 7.4 119,269 1

Nested Loop Left Join (cost=20,216.07..36,037.94 rows=16,100 width=100) (actual time=47.609..207.358 rows=119,269 loops=1)

  • Join Filter: (unhandled_deals.deal_id = deal.id)
25. 120.259 165.785 ↓ 7.4 119,269 1

Bitmap Heap Scan on deal (cost=20,216.07..35,795.15 rows=16,100 width=96) (actual time=47.588..165.785 rows=119,269 loops=1)

  • Recheck Cond: ((office_id = 8) OR (sender_office_id = 8) OR (receiver_office_id = 8))
  • 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: 6704
  • Heap Blocks: exact=14413
26. 0.003 45.526 ↓ 0.0 0 1

BitmapOr (cost=20,216.07..20,216.07 rows=16,235 width=0) (actual time=45.526..45.526 rows=0 loops=1)

27. 36.323 36.323 ↓ 18.4 99,585 1

Bitmap Index Scan on deal_agent_id_office_id_index (cost=0.00..20,001.96 rows=5,412 width=0) (actual time=36.323..36.323 rows=99,585 loops=1)

  • Index Cond: (office_id = 8)
28. 6.441 6.441 ↓ 20.1 108,742 1

Bitmap Index Scan on deal_sender_office_id_ndx (cost=0.00..101.02 rows=5,412 width=0) (actual time=6.441..6.441 rows=108,742 loops=1)

  • Index Cond: (sender_office_id = 8)
29. 2.759 2.759 ↓ 8.7 46,868 1

Bitmap Index Scan on deal_receiver_office_id_ndx (cost=0.00..101.02 rows=5,412 width=0) (actual time=2.759..2.759 rows=46,868 loops=1)

  • Index Cond: (receiver_office_id = 8)
30. 0.000 0.000 ↓ 0.0 0 119,269

Materialize (cost=0.00..1.29 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=119,269)

31. 0.016 0.016 ↓ 0.0 0 1

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

  • Filter: (office_id = 15)
  • Rows Removed by Filter: 23
32. 1.140 1.140 ↑ 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.015..0.015 rows=1 loops=76)

  • Index Cond: ((deal_id = deal.id) AND (action = 'OFFER_SENT'::text))
33. 0.152 0.152 ↓ 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.002..0.002 rows=0 loops=76)

  • Index Cond: ((deal_id = deal.id) AND (action = 'PAID'::text))
34. 6.991 284.353 ↓ 43.1 43,091 1

Hash (cost=10.25..10.25 rows=1,000 width=68) (actual time=284.353..284.353 rows=43,091 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3050kB
35. 277.362 277.362 ↓ 43.1 43,091 1

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

36. 111.113 748.656 ↑ 1.0 466,942 1

Hash (cost=107,023.69..107,023.69 rows=469,457 width=8) (actual time=748.656..748.656 rows=466,942 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3315kB
37. 637.543 637.543 ↑ 1.0 466,942 1

Seq Scan on deal_actions action_buy_received_table (cost=0.00..107,023.69 rows=469,457 width=8) (actual time=0.031..637.543 rows=466,942 loops=1)

  • Filter: (action = 'BUY_RECEIVED'::text)
  • Rows Removed by Filter: 4474713
38. 221.800 943.550 ↑ 1.0 976,692 1

Hash (cost=107,023.69..107,023.69 rows=979,765 width=8) (actual time=943.550..943.550 rows=976,692 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3417kB
39. 721.750 721.750 ↑ 1.0 976,692 1

Seq Scan on deal_actions action_sell_sent_table (cost=0.00..107,023.69 rows=979,765 width=8) (actual time=0.015..721.750 rows=976,692 loops=1)

  • Filter: (action = 'SELL_SENT'::text)
  • Rows Removed by Filter: 3964963
40. 221.794 956.750 ↑ 1.0 973,759 1

Hash (cost=107,023.69..107,023.69 rows=989,978 width=8) (actual time=956.750..956.750 rows=973,759 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3411kB
41. 734.956 734.956 ↑ 1.0 973,759 1

Seq Scan on deal_actions action_sell_received_table (cost=0.00..107,023.69 rows=989,978 width=8) (actual time=0.017..734.956 rows=973,759 loops=1)

  • Filter: (action = 'SELL_RECEIVED'::text)
  • Rows Removed by Filter: 3967896