explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3NYe : Optimization for: plan #ZKo

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13.686 27,595.868 ↑ 1.1 76,776 1

Limit (cost=2,453.82..722,032.59 rows=82,976 width=1,014) (actual time=241.002..27,595.868 rows=76,776 loops=1)

2. 372.102 27,582.182 ↑ 1.1 76,776 1

Nested Loop (cost=2,453.82..722,032.59 rows=82,976 width=1,014) (actual time=241.000..27,582.182 rows=76,776 loops=1)

  • Join Filter: (dr.id_sexe = s.id_sexe)
  • Rows Removed by Join Filter: 153,552
3. 93.489 27,210.080 ↑ 1.1 76,776 1

Nested Loop (cost=2,453.82..717,467.87 rows=82,976 width=798) (actual time=240.855..27,210.080 rows=76,776 loops=1)

4. 41.231 1,319.855 ↑ 1.1 76,776 1

Hash Join (cost=2,453.39..261,784.03 rows=82,976 width=738) (actual time=240.811..1,319.855 rows=76,776 loops=1)

  • Hash Cond: (tdd.id_motif_secondaire = tmds.id_motifdmd)
5. 33.888 1,278.584 ↑ 1.1 76,776 1

Hash Join (cost=2,451.53..260,641.26 rows=82,976 width=528) (actual time=240.727..1,278.584 rows=76,776 loops=1)

  • Hash Cond: (tdd.id_motif_principal = tmdp.id_motifdmd)
6. 34.505 1,244.682 ↑ 1.1 76,776 1

Hash Join (cost=2,449.68..259,498.48 rows=82,976 width=318) (actual time=240.699..1,244.682 rows=76,776 loops=1)

  • Hash Cond: (d.id_double_motif = tdd.id_double_motif)
7. 51.924 1,210.162 ↑ 1.1 76,776 1

Hash Join (cost=2,448.00..258,355.89 rows=82,976 width=310) (actual time=240.670..1,210.162 rows=76,776 loops=1)

  • Hash Cond: (d.id_statutdemand = tsd.id_statutdemand)
8. 924.077 1,158.223 ↑ 1.1 76,776 1

Bitmap Heap Scan on t_demande d (cost=2,446.64..257,213.61 rows=82,976 width=100) (actual time=240.642..1,158.223 rows=76,776 loops=1)

  • Recheck Cond: ((dmd_codeautorite)::text = '142'::text)
  • Rows Removed by Index Recheck: 1,642,264
  • Filter: ((dmd_numpredemande IS NOT NULL) AND (dmd_traitable IS TRUE))
  • Rows Removed by Filter: 39,911
  • Heap Blocks: exact=31,900 lossy=53,739
9. 234.146 234.146 ↓ 1.0 125,042 1

Bitmap Index Scan on idx_t_demande_codeauto (cost=0.00..2,425.90 rows=124,462 width=0) (actual time=234.146..234.146 rows=125,042 loops=1)

  • Index Cond: ((dmd_codeautorite)::text = '142'::text)
10. 0.005 0.015 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=226) (actual time=0.015..0.015 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
11. 0.010 0.010 ↑ 1.0 16 1

Seq Scan on t_statut_demande tsd (cost=0.00..1.16 rows=16 width=226) (actual time=0.007..0.010 rows=16 loops=1)

12. 0.007 0.015 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=24) (actual time=0.015..0.015 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
13. 0.008 0.008 ↑ 1.0 30 1

Seq Scan on t_double_motif_demande tdd (cost=0.00..1.30 rows=30 width=24) (actual time=0.008..0.008 rows=30 loops=1)

14. 0.008 0.014 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=226) (actual time=0.014..0.014 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3kB
15. 0.006 0.006 ↑ 1.0 38 1

Seq Scan on t_motif_demande tmdp (cost=0.00..1.38 rows=38 width=226) (actual time=0.001..0.006 rows=38 loops=1)

16. 0.017 0.040 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=226) (actual time=0.040..0.040 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3kB
17. 0.023 0.023 ↑ 1.0 38 1

Seq Scan on t_motif_demande tmds (cost=0.00..1.38 rows=38 width=226) (actual time=0.020..0.023 rows=38 loops=1)

18. 25,796.736 25,796.736 ↑ 1.0 1 76,776

Index Scan using t_demandeur_pk on t_demandeur dr (cost=0.43..5.48 rows=1 width=68) (actual time=0.336..0.336 rows=1 loops=76,776)

  • Index Cond: (id_demand = d.id_demand)
19. 0.000 0.000 ↑ 1.0 3 76,776

Materialize (cost=0.00..1.04 rows=3 width=220) (actual time=0.000..0.000 rows=3 loops=76,776)

20. 0.011 0.011 ↑ 1.0 3 1

Seq Scan on t_sexe s (cost=0.00..1.03 rows=3 width=220) (actual time=0.010..0.011 rows=3 loops=1)

Planning time : 6.548 ms
Execution time : 27,604.453 ms