explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sTnT

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.210 ↓ 0.0 0 1

Limit (cost=4,946.57..5,164.61 rows=5 width=386) (actual time=0.210..0.210 rows=0 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))
  • Buffers: shared hit=65
2.          

Initplan (forLimit)

3. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=0.28..942,815.73 rows=1,478,142 width=0) (never executed)

4. 0.000 0.000 ↓ 0.0 0

Seq Scan on trading.deal_row (cost=0.00..76,837.83 rows=2,956,283 width=32) (never executed)

  • 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
5. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_article_code_pk on trading.product_article (cost=0.28..0.29 rows=1 width=9) (never executed)

  • Output: product_article.code
  • Index Cond: (product_article.code = deal_row.code)
  • Heap Fetches: 0
6. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4,933.50..4,933.51 rows=1 width=32) (never executed)

  • Output: array_agg(deal_customer_1.customer_id)
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on trading.deal_customer deal_customer_1 (cost=0.00..4,439.98 rows=197,406 width=4) (never executed)

  • 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)
8. 0.001 0.209 ↓ 0.0 0 1

Nested Loop (cost=12.15..230.19 rows=5 width=386) (actual time=0.209..0.209 rows=0 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)
  • Inner Unique: true
  • Buffers: shared hit=65
9. 0.001 0.191 ↑ 5.0 1 1

Nested Loop Left Join (cost=11.72..187.94 rows=5 width=224) (actual time=0.178..0.191 rows=1 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
  • Buffers: shared hit=61
10. 0.001 0.179 ↑ 5.0 1 1

Nested Loop Left Join (cost=11.29..161.32 rows=5 width=220) (actual time=0.166..0.179 rows=1 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
  • Buffers: shared hit=57
11. 0.002 0.166 ↑ 5.0 1 1

Nested Loop Left Join (cost=10.86..119.06 rows=5 width=216) (actual time=0.153..0.166 rows=1 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
  • Buffers: shared hit=53
12. 0.001 0.133 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.16..96.51 rows=1 width=116) (actual time=0.120..0.133 rows=1 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, 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=50
13. 0.002 0.121 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.73..88.06 rows=1 width=112) (actual time=0.108..0.121 rows=1 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, action_offer_sent_table.office_id, action_paid_table.office_id, action_buy_sent_table.office_id
  • Inner Unique: true
  • Buffers: shared hit=47
14. 0.001 0.108 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.29..79.61 rows=1 width=108) (actual time=0.096..0.108 rows=1 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, action_offer_sent_table.office_id, action_paid_table.office_id
  • Inner Unique: true
  • Buffers: shared hit=44
15. 0.002 0.095 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.86..74.28 rows=1 width=104) (actual time=0.083..0.095 rows=1 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, action_offer_sent_table.office_id
  • Inner Unique: true
  • Buffers: shared hit=40
16. 0.002 0.074 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.43..68.96 rows=1 width=100) (actual time=0.062..0.074 rows=1 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
  • Join Filter: (unhandled_deals.deal_id = deal.id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=36
17. 0.060 0.060 ↑ 1.0 1 1

Index Scan Backward using deal_pkey on trading.deal (cost=0.43..67.66 rows=1 width=96) (actual time=0.048..0.060 rows=1 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
  • Index Cond: (deal.id = ANY ('{344,545,345,2345,4353,43534,43543,345}'::integer[]))
  • 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: 6
  • Buffers: shared hit=35
18. 0.012 0.012 ↑ 1.0 1 1

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

  • Output: unhandled_deals.deal_id, unhandled_deals.office_id
  • Filter: (unhandled_deals.office_id = 8)
  • Rows Removed by Filter: 22
  • Buffers: shared hit=1
19. 0.019 0.019 ↑ 2.0 1 1

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_offer_sent_table (cost=0.43..10.21 rows=2 width=8) (actual time=0.019..0.019 rows=1 loops=1)

  • 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=4
20. 0.012 0.012 ↑ 2.0 1 1

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_paid_table (cost=0.43..10.21 rows=2 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • 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=4
21. 0.011 0.011 ↓ 0.0 0 1

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_buy_sent_table (cost=0.43..8.45 rows=1 width=8) (actual time=0.011..0.011 rows=0 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
  • Index Cond: ((action_buy_sent_table.deal_id = deal.id) AND (action_buy_sent_table.action = 'BUY_SENT'::text))
  • Buffers: shared hit=3
22. 0.011 0.011 ↓ 0.0 0 1

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_buy_received_table (cost=0.43..8.45 rows=1 width=8) (actual time=0.011..0.011 rows=0 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
  • Index Cond: ((action_buy_received_table.deal_id = deal.id) AND (action_buy_received_table.action = 'BUY_RECEIVED'::text))
  • Buffers: shared hit=3
23. 0.015 0.031 ↓ 0.0 0 1

Hash Right Join (cost=8.70..22.50 rows=5 width=104) (actual time=0.031..0.031 rows=0 loops=1)

  • Output: deal_customer.customer_id, deal_customer.name, deal_customer.deal_id, list_customer_by_id.name, list_customer_by_id.name_alias
  • Hash Cond: (list_customer_by_id.id = deal_customer.customer_id)
  • Buffers: shared hit=3
24. 0.000 0.000 ↓ 0.0 0

Function Scan on public.list_customer_by_id (cost=0.25..10.25 rows=1,000 width=68) (never executed)

  • Output: list_customer_by_id.id, list_customer_by_id.name, list_customer_by_id.name_alias
  • Function Call: list_customer_by_id('5b27a6da2f730ce446d80eabb97c97df'::text, $2)
25. 0.001 0.016 ↓ 0.0 0 1

Hash (cost=8.44..8.44 rows=1 width=40) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: deal_customer.customer_id, deal_customer.name, deal_customer.deal_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=3
26. 0.015 0.015 ↓ 0.0 0 1

Index Scan using deal_customer_deal_id_ndx on trading.deal_customer (cost=0.42..8.44 rows=1 width=40) (actual time=0.015..0.015 rows=0 loops=1)

  • Output: deal_customer.customer_id, deal_customer.name, deal_customer.deal_id
  • Index Cond: (deal_customer.deal_id = deal.id)
  • Buffers: shared hit=3
27. 0.012 0.012 ↑ 1.0 1 1

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_sell_sent_table (cost=0.43..8.45 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Output: action_sell_sent_table.id, action_sell_sent_table.deal_id, action_sell_sent_table.action, action_sell_sent_table.user_id, action_sell_sent_table.office_id, action_sell_sent_table.created, action_sell_sent_table.identification_id, action_sell_sent_table.representative_name, action_sell_sent_table.identification_level
  • Index Cond: ((action_sell_sent_table.deal_id = deal.id) AND (action_sell_sent_table.action = 'SELL_SENT'::text))
  • Buffers: shared hit=4
28. 0.011 0.011 ↑ 2.0 1 1

Index Scan using deal_actions_deal_id_action_uindex on trading.deal_actions action_sell_received_table (cost=0.43..10.21 rows=2 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Output: action_sell_received_table.id, action_sell_received_table.deal_id, action_sell_received_table.action, action_sell_received_table.user_id, action_sell_received_table.office_id, action_sell_received_table.created, action_sell_received_table.identification_id, action_sell_received_table.representative_name, action_sell_received_table.identification_level
  • Index Cond: ((action_sell_received_table.deal_id = deal.id) AND (action_sell_received_table.action = 'SELL_RECEIVED'::text))
  • Buffers: shared hit=4
29. 0.017 0.017 ↓ 0.0 0 1

Index Scan using deal_state_pkey on trading.deal_state (cost=0.43..8.45 rows=1 width=165) (actual time=0.017..0.017 rows=0 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
  • Index Cond: (deal_state.deal_id = deal.id)
  • Filter: ((deal_state.last_status_state <> 'DONE'::text) AND (deal_state.last_status_state <> 'DISCARDED'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4