explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eoqI

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 4,169.511 ↑ 1.0 1 1

Aggregate (cost=44,734.71..44,734.72 rows=1 width=8) (actual time=4,169.511..4,169.511 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 29.533 90.124 ↑ 1.0 1 1

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

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

  • Filter: (customer_id IS NOT NULL)
  • Rows Removed by Filter: 24824
5. 127.822 4,105.961 ↓ 16,854.7 337,094 1

Hash Left Join (cost=20,228.20..39,801.16 rows=20 width=4) (actual time=370.481..4,105.961 rows=337,094 loops=1)

  • Hash Cond: (deal_customer.customer_id = list_customer_by_id.id)
6. 273.488 3,659.003 ↓ 84,273.5 337,094 1

Nested Loop Left Join (cost=20,205.45..39,777.70 rows=4 width=8) (actual time=51.331..3,659.003 rows=337,094 loops=1)

7. 354.055 2,711.327 ↓ 84,273.5 337,094 1

Nested Loop (cost=20,205.03..39,747.55 rows=4 width=8) (actual time=51.320..2,711.327 rows=337,094 loops=1)

  • Join Filter: ((deal.id = deal_row.deal_id) AND ((deal.type = 'CUSTOMER'::text) OR ((deal_row.quantity > '0'::numeric) AND (deal.receiver_office_id = 8)) OR ((deal_row.quantity <= '0'::numeric) AND (deal.sender_office_id = 8))))
  • Rows Removed by Join Filter: 133507
8. 99.802 926.188 ↓ 496.9 119,257 1

Nested Loop (cost=20,204.60..37,887.81 rows=240 width=48) (actual time=51.265..926.188 rows=119,257 loops=1)

9. 181.291 230.101 ↓ 496.9 119,257 1

Bitmap Heap Scan on deal (cost=20,204.17..35,905.01 rows=240 width=44) (actual time=51.220..230.101 rows=119,257 loops=1)

  • Recheck Cond: ((office_id = 8) OR (sender_office_id = 8) OR (receiver_office_id = 8))
  • Filter: (((type = 'CUSTOMER'::text) OR (receiver_office_id = 8) OR (sender_office_id = 8)) AND ((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: 6716
  • Heap Blocks: exact=14413
10. 0.002 48.810 ↓ 0.0 0 1

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

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

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

  • Index Cond: (sender_office_id = 8)
13. 2.954 2.954 ↓ 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.954..2.954 rows=46,868 loops=1)

  • Index Cond: (receiver_office_id = 8)
14. 596.285 596.285 ↑ 1.0 1 119,257

Index Only Scan using deal_state_pkey on deal_state (cost=0.43..8.26 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=119,257)

  • Index Cond: (deal_id = deal.id)
  • Heap Fetches: 119257
15. 1,431.084 1,431.084 ↑ 37.0 4 119,257

Index Scan using deal_row_deal_id_product_article_type_quantity_index on deal_row (cost=0.43..4.05 rows=148 width=72) (actual time=0.008..0.012 rows=4 loops=119,257)

  • Index Cond: ((deal_id = deal_state.deal_id) AND (product_article_type = ANY ('{PRODUCT,CURRENCY}'::text[])))
16. 674.188 674.188 ↑ 1.0 1 337,094

Index Scan using deal_customer_deal_id_ndx on deal_customer (cost=0.42..7.54 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=337,094)

  • Index Cond: (deal_id = deal.id)
17. 9.209 319.136 ↓ 43.1 43,091 1

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2027kB
18. 309.927 309.927 ↓ 43.1 43,091 1

Function Scan on list_customer_by_id (cost=0.25..10.25 rows=1,000 width=4) (actual time=303.171..309.927 rows=43,091 loops=1)