#	exclusive	inclusive	rows x	rows	loops	node
1.	446.592	446.592	↓ 3,081.1	135,569	1	→ CTE Scan on valid_time_slices vts (cost=14,966.53..14,967.41 rows=44 width=8) (actual time=394.553..446.592 rows=135,569 loops=1)
2.						CTE valid_time_slices_by_time
3.	37.529	178.144	↓ 3.5	139,956	1	→ Hash Join (cost=7,937.16..12,235.49 rows=40,121 width=20) (actual time=106.780..178.144 rows=139,956 loops=1) Hash Cond: (feature.id = ts.feature_id)
4.	60.613	72.478	↑ 1.0	135,552	1	→ HashAggregate (cost=3,155.40..4,510.92 rows=135,552 width=4) (actual time=38.352..72.478 rows=135,552 loops=1) Group Key: feature.id
5.	11.865	11.865	↑ 1.0	135,552	1	→ Seq Scan on feature (cost=0.00..2,816.52 rows=135,552 width=4) (actual time=0.006..11.865 rows=135,552 loops=1)
6.	25.510	68.137	↓ 3.5	139,956	1	→ Hash (cost=4,280.25..4,280.25 rows=40,121 width=20) (actual time=68.137..68.137 rows=139,956 loops=1) Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 9148kB
7.	20.361	42.627	↓ 3.5	139,956	1	→ Hash Join (cost=1.13..4,280.25 rows=40,121 width=20) (actual time=0.042..42.627 rows=139,956 loops=1) Hash Cond: (ts.project_id = p.id)
8.	22.254	22.254	↑ 1.0	140,397	1	→ Seq Scan on time_slice ts (cost=0.00..3,351.31 rows=140,425 width=24) (actual time=0.017..22.254 rows=140,397 loops=1) Filter: (valid_time @> '2018-11-26 03:00:00'::timestamp without time zone) Rows Removed by Filter: 28
9.	0.004	0.012	↓ 2.0	4	1	→ Hash (cost=1.10..1.10 rows=2 width=4) (actual time=0.012..0.012 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
10.	0.008	0.008	↓ 2.0	4	1	→ Seq Scan on project p (cost=0.00..1.10 rows=2 width=4) (actual time=0.007..0.008 rows=4 loops=1) Filter: ((status = 'PUBLISHED'::wf.project_status) OR (id = 7)) Rows Removed by Filter: 3
11.						CTE last_baseline
12.	17.584	278.649	↓ 67,165.5	134,331	1	→ Unique (cost=910.41..911.42 rows=2 width=20) (actual time=244.331..278.649 rows=134,331 loops=1)
13.	50.032	261.065	↓ 690.1	138,713	1	→ Sort (cost=910.41..910.91 rows=201 width=20) (actual time=244.329..261.065 rows=138,713 loops=1) Sort Key: vts_1.feature_id, vts_1.sequence DESC, vts_1.correction DESC Sort Method: quicksort Memory: 16958kB
14.	211.033	211.033	↓ 690.1	138,713	1	→ CTE Scan on valid_time_slices_by_time vts_1 (cost=0.00..902.72 rows=201 width=20) (actual time=106.783..211.033 rows=138,713 loops=1) Filter: (interpretation = 'BASELINE'::aixm_data.ts_interpretation) Rows Removed by Filter: 1243
15.						CTE non_overlapped_permdelta
16.	0.159	37.097	↓ 59.0	1,238	1	→ Unique (cost=905.64..906.15 rows=21 width=16) (actual time=36.853..37.097 rows=1,238 loops=1)
17.	0.389	36.938	↓ 18.2	1,239	1	→ Sort (cost=905.64..905.81 rows=68 width=16) (actual time=36.852..36.938 rows=1,239 loops=1) Sort Key: vts_2.feature_id, vts_2.sequence DESC, vts_2.correction DESC, vts_2.id DESC Sort Method: quicksort Memory: 107kB
18.	0.533	36.549	↓ 18.2	1,239	1	→ Hash Left Join (cost=0.07..903.57 rows=68 width=16) (actual time=27.032..36.549 rows=1,239 loops=1) Hash Cond: (vts_2.feature_id = lb.feature_id) Filter: ((lb.sequence < vts_2.sequence) OR (lb.id IS NULL)) Rows Removed by Filter: 4
19.	9.010	9.010	↓ 6.2	1,243	1	→ CTE Scan on valid_time_slices_by_time vts_2 (cost=0.00..902.72 rows=201 width=16) (actual time=0.015..9.010 rows=1,243 loops=1) Filter: (interpretation = 'PERMDELTA'::aixm_data.ts_interpretation) Rows Removed by Filter: 138713
20.	15.074	27.006	↓ 67,165.5	134,331	1	→ Hash (cost=0.04..0.04 rows=2 width=12) (actual time=27.006..27.006 rows=134,331 loops=1) Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7821kB
21.	11.932	11.932	↓ 67,165.5	134,331	1	→ CTE Scan on last_baseline lb (cost=0.00..0.04 rows=2 width=12) (actual time=0.000..11.932 rows=134,331 loops=1)
22.						CTE valid_time_slices
23.	73.531	426.244	↓ 3,081.1	135,569	1	→ HashAggregate (cost=913.04..913.48 rows=44 width=12) (actual time=394.552..426.244 rows=135,569 loops=1) Group Key: lb_1.id, lb_1.feature_id
24.	6.939	352.713	↓ 3,081.1	135,569	1	→ Append (cost=0.00..912.82 rows=44 width=12) (actual time=244.332..352.713 rows=135,569 loops=1)
25.	300.166	300.166	↓ 67,165.5	134,331	1	→ CTE Scan on last_baseline lb_1 (cost=0.00..0.04 rows=2 width=8) (actual time=244.332..300.166 rows=134,331 loops=1)
26.	37.279	37.279	↓ 59.0	1,238	1	→ CTE Scan on non_overlapped_permdelta pd (cost=0.00..0.42 rows=21 width=8) (actual time=36.855..37.279 rows=1,238 loops=1)
27.	0.001	8.329	↓ 0.0	0	1	→ Subquery Scan on SELECT 3 (cost=910.41..912.13 rows=21 width=16) (actual time=8.329..8.329 rows=0 loops=1)
28.	0.000	8.328	↓ 0.0	0	1	→ Unique (cost=910.41..911.92 rows=21 width=16) (actual time=8.328..8.328 rows=0 loops=1)
29.	0.010	8.328	↓ 0.0	0	1	→ Sort (cost=910.41..910.91 rows=201 width=16) (actual time=8.328..8.328 rows=0 loops=1) Sort Key: td.feature_id, td.sequence DESC, td.correction DESC, td.id DESC Sort Method: quicksort Memory: 25kB
30.	8.318	8.318	↓ 0.0	0	1	→ CTE Scan on valid_time_slices_by_time td (cost=0.00..902.72 rows=201 width=16) (actual time=8.318..8.318 rows=0 loops=1) Filter: (interpretation = 'TEMPDELTA'::aixm_data.ts_interpretation) Rows Removed by Filter: 139956

CTE Scan on valid_time_slices vts  (cost=14966.53..14967.41 rows=44 width=8) (actual time=394.553..446.592 rows=135569 loops=1)
  CTE valid_time_slices_by_time
    ->  Hash Join  (cost=7937.16..12235.49 rows=40121 width=20) (actual time=106.780..178.144 rows=139956 loops=1)
          Hash Cond: (feature.id = ts.feature_id)
          ->  HashAggregate  (cost=3155.40..4510.92 rows=135552 width=4) (actual time=38.352..72.478 rows=135552 loops=1)
                Group Key: feature.id
                ->  Seq Scan on feature  (cost=0.00..2816.52 rows=135552 width=4) (actual time=0.006..11.865 rows=135552 loops=1)
          ->  Hash  (cost=4280.25..4280.25 rows=40121 width=20) (actual time=68.137..68.137 rows=139956 loops=1)
                Buckets: 262144 (originally 65536)  Batches: 1 (originally 1)  Memory Usage: 9148kB
                ->  Hash Join  (cost=1.13..4280.25 rows=40121 width=20) (actual time=0.042..42.627 rows=139956 loops=1)
                      Hash Cond: (ts.project_id = p.id)
                      ->  Seq Scan on time_slice ts  (cost=0.00..3351.31 rows=140425 width=24) (actual time=0.017..22.254 rows=140397 loops=1)
                            Filter: (valid_time @> '2018-11-26 03:00:00'::timestamp without time zone)
                            Rows Removed by Filter: 28
                      ->  Hash  (cost=1.10..1.10 rows=2 width=4) (actual time=0.012..0.012 rows=4 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                            ->  Seq Scan on project p  (cost=0.00..1.10 rows=2 width=4) (actual time=0.007..0.008 rows=4 loops=1)
                                  Filter: ((status = 'PUBLISHED'::wf.project_status) OR (id = 7))
                                  Rows Removed by Filter: 3
  CTE last_baseline
    ->  Unique  (cost=910.41..911.42 rows=2 width=20) (actual time=244.331..278.649 rows=134331 loops=1)
          ->  Sort  (cost=910.41..910.91 rows=201 width=20) (actual time=244.329..261.065 rows=138713 loops=1)
                Sort Key: vts_1.feature_id, vts_1.sequence DESC, vts_1.correction DESC
                Sort Method: quicksort  Memory: 16958kB
                ->  CTE Scan on valid_time_slices_by_time vts_1  (cost=0.00..902.72 rows=201 width=20) (actual time=106.783..211.033 rows=138713 loops=1)
                      Filter: (interpretation = 'BASELINE'::aixm_data.ts_interpretation)
                      Rows Removed by Filter: 1243
  CTE non_overlapped_permdelta
    ->  Unique  (cost=905.64..906.15 rows=21 width=16) (actual time=36.853..37.097 rows=1238 loops=1)
          ->  Sort  (cost=905.64..905.81 rows=68 width=16) (actual time=36.852..36.938 rows=1239 loops=1)
                Sort Key: vts_2.feature_id, vts_2.sequence DESC, vts_2.correction DESC, vts_2.id DESC
                Sort Method: quicksort  Memory: 107kB
                ->  Hash Left Join  (cost=0.07..903.57 rows=68 width=16) (actual time=27.032..36.549 rows=1239 loops=1)
                      Hash Cond: (vts_2.feature_id = lb.feature_id)
                      Filter: ((lb.sequence < vts_2.sequence) OR (lb.id IS NULL))
                      Rows Removed by Filter: 4
                      ->  CTE Scan on valid_time_slices_by_time vts_2  (cost=0.00..902.72 rows=201 width=16) (actual time=0.015..9.010 rows=1243 loops=1)
                            Filter: (interpretation = 'PERMDELTA'::aixm_data.ts_interpretation)
                            Rows Removed by Filter: 138713
                      ->  Hash  (cost=0.04..0.04 rows=2 width=12) (actual time=27.006..27.006 rows=134331 loops=1)
                            Buckets: 262144 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 7821kB
                            ->  CTE Scan on last_baseline lb  (cost=0.00..0.04 rows=2 width=12) (actual time=0.000..11.932 rows=134331 loops=1)
  CTE valid_time_slices
    ->  HashAggregate  (cost=913.04..913.48 rows=44 width=12) (actual time=394.552..426.244 rows=135569 loops=1)
          Group Key: lb_1.id, lb_1.feature_id
          ->  Append  (cost=0.00..912.82 rows=44 width=12) (actual time=244.332..352.713 rows=135569 loops=1)
                ->  CTE Scan on last_baseline lb_1  (cost=0.00..0.04 rows=2 width=8) (actual time=244.332..300.166 rows=134331 loops=1)
                ->  CTE Scan on non_overlapped_permdelta pd  (cost=0.00..0.42 rows=21 width=8) (actual time=36.855..37.279 rows=1238 loops=1)
                ->  Subquery Scan on *SELECT* 3  (cost=910.41..912.13 rows=21 width=16) (actual time=8.329..8.329 rows=0 loops=1)
                      ->  Unique  (cost=910.41..911.92 rows=21 width=16) (actual time=8.328..8.328 rows=0 loops=1)
                            ->  Sort  (cost=910.41..910.91 rows=201 width=16) (actual time=8.328..8.328 rows=0 loops=1)
                                  Sort Key: td.feature_id, td.sequence DESC, td.correction DESC, td.id DESC
                                  Sort Method: quicksort  Memory: 25kB
                                  ->  CTE Scan on valid_time_slices_by_time td  (cost=0.00..902.72 rows=201 width=16) (actual time=8.318..8.318 rows=0 loops=1)
                                        Filter: (interpretation = 'TEMPDELTA'::aixm_data.ts_interpretation)
                                        Rows Removed by Filter: 139956
Planning time: 0.267 ms
Execution time: 454.251 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	6.939 ms	1.6 %
CTE Scan	7	1,024.330 ms	229.4 %
Hash	3	40.588 ms	9.1 %
Hash Join	2	57.890 ms	13.0 %
Hash Left Join	1	0.533 ms	0.1 %
HashAggregate	2	134.144 ms	30.0 %
Seq Scan	3	34.127 ms	7.6 %
Sort	3	50.431 ms	11.3 %
Subquery Scan	1	0.001 ms	0.0 %
Unique	3	17.743 ms	4.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
feature	1	11.865 ms	2.7 %
Seq Scan	1	11.865 ms	100.0 %
project	1	0.008 ms	0.0 %
Seq Scan	1	0.008 ms	100.0 %
time_slice	1	22.254 ms	5.0 %
Seq Scan	1	22.254 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: naRg

Color mode:

Visible columns:

Per node type stats

Per table stats