explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MmL6

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11.371 705.702 ↓ 6.6 11,447 1

Hash Join (cost=1,153.64..78,411.39 rows=1,729 width=241) (actual time=8.884..705.702 rows=11,447 loops=1)

  • Hash Cond: (compart.compartment_type_id = comparttype.id)
2.          

Initplan (for Hash Join)

3. 0.002 0.177 ↑ 1.0 1 1

Limit (cost=0.00..289.39 rows=1 width=4) (actual time=0.176..0.177 rows=1 loops=1)

4. 0.175 0.175 ↑ 1.0 1 1

Seq Scan on company (cost=0.00..289.39 rows=1 width=4) (actual time=0.175..0.175 rows=1 loops=1)

  • Filter: ((tax_id)::text = '08030363000181'::text)
  • Rows Removed by Filter: 1093
5. 7.192 614.013 ↓ 6.6 11,447 1

Hash Join (cost=831.97..50,815.40 rows=1,729 width=181) (actual time=8.838..614.013 rows=11,447 loops=1)

  • Hash Cond: (transdet.compartment_id = compart.id)
6. 7.268 606.794 ↓ 6.6 11,447 1

Hash Left Join (cost=830.23..50,808.54 rows=1,729 width=181) (actual time=8.806..606.794 rows=11,447 loops=1)

  • Hash Cond: (contr.equipment_id = equip.id)
7. 6.408 595.033 ↓ 6.6 11,447 1

Hash Left Join (cost=569.91..50,543.68 rows=1,729 width=178) (actual time=4.299..595.033 rows=11,447 loops=1)

  • Hash Cond: (contr.id = contrcompfields.contract_id)
8. 9.924 588.580 ↓ 6.6 11,447 1

Nested Loop (cost=505.26..50,280.19 rows=1,729 width=146) (actual time=4.248..588.580 rows=11,447 loops=1)

9. 2.994 560.924 ↓ 6.2 4,433 1

Hash Join (cost=504.69..40,883.80 rows=718 width=142) (actual time=4.238..560.924 rows=4,433 loops=1)

  • Hash Cond: (compb.country_id = country.id)
10. 139.373 557.909 ↓ 6.2 4,433 1

Hash Join (cost=503.24..40,880.06 rows=718 width=136) (actual time=4.195..557.909 rows=4,433 loops=1)

  • Hash Cond: (trans.contract_id = contr.id)
  • Join Filter: (((trans.operation_id = 1) AND ((compcust.tax_id)::text = '08030363000181'::text)) OR (compcust.parent_company_id = $1))
  • Rows Removed by Join Filter: 6
11. 416.590 416.590 ↓ 1.1 603,800 1

Index Scan using transaction_deposit_date_idx on transaction trans (cost=0.57..38,235.66 rows=567,760 width=48) (actual time=0.013..416.590 rows=603,800 loops=1)

  • Index Cond: (deposit_date > (now() - '30 days'::interval))
12. 0.009 1.946 ↓ 1.2 11 1

Hash (cost=502.56..502.56 rows=9 width=111) (actual time=1.946..1.946 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.014 1.937 ↓ 1.2 11 1

Hash Join (cost=7.55..502.56 rows=9 width=111) (actual time=0.445..1.937 rows=11 loops=1)

  • Hash Cond: (contr.branch_id = bran.id)
14. 0.007 1.844 ↓ 1.2 11 1

Nested Loop (cost=0.85..495.83 rows=9 width=107) (actual time=0.360..1.844 rows=11 loops=1)

15. 0.010 1.815 ↓ 1.2 11 1

Nested Loop (cost=0.57..448.42 rows=9 width=101) (actual time=0.353..1.815 rows=11 loops=1)

16. 0.018 1.783 ↓ 1.2 11 1

Nested Loop (cost=0.28..400.79 rows=9 width=101) (actual time=0.348..1.783 rows=11 loops=1)

17. 1.717 1.717 ↓ 2.0 24 1

Seq Scan on company compcust (cost=0.00..313.06 rows=12 width=81) (actual time=0.341..1.717 rows=24 loops=1)

  • Filter: (((tax_id)::text = '08030363000181'::text) OR (parent_company_id = $1))
  • Rows Removed by Filter: 9489
18. 0.048 0.048 ↓ 0.0 0 24

Index Scan using idx_contract_customer_id on contract contr (cost=0.28..7.30 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=24)

  • Index Cond: (customer_id = compcust.id)
19. 0.022 0.022 ↑ 1.0 1 11

Index Scan using pk_customer on customer cust (cost=0.29..5.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: (id = contr.customer_id)
20. 0.022 0.022 ↑ 1.0 1 11

Index Scan using pk_company on company compb (cost=0.29..5.27 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: (id = contr.branch_id)
21. 0.038 0.079 ↓ 1.0 121 1

Hash (cost=5.20..5.20 rows=120 width=12) (actual time=0.079..0.079 rows=121 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
22. 0.041 0.041 ↓ 1.0 121 1

Seq Scan on branch bran (cost=0.00..5.20 rows=120 width=12) (actual time=0.005..0.041 rows=121 loops=1)

23. 0.010 0.021 ↓ 1.4 28 1

Hash (cost=1.20..1.20 rows=20 width=8) (actual time=0.021..0.021 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.011 0.011 ↓ 1.4 28 1

Seq Scan on country (cost=0.00..1.20 rows=20 width=8) (actual time=0.004..0.011 rows=28 loops=1)

25. 17.732 17.732 ↑ 41.3 3 4,433

Index Scan using transaction_detail_transaction_id_idx on transaction_detail transdet (cost=0.57..11.85 rows=124 width=12) (actual time=0.003..0.004 rows=3 loops=4,433)

  • Index Cond: (transaction_id = trans.id)
26. 0.004 0.045 ↑ 127.1 7 1

Hash (cost=53.52..53.52 rows=890 width=36) (actual time=0.045..0.045 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.010 0.041 ↑ 127.1 7 1

Hash Left Join (cost=32.27..53.52 rows=890 width=36) (actual time=0.035..0.041 rows=7 loops=1)

  • Hash Cond: (contrcompfields.generic_identifier_id = genident.id)
28. 0.005 0.005 ↑ 127.1 7 1

Seq Scan on contract_complementary_fields contrcompfields (cost=0.00..18.90 rows=890 width=8) (actual time=0.004..0.005 rows=7 loops=1)

29. 0.013 0.026 ↑ 30.0 33 1

Hash (cost=19.90..19.90 rows=990 width=36) (actual time=0.026..0.026 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 0.013 0.013 ↑ 30.0 33 1

Seq Scan on generic_identifier genident (cost=0.00..19.90 rows=990 width=36) (actual time=0.004..0.013 rows=33 loops=1)

31. 2.121 4.493 ↓ 1.0 7,192 1

Hash (cost=170.70..170.70 rows=7,170 width=11) (actual time=4.493..4.493 rows=7,192 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 382kB
32. 2.372 2.372 ↓ 1.0 7,192 1

Seq Scan on equipment equip (cost=0.00..170.70 rows=7,170 width=11) (actual time=0.004..2.372 rows=7,192 loops=1)

33. 0.012 0.027 ↓ 1.1 35 1

Hash (cost=1.33..1.33 rows=33 width=8) (actual time=0.027..0.027 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.015 0.015 ↓ 1.1 35 1

Seq Scan on compartment compart (cost=0.00..1.33 rows=33 width=8) (actual time=0.005..0.015 rows=35 loops=1)

35. 0.004 0.012 ↑ 495.0 2 1

Hash (cost=19.90..19.90 rows=990 width=36) (actual time=0.012..0.012 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.008 0.008 ↑ 495.0 2 1

Seq Scan on compartment_type comparttype (cost=0.00..19.90 rows=990 width=36) (actual time=0.007..0.008 rows=2 loops=1)

37.          

SubPlan (for Hash Join)

38. 45.788 80.129 ↑ 124.0 1 11,447

GroupAggregate (cost=0.57..15.77 rows=124 width=40) (actual time=0.006..0.007 rows=1 loops=11,447)

  • Group Key: transactiondetail.transaction_id
39. 34.341 34.341 ↑ 41.3 3 11,447

Index Scan using transaction_detail_transaction_id_idx on transaction_detail transactiondetail (cost=0.57..11.74 rows=124 width=20) (actual time=0.002..0.003 rows=3 loops=11,447)

  • Index Cond: (transaction_id = trans.id)
Planning time : 4.742 ms
Execution time : 708.333 ms