explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qLfE

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

Aggregate (cost=44,645.91..44,645.92 rows=1 width=8) (actual time=6,474.644..6,474.644 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 28.923 94.294 ↑ 1.0 1 1

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

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

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

Hash Left Join (cost=20,228.20..39,712.36 rows=20 width=0) (actual time=448.192..6,415.005 rows=337,094 loops=1)

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

Nested Loop Left Join (cost=20,205.45..39,688.90 rows=4 width=4) (actual time=48.934..5,889.451 rows=337,094 loops=1)

7. 412.196 4,964.487 ↓ 84,273.5 337,094 1

Nested Loop (cost=20,205.03..39,658.75 rows=4 width=4) (actual time=48.923..4,964.487 rows=337,094 loops=1)

  • 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
8. 134.287 974.581 ↓ 496.9 119,257 1

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

9. 197.676 244.009 ↓ 496.9 119,257 1

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

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

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

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

  • Index Cond: (sender_office_id = 8)
13. 2.823 2.823 ↓ 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.823..2.823 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. 3,577.710 3,577.710 ↑ 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=68) (actual time=0.026..0.030 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. 7.522 399.248 ↓ 43.1 43,091 1

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

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

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