#	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

"HashAggregate  (cost=5943.56..35664.02 rows=1516 width=81) (actual time=330.573..448254.635 rows=7981 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"
"  ->  Hash Right Join  (cost=5206.19..5909.45 rows=1516 width=81) (actual time=199.309..237.082 rows=8007 loops=1)"
"        Hash Cond: (lower((no.name)::text) = lower(sj.inf_nature))"
"        ->  Seq Scan on nature_offense no  (cost=0.00..528.55 rows=15955 width=121) (actual time=0.004..1.804 rows=15955 loops=1)"
"        ->  Hash  (cost=5205.95..5205.95 rows=19 width=129) (actual time=199.276..199.276 rows=8007 loops=1)"
"              Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 1374kB"
"              ->  Hash Left Join  (cost=4383.94..5205.95 rows=19 width=129) (actual time=153.934..185.626 rows=8007 loops=1)"
"                    Hash Cond: (lower(fc."libellé_court") = lower((s.short_name)::text))"
"                    ->  Hash Left Join  (cost=4381.63..5203.34 rows=19 width=127) (actual time=153.865..175.716 rows=8007 loops=1)"
"                          Hash Cond: (sj.idfichier_consultation = fc.idfichier_consultation)"
"                          ->  Merge Join  (cost=4379.33..5200.77 rows=19 width=126) (actual time=153.820..171.630 rows=8007 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  (...)"
"                                ->  Sort  (cost=2509.70..2529.80 rows=8043 width=137) (actual time=66.012..67.182 rows=8043 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"
"                                      ->  Hash Join  (cost=1543.95..1987.97 rows=8043 width=137) (actual time=20.158..41.748 rows=8043 loops=1)"
"                                            Hash Cond: (sj.idcnil = cni.idcnil)"
"                                            ->  Seq Scan on suites_judiciaires sj  (cost=0.00..333.43 rows=8043 width=124) (actual time=0.005..1.845 rows=8043 loops=1)"
"                                            ->  Hash  (cost=1288.42..1288.42 rows=20442 width=25) (actual time=19.935..19.935 rows=20442 loops=1)"
"                                                  Buckets: 32768  Batches: 1  Memory Usage: 1454kB"
"                                                  ->  Seq Scan on cnile cni  (cost=0.00..1288.42 rows=20442 width=25) (actual time=0.317..10.742 rows=20442 loops=1)"
"                                ->  Sort  (cost=1869.63..1917.96 rows=19332 width=27) (actual time=87.797..89.998 rows=24141 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"
"                                      ->  Seq Scan on identity i  (cost=0.00..493.32 rows=19332 width=27) (actual time=0.016..34.186 rows=19332 loops=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"
"                                ->  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)"
"                    ->  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"
"                          ->  Seq Scan on soft s  (cost=0.00..1.58 rows=58 width=14) (actual time=0.009..0.020 rows=58 loops=1)"
"  SubPlan 2"
"    ->  Result  (cost=8.13..8.14 rows=1 width=0) (actual time=29.438..29.438 rows=1 loops=7981)"
"          InitPlan 1 (returns $1)"
"            ->  Limit  (cost=0.29..8.13 rows=1 width=8) (actual time=29.437..29.437 rows=0 loops=7981)"
"                  ->  Index Scan using idx_34552_primary on unit u  (cost=0.29..2086.12 rows=266 width=8) (actual time=29.436..29.436 rows=0 loops=7981)"
"                        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"
"  SubPlan 4"
"    ->  Result  (cost=11.44..11.45 rows=1 width=0) (actual time=26.694..26.695 rows=1 loops=7981)"
"          InitPlan 3 (returns $3)"
"            ->  Limit  (cost=0.29..11.44 rows=1 width=8) (actual time=26.694..26.694 rows=0 loops=7981)"
"                  ->  Index Scan using idx_34515_primary on town tinf  (cost=0.29..2163.98 rows=194 width=8) (actual time=26.694..26.694 rows=0 loops=7981)"
"                        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: 448256.770 ms"

Per node type stats

node type	count	sum of times	% of query
Hash	4	22.898 ms	0.0 %
Hash Join	1	19.968 ms	0.0 %
Hash Left Join	2	13.904 ms	0.0 %
Hash Right Join	1	36.002 ms	0.0 %
HashAggregate	1	20.080 ms	0.0 %
Index Scan	2	447,973.530 ms	99.9 %
Limit	2	7.981 ms	0.0 %
Merge Join	1	14.450 ms	0.0 %
Result	2	15.962 ms	0.0 %
Seq Scan	6	48.614 ms	0.0 %
Sort	2	81.246 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
cnile	1	10.742 ms	0.0 %
Seq Scan	1	10.742 ms	100.0 %
fichier_consultation	1	0.017 ms	0.0 %
Seq Scan	1	0.017 ms	100.0 %
identity	1	34.186 ms	0.0 %
Seq Scan	1	34.186 ms	100.0 %
nature_offense	1	1.804 ms	0.0 %
Seq Scan	1	1.804 ms	100.0 %
soft	1	0.020 ms	0.0 %
Seq Scan	1	0.020 ms	100.0 %
suites_judiciaires	1	1.845 ms	0.0 %
Seq Scan	1	1.845 ms	100.0 %
town	1	213,044.814 ms	47.5 %
Index Scan	1	213,044.814 ms	100.0 %
unit	1	234,928.716 ms	52.4 %
Index Scan	1	234,928.716 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x5kU8

Color mode:

Visible columns:

Per node type stats

Per table stats