explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2LnX

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 8,066.562 ↑ 1,048.6 76 1

Merge Join (cost=687,209.76..780,084.07 rows=79,697 width=386) (actual time=7,410.432..8,066.562 rows=76 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, COALESCE(list_customer_by_id.name, deal_customer.name), list_customer_by_id.name_alias, deal_state.last_status_state, deal_state.total_sell, deal_state.total_buy, deal_state.total_tax_sell, deal_state.total_tax_buy, deal_state.suspicious, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id, action_sell_sent_table.office_id, action_sell_received_table.office_id, unhandled_deals.deal_id, (NOT $1)
  • Merge Cond: (deal_state.deal_id = deal.id)
  • Buffers: shared hit=1662019 read=368783 written=5, temp read=20221 written=20147
2.          

Initplan (forMerge Join)

3. 0.029 1.432 ↑ 1,478,142.0 1 1

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

  • Buffers: shared hit=111 read=10
4. 0.389 0.389 ↑ 75,802.1 39 1

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

  • Output: deal_row.id, deal_row.deal_id, deal_row.price, deal_row.quantity, deal_row.price_applied, deal_row.product_article_type, deal_row.code, deal_row.name, deal_row.total, deal_row.total_applied, deal_row.product_spot_eur, deal_row.currency_spot_rate_eur, deal_row.tax_percent, deal_row.tax_type, deal_row.tax_amount, deal_row.product_currency_spot_eur, deal_row.row_index_in_ui
  • Buffers: shared read=1
5. 1.014 1.014 ↑ 1.0 1 39

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

  • Output: product_article.code
  • Index Cond: (product_article.code = deal_row.code)
  • Heap Fetches: 38
  • Buffers: shared hit=111 read=9
6. 22.926 60.511 ↑ 1.0 1 1

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

  • Output: array_agg(deal_customer_1.customer_id)
  • Buffers: shared hit=2456
7. 37.585 37.585 ↑ 1.1 173,574 1

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

  • Output: deal_customer_1.deal_id, deal_customer_1.type, deal_customer_1.name, deal_customer_1.id_code, deal_customer_1.email, deal_customer_1.phone, deal_customer_1.address, deal_customer_1.postcode, deal_customer_1.city, deal_customer_1.country_code, deal_customer_1.comment, deal_customer_1.customer_id
  • Filter: (deal_customer_1.customer_id IS NOT NULL)
  • Rows Removed by Filter: 24824
  • Buffers: shared hit=2456
8. 598.046 598.046 ↑ 1,991.7 538 1

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

  • Output: deal_state.last_status_state, deal_state.total_sell, deal_state.total_buy, deal_state.total_tax_sell, deal_state.total_tax_buy, deal_state.suspicious, deal_state.deal_id
  • Filter: ((deal_state.last_status_state <> 'DONE'::text) AND (deal_state.last_status_state <> 'DISCARDED'::text))
  • Rows Removed by Filter: 1081799
  • Buffers: shared hit=783095 read=16775
9. 18.651 7,451.559 ↓ 1.5 118,111 1

Materialize (cost=682,274.91..682,677.41 rows=80,500 width=224) (actual time=7,408.897..7,451.559 rows=118,111 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, list_customer_by_id.name, list_customer_by_id.name_alias, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id, action_sell_sent_table.office_id, action_sell_received_table.office_id
  • Buffers: shared hit=878813 read=351998 written=5, temp read=20221 written=20147
10. 122.921 7,432.908 ↓ 1.5 118,111 1

Sort (cost=682,274.91..682,476.16 rows=80,500 width=224) (actual time=7,408.894..7,432.908 rows=118,111 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, list_customer_by_id.name, list_customer_by_id.name_alias, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id, action_sell_sent_table.office_id, action_sell_received_table.office_id
  • Sort Key: deal.id DESC
  • Sort Method: external merge Disk: 10152kB
  • Buffers: shared hit=878813 read=351998 written=5, temp read=20221 written=20147
11. 217.327 7,309.987 ↓ 1.5 119,269 1

Hash Left Join (cost=397,052.00..667,182.49 rows=80,500 width=224) (actual time=3,652.114..7,309.987 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, list_customer_by_id.name, list_customer_by_id.name_alias, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id, action_sell_sent_table.office_id, action_sell_received_table.office_id
  • Inner Unique: true
  • Hash Cond: (deal.id = action_sell_received_table.deal_id)
  • Buffers: shared hit=878813 read=351998 written=5, temp read=18952 written=18874
12. 275.353 6,135.832 ↓ 1.5 119,269 1

Hash Left Join (cost=273,785.59..534,155.77 rows=80,500 width=220) (actual time=2,694.671..6,135.832 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, list_customer_by_id.name, list_customer_by_id.name_alias, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id, action_sell_sent_table.office_id
  • Inner Unique: true
  • Hash Cond: (deal.id = action_sell_sent_table.deal_id)
  • Buffers: shared hit=878735 read=306823 written=5, temp read=14505 written=14457
13. 55.185 4,921.772 ↓ 1.5 119,269 1

Hash Left Join (cost=150,686.84..401,494.70 rows=80,500 width=216) (actual time=1,755.735..4,921.772 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, list_customer_by_id.name, list_customer_by_id.name_alias, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id
  • Hash Cond: (deal_customer.customer_id = list_customer_by_id.id)
  • Buffers: shared hit=878690 read=261615 written=5, temp read=10089 written=10071
14. 90.750 4,567.537 ↓ 7.4 119,269 1

Nested Loop Left Join (cost=150,664.09..398,614.20 rows=16,100 width=152) (actual time=1,456.669..4,567.537 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id
  • Inner Unique: true
  • Buffers: shared hit=768276 read=260486 written=5, temp read=10089 written=10071
15. 55.806 4,238.249 ↓ 7.4 119,269 1

Nested Loop Left Join (cost=150,663.66..329,065.70 rows=16,100 width=148) (actual time=1,456.659..4,238.249 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, action_offer_sent_table.office_id, action_buy_sent_table.office_id, action_buy_received_table.office_id
  • Inner Unique: true
  • Buffers: shared hit=315771 read=259151 written=5, temp read=10089 written=10071
16. 275.432 2,393.408 ↓ 7.4 119,269 1

Hash Right Join (cost=150,663.22..259,517.20 rows=16,100 width=144) (actual time=1,456.628..2,393.408 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, action_buy_sent_table.office_id, action_buy_received_table.office_id
  • Hash Cond: (action_buy_received_table.deal_id = deal.id)
  • Buffers: shared hit=1231 read=109536 written=5, temp read=10089 written=10071
17. 661.947 661.947 ↑ 1.0 466,942 1

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

  • Output: action_buy_received_table.id, action_buy_received_table.deal_id, action_buy_received_table.action, action_buy_received_table.user_id, action_buy_received_table.office_id, action_buy_received_table.created, action_buy_received_table.identification_id, action_buy_received_table.representative_name, action_buy_received_table.identification_level
  • Filter: (action_buy_received_table.action = 'BUY_RECEIVED'::text)
  • Rows Removed by Filter: 4474713
  • Buffers: shared hit=96 read=45157
18. 51.034 1,456.029 ↓ 7.4 119,269 1

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

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, action_buy_sent_table.office_id
  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3585kB
  • Buffers: shared hit=1135 read=64379 written=5, temp read=6327 written=7153
19. 259.024 1,404.995 ↓ 7.4 119,269 1

Hash Right Join (cost=41,615.69..150,461.97 rows=16,100 width=140) (actual time=506.648..1,404.995 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id, action_buy_sent_table.office_id
  • Hash Cond: (action_buy_sent_table.deal_id = deal.id)
  • Buffers: shared hit=1135 read=64379 written=5, temp read=6327 written=6315
20. 639.831 639.831 ↓ 1.0 468,485 1

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

  • Output: action_buy_sent_table.id, action_buy_sent_table.deal_id, action_buy_sent_table.action, action_buy_sent_table.user_id, action_buy_sent_table.office_id, action_buy_sent_table.created, action_buy_sent_table.identification_id, action_buy_sent_table.representative_name, action_buy_sent_table.identification_level
  • Filter: (action_buy_sent_table.action = 'BUY_SENT'::text)
  • Rows Removed by Filter: 4473170
  • Buffers: shared read=45253
21. 49.269 506.140 ↓ 7.4 119,269 1

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

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id
  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3585kB
  • Buffers: shared hit=1135 read=19126 written=5, temp read=2573 written=3387
22. 162.575 456.871 ↓ 7.4 119,269 1

Hash Right Join (cost=36,200.96..41,414.44 rows=16,100 width=136) (actual time=261.249..456.871 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, deal_customer.customer_id, deal_customer.name, unhandled_deals.deal_id
  • Hash Cond: (deal_customer.deal_id = deal.id)
  • Buffers: shared hit=1135 read=19126 written=5, temp read=2573 written=2567
23. 34.399 34.399 ↑ 1.0 198,398 1

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

  • Output: deal_customer.deal_id, deal_customer.type, deal_customer.name, deal_customer.id_code, deal_customer.email, deal_customer.phone, deal_customer.address, deal_customer.postcode, deal_customer.city, deal_customer.country_code, deal_customer.comment, deal_customer.customer_id
  • Buffers: shared hit=979 read=1477
24. 55.803 259.897 ↓ 7.4 119,269 1

Hash (cost=35,999.71..35,999.71 rows=16,100 width=100) (actual time=259.897..259.897 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, unhandled_deals.deal_id
  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3585kB
  • Buffers: shared hit=156 read=17649 written=5, temp written=742
25. 29.697 204.094 ↓ 7.4 119,269 1

Hash Left Join (cost=20,217.37..35,999.71 rows=16,100 width=100) (actual time=55.792..204.094 rows=119,269 loops=1)

  • Output: deal.id, deal.agent_deal_id, deal.type, deal.invoice_no, deal.channel, deal.lock_user_id, deal.created, deal.sender_office_id, deal.receiver_office_id, unhandled_deals.deal_id
  • Inner Unique: true
  • Hash Cond: (deal.id = unhandled_deals.deal_id)
  • Buffers: shared hit=156 read=17649 written=5
26. 120.697 174.385 ↓ 7.4 119,269 1

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

  • Output: deal.id, deal.invoice_no, deal.group_id, deal.receiver_office_id, deal.channel, deal.tax_region, deal.sender_office_id, deal.lock_user_id, deal.customer_comment, deal.agent_deal_id, deal.type, deal.office_id, deal.agent_id, deal.created, deal.invoiced_date_time, deal.estimated_stock_movement
  • Recheck Cond: ((deal.office_id = 8) OR (deal.sender_office_id = 8) OR (deal.receiver_office_id = 8))
  • Filter: ((deal.office_id = 8) OR ((deal.type <> 'AGENT'::text) AND (deal.sender_office_id = 8)) OR ((deal.type <> 'AGENT'::text) AND (deal.receiver_office_id = 8)))
  • Rows Removed by Filter: 6704
  • Heap Blocks: exact=14413
  • Buffers: shared hit=155 read=17649 written=5
27. 0.003 53.688 ↓ 0.0 0 1

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

  • Buffers: shared read=3391
28. 42.431 42.431 ↓ 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=42.431..42.431 rows=99,585 loops=1)

  • Index Cond: (deal.office_id = 8)
  • Buffers: shared read=2960
29. 7.941 7.941 ↓ 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=7.941..7.941 rows=108,742 loops=1)

  • Index Cond: (deal.sender_office_id = 8)
  • Buffers: shared read=300
30. 3.313 3.313 ↓ 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.313..3.313 rows=46,868 loops=1)

  • Index Cond: (deal.receiver_office_id = 8)
  • Buffers: shared read=131
31. 0.002 0.012 ↑ 1.0 1 1

Hash (cost=1.29..1.29 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=1)

  • Output: unhandled_deals.deal_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
32. 0.010 0.010 ↑ 1.0 1 1

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

  • Output: unhandled_deals.deal_id
  • Filter: (unhandled_deals.office_id = 8)
  • Rows Removed by Filter: 22
  • Buffers: shared hit=1
33. 1,789.035 1,789.035 ↑ 2.0 1 119,269

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_offer_sent_table (cost=0.43..8.21 rows=2 width=8) (actual time=0.015..0.015 rows=1 loops=119,269)

  • Output: action_offer_sent_table.id, action_offer_sent_table.deal_id, action_offer_sent_table.action, action_offer_sent_table.user_id, action_offer_sent_table.office_id, action_offer_sent_table.created, action_offer_sent_table.identification_id, action_offer_sent_table.representative_name, action_offer_sent_table.identification_level
  • Index Cond: ((action_offer_sent_table.deal_id = deal.id) AND (action_offer_sent_table.action = 'OFFER_SENT'::text))
  • Buffers: shared hit=314540 read=149615
34. 238.538 238.538 ↑ 2.0 1 119,269

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_paid_table (cost=0.43..8.21 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=119,269)

  • Output: action_paid_table.id, action_paid_table.deal_id, action_paid_table.action, action_paid_table.user_id, action_paid_table.office_id, action_paid_table.created, action_paid_table.identification_id, action_paid_table.representative_name, action_paid_table.identification_level
  • Index Cond: ((action_paid_table.deal_id = deal.id) AND (action_paid_table.action = 'PAID'::text))
  • Buffers: shared hit=452505 read=1335
35. 8.086 299.050 ↓ 43.1 43,091 1

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

  • Output: list_customer_by_id.name, list_customer_by_id.name_alias, list_customer_by_id.id
  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3050kB
  • Buffers: shared hit=110414 read=1129
36. 290.964 290.964 ↓ 43.1 43,091 1

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

  • Output: list_customer_by_id.name, list_customer_by_id.name_alias, list_customer_by_id.id
  • Function Call: list_customer_by_id('5b27a6da2f730ce446d80eabb97c97df'::text, $2)
  • Buffers: shared hit=110414 read=1129
37. 224.635 938.707 ↑ 1.0 976,692 1

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

  • Output: action_sell_sent_table.office_id, action_sell_sent_table.deal_id
  • Buckets: 131072 Batches: 16 Memory Usage: 3417kB
  • Buffers: shared hit=45 read=45208, temp written=3122
38. 714.072 714.072 ↑ 1.0 976,692 1

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

  • Output: action_sell_sent_table.office_id, action_sell_sent_table.deal_id
  • Filter: (action_sell_sent_table.action = 'SELL_SENT'::text)
  • Rows Removed by Filter: 3964963
  • Buffers: shared hit=45 read=45208
39. 227.445 956.828 ↑ 1.0 973,759 1

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

  • Output: action_sell_received_table.office_id, action_sell_received_table.deal_id
  • Buckets: 131072 Batches: 16 Memory Usage: 3411kB
  • Buffers: shared hit=78 read=45175, temp written=3111
40. 729.383 729.383 ↑ 1.0 973,759 1

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

  • Output: action_sell_received_table.office_id, action_sell_received_table.deal_id
  • Filter: (action_sell_received_table.action = 'SELL_RECEIVED'::text)
  • Rows Removed by Filter: 3967896
  • Buffers: shared hit=78 read=45175