explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x5kU8

Settings
# exclusive inclusive rows x rows loops node
1. 20.080 448,254.635 ↓ 5.3 7,981 1

HashAggregate (cost=5,943.56..35,664.02 rows=1,516 width=81) (actual time=330.573..448,254.635 rows=7,981 loops=1)

  • Group Key: i.id, sj.auteur, s.id, sj.inf_date, sj.inf_num_pv, sj.inf_datepv, no.id, sj.inf_unitepv, sj.inf_localite
2. 36.002 237.082 ↓ 5.3 8,007 1

Hash Right Join (cost=5,206.19..5,909.45 rows=1,516 width=81) (actual time=199.309..237.082 rows=8,007 loops=1)

  • Hash Cond: (lower((no.name)::text) = lower(sj.inf_nature))
3. 1.804 1.804 ↑ 1.0 15,955 1

Seq Scan on nature_offense no (cost=0.00..528.55 rows=15,955 width=121) (actual time=0.004..1.804 rows=15,955 loops=1)

4. 13.650 199.276 ↓ 421.4 8,007 1

Hash (cost=5,205.95..5,205.95 rows=19 width=129) (actual time=199.276..199.276 rows=8,007 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1374kB
5. 9.850 185.626 ↓ 421.4 8,007 1

Hash Left Join (cost=4,383.94..5,205.95 rows=19 width=129) (actual time=153.934..185.626 rows=8,007 loops=1)

  • Hash Cond: (lower(fc."libellé_court") = lower((s.short_name)::text))
6. 4.054 175.716 ↓ 421.4 8,007 1

Hash Left Join (cost=4,381.63..5,203.34 rows=19 width=127) (actual time=153.865..175.716 rows=8,007 loops=1)

  • Hash Cond: (sj.idfichier_consultation = fc.idfichier_consultation)
7. 14.450 171.630 ↓ 421.4 8,007 1

Merge Join (cost=4,379.33..5,200.77 rows=19 width=126) (actual time=153.820..171.630 rows=8,007 loops=1)

  • Merge Cond: (((lower(cni.nom)) = (lower((i.last_name)::text))) AND ((lower(cni.prenom)) = (lower((i.first_name)::text))) AND ((CASE WHEN (cni.naissance_date IS NULL) THEN now() ELSE (cni.naissance_date)::timestamp with time (...)
8. 25.434 67.182 ↑ 1.0 8,043 1

Sort (cost=2,509.70..2,529.80 rows=8,043 width=137) (actual time=66.012..67.182 rows=8,043 loops=1)

  • Sort Key: (lower(cni.nom)), (lower(cni.prenom)), (CASE WHEN (cni.naissance_date IS NULL) THEN now() ELSE (cni.naissance_date)::timestamp with time zone END)
  • Sort Method: quicksort Memory: 2425kB
9. 19.968 41.748 ↑ 1.0 8,043 1

Hash Join (cost=1,543.95..1,987.97 rows=8,043 width=137) (actual time=20.158..41.748 rows=8,043 loops=1)

  • Hash Cond: (sj.idcnil = cni.idcnil)
10. 1.845 1.845 ↑ 1.0 8,043 1

Seq Scan on suites_judiciaires sj (cost=0.00..333.43 rows=8,043 width=124) (actual time=0.005..1.845 rows=8,043 loops=1)

11. 9.193 19.935 ↑ 1.0 20,442 1

Hash (cost=1,288.42..1,288.42 rows=20,442 width=25) (actual time=19.935..19.935 rows=20,442 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1454kB
12. 10.742 10.742 ↑ 1.0 20,442 1

Seq Scan on cnile cni (cost=0.00..1,288.42 rows=20,442 width=25) (actual time=0.317..10.742 rows=20,442 loops=1)

13. 55.812 89.998 ↓ 1.2 24,141 1

Sort (cost=1,869.63..1,917.96 rows=19,332 width=27) (actual time=87.797..89.998 rows=24,141 loops=1)

  • Sort Key: (lower((i.last_name)::text)), (lower((i.first_name)::text)), (CASE WHEN (i.birth_date IS NULL) THEN now() ELSE (i.birth_date)::timestamp with time zone END)
  • Sort Method: quicksort Memory: 3197kB
14. 34.186 34.186 ↑ 1.0 19,332 1

Seq Scan on identity i (cost=0.00..493.32 rows=19,332 width=27) (actual time=0.016..34.186 rows=19,332 loops=1)

15. 0.015 0.032 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=11) (actual time=0.032..0.032 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
16. 0.017 0.017 ↑ 1.0 58 1

Seq Scan on fichier_consultation fc (cost=0.00..1.58 rows=58 width=11) (actual time=0.005..0.017 rows=58 loops=1)

17. 0.040 0.060 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=14) (actual time=0.060..0.060 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
18. 0.020 0.020 ↑ 1.0 58 1

Seq Scan on soft s (cost=0.00..1.58 rows=58 width=14) (actual time=0.009..0.020 rows=58 loops=1)

19.          

SubPlan (for HashAggregate)

20. 7.981 234,944.678 ↑ 1.0 1 7,981

Result (cost=8.13..8.14 rows=1 width=0) (actual time=29.438..29.438 rows=1 loops=7,981)

21.          

Initplan (for Result)

22. 7.981 234,936.697 ↓ 0.0 0 7,981

Limit (cost=0.29..8.13 rows=1 width=8) (actual time=29.437..29.437 rows=0 loops=7,981)

23. 234,928.716 234,928.716 ↓ 0.0 0 7,981

Index Scan using idx_34552_primary on unit u (cost=0.29..2,086.12 rows=266 width=8) (actual time=29.436..29.436 rows=0 loops=7,981)

  • Index Cond: (id IS NOT NULL)
  • Filter: ((((id)::character(1))::text = lower(sj.inf_unitepv)) OR (lower((short_name)::text) = lower(sj.inf_unitepv)) OR (lower((name)::text) = lower(sj.inf_unitepv)))
  • Rows Removed by Filter: 12263
24. 7.981 213,052.795 ↑ 1.0 1 7,981

Result (cost=11.44..11.45 rows=1 width=0) (actual time=26.694..26.695 rows=1 loops=7,981)

25.          

Initplan (for Result)

26. 0.000 213,044.814 ↓ 0.0 0 7,981

Limit (cost=0.29..11.44 rows=1 width=8) (actual time=26.694..26.694 rows=0 loops=7,981)

27. 213,044.814 213,044.814 ↓ 0.0 0 7,981

Index Scan using idx_34515_primary on town tinf (cost=0.29..2,163.98 rows=194 width=8) (actual time=26.694..26.694 rows=0 loops=7,981)

  • Index Cond: (id IS NOT NULL)
  • Filter: (lower((name)::text) = lower(sj.inf_localite))
  • Rows Removed by Filter: 35477
Planning time : 2.310 ms
Execution time : 448,256.770 ms