explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.416 28.370 ↓ 1,513.0 1,513 1

Nested Loop (cost=3,293.53..3,301.64 rows=1 width=156) (actual time=21.405..28.370 rows=1,513 loops=1)

  • Join Filter: ((t.code)::text = dt.code)
  • Rows Removed by Join Filter: 18,156
2.          

CTE a

3. 0.262 4.128 ↑ 1.0 2,000 1

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

4. 1.001 3.866 ↑ 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.044..3.866 rows=2,000 loops=1)

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

6. 2.415 2.415 ↑ 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.017..2.415 rows=2,000 loops=1)

7. 0.537 21.928 ↓ 1,513.0 1,513 1

Hash Join (cost=69.39..76.21 rows=1 width=172) (actual time=21.381..21.928 rows=1,513 loops=1)

  • Hash Cond: (dimo.bk_operateur = a.operateur_id)
8. 0.052 0.052 ↑ 1.0 204 1

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

9. 0.741 21.339 ↓ 1,513.0 1,513 1

Hash (cost=69.38..69.38 rows=1 width=166) (actual time=21.339..21.339 rows=1,513 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 147kB
10. 12.025 20.598 ↓ 1,513.0 1,513 1

Nested Loop (cost=1.29..69.38 rows=1 width=166) (actual time=3.060..20.598 rows=1,513 loops=1)

  • Join Filter: (a.debit_descendant <@ int4range(dcd.min_debit, (dcd.max_debit - 1)))
  • Rows Removed by Join Filter: 12,487
11. 1.183 6.573 ↓ 15.4 2,000 1

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

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

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

13. 0.008 0.018 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 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.007..0.010 rows=13 loops=1)

15. 1.981 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.019 0.019 ↑ 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.019 rows=7 loops=1)

17. 3.026 3.026 ↑ 1.0 13 1,513

Seq Scan on d_techno dt (cost=0.00..1.13 rows=13 width=36) (actual time=0.001..0.002 rows=13 loops=1,513)

Planning time : 14.657 ms
Execution time : 28.691 ms