#	exclusive	inclusive	rows x	rows	loops	node
1.	42.719	359.246	↓ 417.2	71,765	1	→ Nested Loop (cost=9,616.46..48,688.20 rows=172 width=8) (actual time=216.838..359.246 rows=71,765 loops=1)
2.						CTE time_slices
3.	62.757	73.272	↓ 14.4	14,353	1	→ Function Scan on d_get_features_valid_time_slices_new_new fts (cost=3,162.57..3,172.57 rows=1,000 width=24) (actual time=71.969..73.272 rows=14,353 loops=1)
4.						Initplan (forFunction Scan)
5.	0.896	10.515	↑ 1.0	1	1	→ Aggregate (cost=3,162.31..3,162.32 rows=1 width=4) (actual time=10.515..10.515 rows=1 loops=1)
6.						Initplan (forAggregate)
7.	0.050	0.050	↑ 1.0	1	1	→ Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)
8.	9.569	9.569	↓ 5.4	14,364	1	→ Seq Scan on feature (cost=0.00..3,155.40 rows=2,658 width=4) (actual time=0.763..9.569 rows=14,364 loops=1) Filter: (data_class_id = $0) Rows Removed by Filter: 121188
9.						CTE matched_data_field
10.	10.460	234.041	↓ 10.7	71,765	1	→ Unique (cost=6,392.96..6,443.47 rows=6,735 width=36) (actual time=216.815..234.041 rows=71,765 loops=1)
11.	35.570	223.581	↓ 10.7	71,765	1	→ Sort (cost=6,392.96..6,409.79 rows=6,735 width=36) (actual time=216.814..223.581 rows=71,765 loops=1) Sort Key: ts.feature_id, df.name, ((ts.interpretation <> 'TEMPDELTA'::aixm_data.ts_interpretation)), ts.sequence DESC Sort Method: quicksort Memory: 8679kB
12.	12.467	188.011	↓ 10.7	71,765	1	→ Hash Join (cost=4,817.69..5,964.69 rows=6,735 width=36) (actual time=113.691..188.011 rows=71,765 loops=1) Hash Cond: (v_1.data_field_id = df.id)
13.	7.905	175.174	↓ 10.7	71,765	1	→ Nested Loop (cost=4,755.99..5,793.55 rows=6,735 width=24) (actual time=113.315..175.174 rows=71,765 loops=1)
14.	7.198	124.210	↓ 14.4	14,353	1	→ Hash Join (cost=4,755.56..4,789.31 rows=1,000 width=20) (actual time=113.298..124.210 rows=14,353 loops=1) Hash Cond: (fts_1.id = ts.id)
15.	75.933	75.933	↓ 14.4	14,353	1	→ CTE Scan on time_slices fts_1 (cost=0.00..20.00 rows=1,000 width=4) (actual time=71.970..75.933 rows=14,353 loops=1)
16.	23.354	41.079	↑ 1.0	140,425	1	→ Hash (cost=3,000.25..3,000.25 rows=140,425 width=20) (actual time=41.079..41.079 rows=140,425 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 9179kB
17.	17.725	17.725	↑ 1.0	140,425	1	→ Seq Scan on time_slice ts (cost=0.00..3,000.25 rows=140,425 width=20) (actual time=0.008..17.725 rows=140,425 loops=1)
18.	43.059	43.059	↑ 1.4	5	14,353	→ Index Scan using value_property_group_id_idx on value v_1 (cost=0.43..0.93 rows=7 width=8) (actual time=0.002..0.003 rows=5 loops=14,353) Index Cond: (property_group_id = ts.property_group_id)
19.	0.196	0.370	↑ 1.0	1,898	1	→ Hash (cost=37.98..37.98 rows=1,898 width=16) (actual time=0.370..0.370 rows=1,898 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 107kB
20.	0.174	0.174	↑ 1.0	1,898	1	→ Seq Scan on data_field df (cost=0.00..37.98 rows=1,898 width=16) (actual time=0.003..0.174 rows=1,898 loops=1)
21.	244.762	244.762	↓ 10.7	71,765	1	→ CTE Scan on matched_data_field mdf (cost=0.00..134.70 rows=6,735 width=12) (actual time=216.817..244.762 rows=71,765 loops=1)
22.	71.765	71.765	↑ 1.0	1	71,765	→ Index Scan using value_property_group_id_idx on value v (cost=0.43..5.77 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=71,765) Index Cond: (property_group_id = mdf.property_group_id) Filter: (mdf.data_field_id = data_field_id) Rows Removed by Filter: 4

Nested Loop  (cost=9616.46..48688.20 rows=172 width=8) (actual time=216.838..359.246 rows=71765 loops=1)
  CTE time_slices
    ->  Function Scan on d_get_features_valid_time_slices_new_new fts  (cost=3162.57..3172.57 rows=1000 width=24) (actual time=71.969..73.272 rows=14353 loops=1)
          InitPlan 2 (returns $1)
            ->  Aggregate  (cost=3162.31..3162.32 rows=1 width=4) (actual time=10.515..10.515 rows=1 loops=1)
                  InitPlan 1 (returns $0)
                    ->  Result  (cost=0.00..0.26 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)
                  ->  Seq Scan on feature  (cost=0.00..3155.40 rows=2658 width=4) (actual time=0.763..9.569 rows=14364 loops=1)
                        Filter: (data_class_id = $0)
                        Rows Removed by Filter: 121188
  CTE matched_data_field
    ->  Unique  (cost=6392.96..6443.47 rows=6735 width=36) (actual time=216.815..234.041 rows=71765 loops=1)
          ->  Sort  (cost=6392.96..6409.79 rows=6735 width=36) (actual time=216.814..223.581 rows=71765 loops=1)
                Sort Key: ts.feature_id, df.name, ((ts.interpretation <> 'TEMPDELTA'::aixm_data.ts_interpretation)), ts.sequence DESC
                Sort Method: quicksort  Memory: 8679kB
                ->  Hash Join  (cost=4817.69..5964.69 rows=6735 width=36) (actual time=113.691..188.011 rows=71765 loops=1)
                      Hash Cond: (v_1.data_field_id = df.id)
                      ->  Nested Loop  (cost=4755.99..5793.55 rows=6735 width=24) (actual time=113.315..175.174 rows=71765 loops=1)
                            ->  Hash Join  (cost=4755.56..4789.31 rows=1000 width=20) (actual time=113.298..124.210 rows=14353 loops=1)
                                  Hash Cond: (fts_1.id = ts.id)
                                  ->  CTE Scan on time_slices fts_1  (cost=0.00..20.00 rows=1000 width=4) (actual time=71.970..75.933 rows=14353 loops=1)
                                  ->  Hash  (cost=3000.25..3000.25 rows=140425 width=20) (actual time=41.079..41.079 rows=140425 loops=1)
                                        Buckets: 262144  Batches: 1  Memory Usage: 9179kB
                                        ->  Seq Scan on time_slice ts  (cost=0.00..3000.25 rows=140425 width=20) (actual time=0.008..17.725 rows=140425 loops=1)
                            ->  Index Scan using value_property_group_id_idx on value v_1  (cost=0.43..0.93 rows=7 width=8) (actual time=0.002..0.003 rows=5 loops=14353)
                                  Index Cond: (property_group_id = ts.property_group_id)
                      ->  Hash  (cost=37.98..37.98 rows=1898 width=16) (actual time=0.370..0.370 rows=1898 loops=1)
                            Buckets: 2048  Batches: 1  Memory Usage: 107kB
                            ->  Seq Scan on data_field df  (cost=0.00..37.98 rows=1898 width=16) (actual time=0.003..0.174 rows=1898 loops=1)
  ->  CTE Scan on matched_data_field mdf  (cost=0.00..134.70 rows=6735 width=12) (actual time=216.817..244.762 rows=71765 loops=1)
  ->  Index Scan using value_property_group_id_idx on value v  (cost=0.43..5.77 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=71765)
        Index Cond: (property_group_id = mdf.property_group_id)
        Filter: (mdf.data_field_id = data_field_id)
        Rows Removed by Filter: 4
Planning time: 0.461 ms
Execution time: 364.687 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	0.896 ms	0.2 %
CTE Scan	2	320.695 ms	89.3 %
Function Scan	1	62.757 ms	17.5 %
Hash	2	23.550 ms	6.6 %
Hash Join	2	19.665 ms	5.5 %
Index Scan	2	114.824 ms	32.0 %
Nested Loop	2	50.624 ms	14.1 %
Result	1	0.050 ms	0.0 %
Seq Scan	3	27.468 ms	7.6 %
Sort	1	35.570 ms	9.9 %
Unique	1	10.460 ms	2.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
data_field	1	0.174 ms	0.0 %
Seq Scan	1	0.174 ms	100.0 %
feature	1	9.569 ms	2.7 %
Seq Scan	1	9.569 ms	100.0 %
time_slice	1	17.725 ms	4.9 %
Seq Scan	1	17.725 ms	100.0 %
value	2	114.824 ms	32.0 %
Index Scan	2	114.824 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dxKD

Color mode:

Visible columns:

Per node type stats

Per table stats