explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ct3g

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 6,153.890 ↑ 1.0 1 1

Aggregate (cost=86,180.37..86,180.38 rows=1 width=8) (actual time=6,153.890..6,153.890 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 28.725 86.568 ↑ 1.0 1 1

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

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

  • Filter: (customer_id IS NOT NULL)
  • Rows Removed by Filter: 24824
5. 127.400 6,095.144 ↓ 16,854.7 337,094 1

Hash Left Join (cost=36,568.93..81,246.82 rows=20 width=0) (actual time=871.963..6,095.144 rows=337,094 loops=1)

  • Hash Cond: (deal_customer.customer_id = list_customer_by_id.id)
6. 243.448 5,560.638 ↓ 84,273.5 337,094 1

Nested Loop Left Join (cost=36,546.18..81,223.36 rows=4 width=4) (actual time=464.847..5,560.638 rows=337,094 loops=1)

7. 246.079 4,643.002 ↓ 84,273.5 337,094 1

Nested Loop (cost=36,545.76..81,193.21 rows=4 width=4) (actual time=464.834..4,643.002 rows=337,094 loops=1)

  • Join Filter: (deal.id = deal_state.deal_id)
8. 2,255.790 3,385.641 ↓ 84,273.5 337,094 1

Hash Join (cost=36,545.33..81,182.27 rows=4 width=8) (actual time=464.808..3,385.641 rows=337,094 loops=1)

  • Hash Cond: (deal_row.deal_id = deal.id)
  • Join Filter: ((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
9. 672.810 851.237 ↓ 66.2 1,952,550 1

Bitmap Heap Scan on deal_row (cost=637.32..44,533.34 rows=29,489 width=68) (actual time=185.557..851.237 rows=1,952,550 loops=1)

  • Recheck Cond: ((product_article_type = 'PRODUCT'::text) OR (product_article_type = 'CURRENCY'::text))
  • Heap Blocks: exact=43511
10. 0.002 178.427 ↓ 0.0 0 1

BitmapOr (cost=637.32..637.32 rows=29,563 width=0) (actual time=178.427..178.427 rows=0 loops=1)

11. 53.333 53.333 ↓ 36.9 546,040 1

Bitmap Index Scan on deal_row_product_article_type_index (cost=0.00..311.29 rows=14,781 width=0) (actual time=53.333..53.333 rows=546,040 loops=1)

  • Index Cond: (product_article_type = 'PRODUCT'::text)
12. 125.092 125.092 ↓ 95.2 1,406,510 1

Bitmap Index Scan on deal_row_product_article_type_index (cost=0.00..311.29 rows=14,781 width=0) (actual time=125.092..125.092 rows=1,406,510 loops=1)

  • Index Cond: (product_article_type = 'CURRENCY'::text)
13. 55.183 278.614 ↓ 496.9 119,257 1

Hash (cost=35,905.01..35,905.01 rows=240 width=44) (actual time=278.614..278.614 rows=119,257 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3562kB
14. 165.744 223.431 ↓ 496.9 119,257 1

Bitmap Heap Scan on deal (cost=20,204.17..35,905.01 rows=240 width=44) (actual time=62.205..223.431 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
15. 0.004 57.687 ↓ 0.0 0 1

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

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

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

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

  • Index Cond: (receiver_office_id = 8)
19. 1,011.282 1,011.282 ↑ 1.0 1 337,094

Index Only Scan using deal_state_pkey on deal_state (cost=0.43..2.72 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=337,094)

  • Index Cond: (deal_id = deal_row.deal_id)
  • Heap Fetches: 337094
20. 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)
21. 8.408 407.106 ↓ 43.1 43,091 1

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

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

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