#	exclusive	inclusive	rows x	rows	loops	node
1.	4.223	18.010	↑ 1.0	1	1	→ Aggregate (cost=622.43..622.44 rows=1 width=12) (actual time=18.010..18.010 rows=1 loops=1)
2.	4.832	13.787	↓ 10.7	9443	1	→ Nested Loop (cost=5.07..618.03 rows=880 width=12) (actual time=0.349..13.787 rows=9443 loops=1)
3.	0.386	0.386	↓ 1.7	19	1	→ Index Scan using nodup_shiftbuilds on shift_build (cost=0.00..44.60 rows=11 width=8) (actual time=0.078..0.386 rows=19 loops=1) Index Cond: (v2organization_id = 483)
4.	2.927	8.569	↓ 6.2	497	19	→ Materialize (cost=5.07..562.63 rows=80 width=12) (actual time=0.014..0.451 rows=497 loops=19)
5.	0.575	5.642	↓ 6.2	497	1	→ Nested Loop (cost=5.07..562.23 rows=80 width=12) (actual time=0.262..5.642 rows=497 loops=1)
6.	0.007	0.195	↓ 2.0	4	1	→ Nested Loop (cost=0.00..19.01 rows=2 width=8) (actual time=0.137..0.195 rows=4 loops=1)
7.	0.066	0.066	↑ 1.0	1	1	→ Index Scan using v2organization_pkey on v2organization (cost=0.00..8.27 rows=1 width=4) (actual time=0.065..0.066 rows=1 loops=1) Index Cond: (id = 483) Filter: (chain_id = 54)
8.	0.122	0.122	↓ 2.0	4	1	→ Index Scan using location_aggregate_v2organization_id_key on location_aggregate (cost=0.00..10.72 rows=2 width=8) (actual time=0.070..0.122 rows=4 loops=1) Index Cond: (v2organization_id = 483)
9.	4.516	4.872	↓ 1.2	124	4	→ Bitmap Heap Scan on location_aggregate_census (cost=5.07..270.31 rows=104 width=12) (actual time=0.146..1.218 rows=124 loops=4) Recheck Cond: (location_aggregate_id = location_aggregate.id)
10.	0.356	0.356	↓ 1.2	124	4	→ Bitmap Index Scan on unit_census_unique_unit_id_to_observation_date (cost=0.00..5.04 rows=104 width=0) (actual time=0.089..0.089 rows=124 loops=4) Index Cond: (location_aggregate_id = location_aggregate.id)

"Aggregate  (cost=622.43..622.44 rows=1 width=12) (actual time=18.010..18.010 rows=1 loops=1)"
"  ->  Nested Loop  (cost=5.07..618.03 rows=880 width=12) (actual time=0.349..13.787 rows=9443 loops=1)"
"        ->  Index Scan using nodup_shiftbuilds on shift_build  (cost=0.00..44.60 rows=11 width=8) (actual time=0.078..0.386 rows=19 loops=1)"
"              Index Cond: (v2organization_id = 483)"
"        ->  Materialize  (cost=5.07..562.63 rows=80 width=12) (actual time=0.014..0.451 rows=497 loops=19)"
"              ->  Nested Loop  (cost=5.07..562.23 rows=80 width=12) (actual time=0.262..5.642 rows=497 loops=1)"
"                    ->  Nested Loop  (cost=0.00..19.01 rows=2 width=8) (actual time=0.137..0.195 rows=4 loops=1)"
"                          ->  Index Scan using v2organization_pkey on v2organization  (cost=0.00..8.27 rows=1 width=4) (actual time=0.065..0.066 rows=1 loops=1)"
"                                Index Cond: (id = 483)"
"                                Filter: (chain_id = 54)"
"                          ->  Index Scan using location_aggregate_v2organization_id_key on location_aggregate  (cost=0.00..10.72 rows=2 width=8) (actual time=0.070..0.122 rows=4 loops=1)"
"                                Index Cond: (v2organization_id = 483)"
"                    ->  Bitmap Heap Scan on location_aggregate_census  (cost=5.07..270.31 rows=104 width=12) (actual time=0.146..1.218 rows=124 loops=4)"
"                          Recheck Cond: (location_aggregate_id = location_aggregate.id)"
"                          ->  Bitmap Index Scan on unit_census_unique_unit_id_to_observation_date  (cost=0.00..5.04 rows=104 width=0) (actual time=0.089..0.089 rows=124 loops=4)"
"                                Index Cond: (location_aggregate_id = location_aggregate.id)"
"Total runtime: 18.174 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	4.223 ms	23.4 %
Bitmap Heap Scan	1	4.516 ms	25.1 %
Bitmap Index Scan	1	0.356 ms	2.0 %
Index Scan	3	0.574 ms	3.2 %
Materialize	1	2.927 ms	16.3 %
Nested Loop	3	5.414 ms	30.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
location_aggregate	1	0.122 ms	0.7 %
Index Scan	1	0.122 ms	100.0 %
location_aggregate_census	1	4.516 ms	25.1 %
Bitmap Heap Scan	1	4.516 ms	100.0 %
shift_build	1	0.386 ms	2.1 %
Index Scan	1	0.386 ms	100.0 %
v2organization	1	0.066 ms	0.4 %
Index Scan	1	0.066 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 4kN

Colorize:

Columns visibility:

Per node type stats

Per table stats