explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m1F2 : Optimization for: Optimization for: plan #krmP; plan #wmhC

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.884 20,755.175 ↓ 13.9 2,000 1

Append (cost=3,235.49..3,369.62 rows=144 width=156) (actual time=0.394..20,755.175 rows=2,000 loops=1)

2.          

CTE a

3. 1.189 20,719.999 ↑ 1.0 2,000 1

Limit (cost=1,298.35..3,224.14 rows=2,000 width=30) (actual time=0.032..20,719.999 rows=2,000 loops=1)

4. 8.101 20,718.810 ↑ 88,981.8 2,000 1

Merge Join (cost=1,298.35..171,361,436.30 rows=177,963,664 width=30) (actual time=0.031..20,718.810 rows=2,000 loops=1)

  • Merge Cond: (ai.id = a_2.immeuble_id)
5. 4,488.677 4,488.677 ↑ 90,134.4 278 1

Index Scan using immeuble_commune_id_idx on immeuble ai (cost=0.44..19,150,122.77 rows=25,057,372 width=22) (actual time=0.013..4,488.677 rows=278 loops=1)

6. 16,222.032 16,222.032 ↑ 88,981.8 2,000 1

Index Scan using eligiblite_immeuble_id_idx on actuel a_2 (cost=0.57..149,938,011.37 rows=177,963,664 width=12) (actual time=0.011..16,222.032 rows=2,000 loops=1)

7. 4.153 20,751.196 ↓ 18.6 1,878 1

Hash Join (cost=11.35..80.83 rows=101 width=156) (actual time=0.394..20,751.196 rows=1,878 loops=1)

  • Hash Cond: (a.operateur_id = dimo.bk_operateur)
8. 11.529 20,746.862 ↓ 18.6 1,878 1

Nested Loop (cost=2.76..70.85 rows=101 width=150) (actual time=0.187..20,746.862 rows=1,878 loops=1)

  • Join Filter: ((a.debit_descendant >= dcd.min_debit) AND (a.debit_descendant <= (dcd.max_debit - 1)))
  • Rows Removed by Join Filter: 12,122
9. 6.831 20,733.333 ↓ 15.4 2,000 1

Hash Join (cost=2.76..51.56 rows=130 width=118) (actual time=0.165..20,733.333 rows=2,000 loops=1)

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

CTE Scan on a (cost=0.00..40.00 rows=2,000 width=48) (actual time=0.036..20,726.403 rows=2,000 loops=1)

11. 0.013 0.099 ↑ 1.0 13 1

Hash (cost=2.60..2.60 rows=13 width=74) (actual time=0.099..0.099 rows=13 loops=1)

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

Hash Join (cost=1.29..2.60 rows=13 width=74) (actual time=0.072..0.086 rows=13 loops=1)

  • Hash Cond: ((t.code)::text = dt.code)
13. 0.013 0.013 ↑ 1.0 13 1

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

14. 0.017 0.028 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.011 0.011 ↑ 1.0 13 1

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

16. 1.988 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)

17. 0.012 0.012 ↑ 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.012 rows=7 loops=1)

18. 0.083 0.181 ↑ 1.0 204 1

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

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

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

20. 0.168 3.095 ↓ 2.8 122 1

Hash Join (cost=54.62..63.21 rows=43 width=156) (actual time=2.841..3.095 rows=122 loops=1)

  • Hash Cond: ((t_1.code)::text = dt_1.code)
21. 0.182 2.880 ↓ 2.8 122 1

Hash Join (cost=53.32..61.32 rows=43 width=172) (actual time=2.710..2.880 rows=122 loops=1)

  • Hash Cond: (dimo_1.bk_operateur = a_1.operateur_id)
22. 0.233 0.233 ↑ 1.0 204 1

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

23. 0.092 2.465 ↓ 2.8 122 1

Hash (cost=52.79..52.79 rows=43 width=166) (actual time=2.465..2.465 rows=122 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
24. 0.573 2.373 ↓ 2.8 122 1

Nested Loop (cost=1.29..52.79 rows=43 width=166) (actual time=0.154..2.373 rows=122 loops=1)

  • Join Filter: (a_1.debit_descendant >= dcd_1.min_debit)
  • Rows Removed by Join Filter: 1,878
25. 0.013 0.013 ↑ 1.0 1 1

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

  • Filter: (max_debit IS NULL)
  • Rows Removed by Filter: 6
26. 1.363 1.787 ↓ 15.4 2,000 1

Hash Join (cost=1.29..50.09 rows=130 width=134) (actual time=0.077..1.787 rows=2,000 loops=1)

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

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

28. 0.028 0.039 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.011 0.011 ↑ 1.0 13 1

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

30. 0.018 0.047 ↑ 1.0 13 1

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

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

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

Planning time : 2.098 ms
Execution time : 20,756.758 ms