explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KcHW : Optimization for: plan #MmL6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 53.613 2,886.443 ↓ 26.3 47,775 1

Hash Join (cost=1,153.64..82,243.22 rows=1,818 width=241) (actual time=210.382..2,886.443 rows=47,775 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. 29.726 2,498.216 ↓ 26.3 47,775 1

Hash Join (cost=831.97..53,243.29 rows=1,818 width=181) (actual time=210.328..2,498.216 rows=47,775 loops=1)

  • Hash Cond: (transdet.compartment_id = compart.id)
6. 30.362 2,468.463 ↓ 26.3 47,775 1

Hash Left Join (cost=830.23..53,236.16 rows=1,818 width=181) (actual time=210.295..2,468.463 rows=47,775 loops=1)

  • Hash Cond: (contr.equipment_id = equip.id)
7. 26.869 2,433.678 ↓ 26.3 47,775 1

Hash Left Join (cost=569.91..52,971.06 rows=1,818 width=178) (actual time=205.858..2,433.678 rows=47,775 loops=1)

  • Hash Cond: (contr.id = contrcompfields.contract_id)
8. 39.588 2,406.753 ↓ 26.3 47,775 1

Nested Loop (cost=505.26..52,697.33 rows=1,818 width=146) (actual time=205.790..2,406.753 rows=47,775 loops=1)

9. 13.966 2,286.245 ↓ 26.8 20,230 1

Hash Join (cost=504.69..42,851.31 rows=755 width=142) (actual time=4.799..2,286.245 rows=20,230 loops=1)

  • Hash Cond: (compb.country_id = country.id)
10. 573.490 2,272.258 ↓ 26.8 20,230 1

Hash Join (cost=503.24..42,847.45 rows=755 width=136) (actual time=4.774..2,272.258 rows=20,230 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: 25
11. 1,696.806 1,696.806 ↓ 4.1 2,429,794 1

Index Scan using transaction_deposit_date_idx on transaction trans (cost=0.57..40,093.06 rows=596,919 width=48) (actual time=0.016..1,696.806 rows=2,429,794 loops=1)

  • Index Cond: (deposit_date > (now() - '50 days'::interval))
12. 0.010 1.962 ↓ 1.2 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.013 1.952 ↓ 1.2 11 1

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

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

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

15. 0.011 1.832 ↓ 1.2 11 1

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

16. 0.017 1.799 ↓ 1.2 11 1

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

17. 1.734 1.734 ↓ 2.0 24 1

Seq Scan on company compcust (cost=0.00..313.06 rows=12 width=81) (actual time=0.341..1.734 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.001..0.002 rows=1 loops=11)

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

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

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

Seq Scan on branch bran (cost=0.00..5.20 rows=120 width=12) (actual time=0.005..0.040 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.020..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.003..0.011 rows=28 loops=1)

25. 80.920 80.920 ↑ 62.0 2 20,230

Index Scan using transaction_detail_transaction_id_idx on transaction_detail transdet (cost=0.57..11.80 rows=124 width=12) (actual time=0.003..0.004 rows=2 loops=20,230)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.012 0.052 ↑ 127.1 7 1

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

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

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

29. 0.017 0.031 ↑ 30.0 33 1

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

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

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

31. 2.101 4.423 ↓ 1.0 7,192 1

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

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

Seq Scan on equipment equip (cost=0.00..170.70 rows=7,170 width=11) (actual time=0.005..2.322 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. 191.100 334.425 ↑ 124.0 1 47,775

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

  • Group Key: transactiondetail.transaction_id
39. 143.325 143.325 ↑ 41.3 3 47,775

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=47,775)

  • Index Cond: (transaction_id = trans.id)
Planning time : 4.760 ms
Execution time : 2,897.011 ms