explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wmhC : Optimization for: plan #krmP

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.781 44,133.101 ↓ 13.9 2,000 1

Append (cost=48.52..565.94 rows=144 width=138) (actual time=69.078..44,133.101 rows=2,000 loops=1)

2.          

CTE a

3. 0.357 0.762 ↑ 1.0 2,000 1

Limit (cost=0.00..36.91 rows=2,000 width=41) (actual time=0.013..0.762 rows=2,000 loops=1)

4. 0.405 0.405 ↑ 88,981.8 2,000 1

Seq Scan on actuel a_2 (cost=0.00..3,284,292.64 rows=177,963,664 width=41) (actual time=0.012..0.405 rows=2,000 loops=1)

5. 0.125 952.637 ↑ 10.1 10 1

Hash Join (cost=11.61..349.76 rows=101 width=138) (actual time=69.077..952.637 rows=10 loops=1)

  • Hash Cond: (a.operateur_id = dimo.bk_operateur)
6. 0.186 952.316 ↑ 10.1 10 1

Hash Join (cost=3.02..339.78 rows=101 width=132) (actual time=68.821..952.316 rows=10 loops=1)

  • Hash Cond: ((t.code)::text = dt.code)
7. 0.106 952.087 ↑ 10.1 10 1

Nested Loop (cost=1.73..337.10 rows=101 width=148) (actual time=68.734..952.087 rows=10 loops=1)

8. 5.940 11.401 ↑ 10.1 10 1

Nested Loop (cost=1.29..69.38 rows=101 width=130) (actual time=2.350..11.401 rows=10 loops=1)

  • Join Filter: ((a.debit_descendant >= dcd.min_debit) AND (a.debit_descendant <= (dcd.max_debit - 1)))
  • Rows Removed by Join Filter: 13,990
9. 1.419 3.461 ↓ 15.4 2,000 1

Hash Join (cost=1.29..50.09 rows=130 width=98) (actual time=0.071..3.461 rows=2,000 loops=1)

  • Hash Cond: (a.techno_id = t.id)
10. 2.018 2.018 ↑ 1.0 2,000 1

CTE Scan on a (cost=0.00..40.00 rows=2,000 width=12) (actual time=0.015..2.018 rows=2,000 loops=1)

11. 0.014 0.024 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=90) (actual time=0.024..0.024 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.010 0.010 ↑ 1.0 13 1

Seq Scan on techno t (cost=0.00..1.13 rows=13 width=90) (actual time=0.006..0.010 rows=13 loops=1)

13. 1.989 2.000 ↑ 1.0 7 2,000

Materialize (cost=0.00..1.10 rows=7 width=44) (actual time=0.000..0.001 rows=7 loops=2,000)

14. 0.011 0.011 ↑ 1.0 7 1

Seq Scan on d_classe_debit dcd (cost=0.00..1.07 rows=7 width=44) (actual time=0.006..0.011 rows=7 loops=1)

15. 940.580 940.580 ↑ 1.0 1 10

Index Scan using immeuble_commune_id_idx on immeuble ai (cost=0.44..2.64 rows=1 width=22) (actual time=94.053..94.058 rows=1 loops=10)

  • Index Cond: (id = a.immeuble_id)
16. 0.021 0.043 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=36) (actual time=0.043..0.043 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.022 0.022 ↑ 1.0 13 1

Seq Scan on d_techno dt (cost=0.00..1.13 rows=13 width=36) (actual time=0.016..0.022 rows=13 loops=1)

18. 0.081 0.196 ↑ 1.0 204 1

Hash (cost=6.04..6.04 rows=204 width=12) (actual time=0.196..0.196 rows=204 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
19. 0.115 0.115 ↑ 1.0 204 1

Seq Scan on d_operateur dimo (cost=0.00..6.04 rows=204 width=12) (actual time=0.023..0.115 rows=204 loops=1)

20. 6.417 43,178.683 ↓ 46.3 1,990 1

Hash Join (cost=11.61..177.83 rows=43 width=138) (actual time=0.439..43,178.683 rows=1,990 loops=1)

  • Hash Cond: (a_1.operateur_id = dimo_1.bk_operateur)
21. 11.445 43,172.090 ↓ 46.3 1,990 1

Hash Join (cost=3.02..168.65 rows=43 width=132) (actual time=0.222..43,172.090 rows=1,990 loops=1)

  • Hash Cond: ((t_1.code)::text = dt_1.code)
22. 10.027 43,160.593 ↓ 46.3 1,990 1

Nested Loop (cost=1.73..166.77 rows=43 width=148) (actual time=0.124..43,160.593 rows=1,990 loops=1)

23. 2.634 11.346 ↓ 46.3 1,990 1

Nested Loop (cost=1.29..52.79 rows=43 width=130) (actual time=0.090..11.346 rows=1,990 loops=1)

  • Join Filter: (a_1.debit_descendant >= dcd_1.min_debit)
  • Rows Removed by Join Filter: 10
24. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on d_classe_debit dcd_1 (cost=0.00..1.07 rows=1 width=40) (actual time=0.011..0.014 rows=1 loops=1)

  • Filter: (max_debit IS NULL)
  • Rows Removed by Filter: 6
25. 4.226 8.698 ↓ 15.4 2,000 1

Hash Join (cost=1.29..50.09 rows=130 width=98) (actual time=0.055..8.698 rows=2,000 loops=1)

  • Hash Cond: (a_1.techno_id = t_1.id)
26. 4.448 4.448 ↑ 1.0 2,000 1

CTE Scan on a a_1 (cost=0.00..40.00 rows=2,000 width=12) (actual time=0.001..4.448 rows=2,000 loops=1)

27. 0.011 0.024 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=90) (actual time=0.024..0.024 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.013 0.013 ↑ 1.0 13 1

Seq Scan on techno t_1 (cost=0.00..1.13 rows=13 width=90) (actual time=0.008..0.013 rows=13 loops=1)

29. 43,139.220 43,139.220 ↑ 1.0 1 1,990

Index Scan using immeuble_commune_id_idx on immeuble ai_1 (cost=0.44..2.64 rows=1 width=22) (actual time=21.662..21.678 rows=1 loops=1,990)

  • Index Cond: (id = a_1.immeuble_id)
30. 0.019 0.052 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=36) (actual time=0.052..0.052 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.033 0.033 ↑ 1.0 13 1

Seq Scan on d_techno dt_1 (cost=0.00..1.13 rows=13 width=36) (actual time=0.028..0.033 rows=13 loops=1)

32. 0.080 0.176 ↑ 1.0 204 1

Hash (cost=6.04..6.04 rows=204 width=12) (actual time=0.176..0.176 rows=204 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
33. 0.096 0.096 ↑ 1.0 204 1

Seq Scan on d_operateur dimo_1 (cost=0.00..6.04 rows=204 width=12) (actual time=0.012..0.096 rows=204 loops=1)

Planning time : 2.797 ms
Execution time : 44,134.958 ms