explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TCUu

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

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

2.          

Initplan (forLimit)

3. 0.002 0.096 ↑ 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.096..0.096 rows=1 loops=1)

4. 0.016 0.016 ↑ 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.014..0.016 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. 20.608 54.968 ↑ 1.0 1 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

14. 0.062 1,601.529 ↑ 209.7 76 1

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

15. 19.145 1,600.631 ↑ 209.7 76 1

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

  • Hash Cond: (deal_state.deal_id = deal.id)
16. 195.320 195.320 ↑ 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.138..195.320 rows=538 loops=1)

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

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

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

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

  • Hash Cond: (action_buy_sent_table.deal_id = deal.id)
19. 603.811 603.811 ↓ 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.029..603.811 rows=468,485 loops=1)

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

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

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

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

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

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

23. 51.879 260.036 ↓ 7.4 119,269 1

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

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

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

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

Bitmap Heap Scan on deal (cost=20,216.07..35,795.15 rows=16,100 width=96) (actual time=55.765..165.308 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 53.633 ↓ 0.0 0 1

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

27. 44.226 44.226 ↓ 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=44.226..44.226 rows=99,585 loops=1)

  • Index Cond: (office_id = 8)
28. 5.929 5.929 ↓ 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=5.929..5.929 rows=108,742 loops=1)

  • Index Cond: (sender_office_id = 8)
29. 3.475 3.475 ↓ 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=3.475..3.475 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. 0.836 0.836 ↑ 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.011..0.011 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. 8.860 296.127 ↓ 43.1 43,091 1

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

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

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

36. 108.094 729.535 ↑ 1.0 466,942 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 3315kB
37. 621.441 621.441 ↑ 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..621.441 rows=466,942 loops=1)

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

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

  • Buckets: 131072 Batches: 16 Memory Usage: 3417kB
39. 691.197 691.197 ↑ 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.017..691.197 rows=976,692 loops=1)

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

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

  • Buckets: 131072 Batches: 16 Memory Usage: 3411kB
41. 706.089 706.089 ↑ 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.015..706.089 rows=973,759 loops=1)

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