explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.049 14.325 ↓ 18.6 1,878 1

Hash Join (cost=3,235.49..3,304.97 rows=101 width=156) (actual time=0.324..14.325 rows=1,878 loops=1)

  • Hash Cond: (a.operateur_id = dimo.bk_operateur)
2.          

CTE a

3. 0.330 4.088 ↑ 1.0 2,000 1

Limit (cost=1,298.35..3,224.14 rows=2,000 width=30) (actual time=0.033..4.088 rows=2,000 loops=1)

4. 0.994 3.758 ↑ 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..3.758 rows=2,000 loops=1)

  • Merge Cond: (ai.id = a_1.immeuble_id)
5. 0.412 0.412 ↑ 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.014..0.412 rows=278 loops=1)

6. 2.352 2.352 ↑ 88,981.8 2,000 1

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

7. 4.429 13.116 ↓ 18.6 1,878 1

Nested Loop (cost=2.76..70.85 rows=101 width=150) (actual time=0.154..13.116 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
8. 1.235 6.687 ↓ 15.4 2,000 1

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

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

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

10. 0.037 0.090 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.025 0.053 ↑ 1.0 13 1

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

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

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

13. 0.007 0.017 ↑ 1.0 13 1

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

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

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

15. 1.992 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)

16. 0.008 0.008 ↑ 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.008 rows=7 loops=1)

17. 0.070 0.160 ↑ 1.0 204 1

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

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

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

Planning time : 1.401 ms
Execution time : 14.678 ms