#	exclusive	inclusive	rows x	rows	loops	node
1.	4.086	374,593.005	↓ 5.0	5	1	→ HashAggregate (cost=61,382.43..61,382.44 rows=1 width=36) (actual time=374,593.004..374,593.005 rows=5 loops=1) Group Key: date.year4, date.year_month_number, dim_airline.airline_id, dim_task_type.task_type_id, dim_station.station_id, dim_planning_state.planning_state_id
2.	179.159	374,588.919	↓ 1,911.0	1,911	1	→ Nested Loop (cost=0.56..61,382.41 rows=1 width=36) (actual time=17,113.136..374,588.919 rows=1,911 loops=1) Join Filter: (bridge_snapshot_state.planning_state_key = dim_planning_state.planning_state_key) Rows Removed by Join Filter: 114,037
3.	231,091.567	374,293.812	↓ 115,948.0	115,948	1	→ Nested Loop (cost=0.56..61,381.36 rows=1 width=37) (actual time=0.914..374,293.812 rows=115,948 loops=1) Join Filter: (fact_task.snapshotstate_key = bridge_snapshot_state.snapshotstate_key) Rows Removed by Join Filter: 2,268,290,724
4.	263.523	5,340.073	↓ 115,948.0	115,948	1	→ Nested Loop (cost=0.56..60,815.17 rows=1 width=40) (actual time=0.858..5,340.073 rows=115,948 loops=1) Join Filter: (fact_task.task_type_key = dim_task_type.task_type_key) Rows Removed by Join Filter: 463,792
5.	133.264	1,598.110	↓ 115,948.0	115,948	1	→ Nested Loop (cost=0.56..60,806.74 rows=1 width=44) (actual time=0.847..1,598.110 rows=115,948 loops=1) Join Filter: (fact_task.airline_key = dim_airline.airline_key) Rows Removed by Join Filter: 1,063,615
6.	0.324	0.324	↑ 1.0	1	1	→ Seq Scan on dim_airline (cost=0.00..44.74 rows=1 width=12) (actual time=0.321..0.324 rows=1 loops=1) Filter: (airline_id = 'SXS'::text) Rows Removed by Filter: 1,978
7.	139.063	1,464.522	↓ 981.3	1,179,563	1	→ Nested Loop (cost=0.56..60,746.97 rows=1,202 width=48) (actual time=0.407..1,464.522 rows=1,179,563 loops=1)
8.	0.019	0.019	↑ 1.0	1	1	→ Seq Scan on dim_station (cost=0.00..1.29 rows=1 width=8) (actual time=0.016..0.019 rows=1 loops=1) Filter: (station_id = 'FRA'::text) Rows Removed by Filter: 22
9.	153.896	1,325.440	↓ 76.5	1,179,563	1	→ Nested Loop (cost=0.56..60,591.49 rows=15,420 width=48) (actual time=0.389..1,325.440 rows=1,179,563 loops=1)
10.	1.015	1.015	↓ 10.3	31	1	→ Seq Scan on dim_date date (cost=0.00..156.90 rows=3 width=22) (actual time=0.341..1.015 rows=31 loops=1) Filter: ((year4 = '2019'::text) AND (year_month_number = '2019-10'::text)) Rows Removed by Filter: 3,629
11.	1,170.529	1,170.529	↓ 7.4	38,050	31	→ Index Scan using fact_task_begin_date_key_idx on fact_task (cost=0.56..20,093.46 rows=5,140 width=44) (actual time=0.027..37.759 rows=38,050 loops=31) Index Cond: ((begin_date_key = date.date_key) AND (hdp_section_id = dim_station.hdp_section_id) AND (hdp_section_id = ANY ('{AVI,BER,BEX,BRE,BRX,CGX,DRS,DUS,EGR,FRA,HAJ,HAM,HQCGX,HQDUS,HQHAJ,HQHAM,HQMUC,LEJ,MUC,SGS,TST,TXL,TXX}'::text[])))
12.	3,478.440	3,478.440	↑ 1.0	5	115,948	→ Seq Scan on dim_task_type (cost=0.00..8.37 rows=5 width=12) (actual time=0.007..0.030 rows=5 loops=115,948) Filter: (task_type_id = ANY ('{B,CI,LL,R,W&B}'::text[])) Rows Removed by Filter: 141
13.	137,862.172	137,862.172	↑ 1.0	19,564	115,948	→ Seq Scan on bridge_snapshot_state (cost=0.00..321.64 rows=19,564 width=19) (actual time=0.002..1.189 rows=19,564 loops=115,948)
14.	115.948	115.948	↑ 1.0	1	115,948	→ Seq Scan on dim_planning_state (cost=0.00..1.04 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=115,948) Filter: (planning_state_id = 'ACTUAL'::text) Rows Removed by Filter: 2

Planning time	:	4.210 ms
Execution time	:	374,593.194 ms

HashAggregate  (cost=61382.43..61382.44 rows=1 width=36) (actual time=374593.004..374593.005 rows=5 loops=1)
  Group Key: date.year4, date.year_month_number, dim_airline.airline_id, dim_task_type.task_type_id, dim_station.station_id, dim_planning_state.planning_state_id
  ->  Nested Loop  (cost=0.56..61382.41 rows=1 width=36) (actual time=17113.136..374588.919 rows=1911 loops=1)
        Join Filter: (bridge_snapshot_state.planning_state_key = dim_planning_state.planning_state_key)
        Rows Removed by Join Filter: 114037
        ->  Nested Loop  (cost=0.56..61381.36 rows=1 width=37) (actual time=0.914..374293.812 rows=115948 loops=1)
              Join Filter: (fact_task.snapshotstate_key = bridge_snapshot_state.snapshotstate_key)
              Rows Removed by Join Filter: 2268290724
              ->  Nested Loop  (cost=0.56..60815.17 rows=1 width=40) (actual time=0.858..5340.073 rows=115948 loops=1)
                    Join Filter: (fact_task.task_type_key = dim_task_type.task_type_key)
                    Rows Removed by Join Filter: 463792
                    ->  Nested Loop  (cost=0.56..60806.74 rows=1 width=44) (actual time=0.847..1598.110 rows=115948 loops=1)
                          Join Filter: (fact_task.airline_key = dim_airline.airline_key)
                          Rows Removed by Join Filter: 1063615
                          ->  Seq Scan on dim_airline  (cost=0.00..44.74 rows=1 width=12) (actual time=0.321..0.324 rows=1 loops=1)
                                Filter: (airline_id = 'SXS'::text)
                                Rows Removed by Filter: 1978
                          ->  Nested Loop  (cost=0.56..60746.97 rows=1202 width=48) (actual time=0.407..1464.522 rows=1179563 loops=1)
                                ->  Seq Scan on dim_station  (cost=0.00..1.29 rows=1 width=8) (actual time=0.016..0.019 rows=1 loops=1)
                                      Filter: (station_id = 'FRA'::text)
                                      Rows Removed by Filter: 22
                                ->  Nested Loop  (cost=0.56..60591.49 rows=15420 width=48) (actual time=0.389..1325.440 rows=1179563 loops=1)
                                      ->  Seq Scan on dim_date date  (cost=0.00..156.90 rows=3 width=22) (actual time=0.341..1.015 rows=31 loops=1)
                                            Filter: ((year4 = '2019'::text) AND (year_month_number = '2019-10'::text))
                                            Rows Removed by Filter: 3629
                                      ->  Index Scan using fact_task_begin_date_key_idx on fact_task  (cost=0.56..20093.46 rows=5140 width=44) (actual time=0.027..37.759 rows=38050 loops=31)
                                            Index Cond: ((begin_date_key = date.date_key) AND (hdp_section_id = dim_station.hdp_section_id) AND (hdp_section_id = ANY ('{AVI,BER,BEX,BRE,BRX,CGX,DRS,DUS,EGR,FRA,HAJ,HAM,HQCGX,HQDUS,HQHAJ,HQHAM,HQMUC,LEJ,MUC,SGS,TST,TXL,TXX}'::text[])))
                    ->  Seq Scan on dim_task_type  (cost=0.00..8.37 rows=5 width=12) (actual time=0.007..0.030 rows=5 loops=115948)
                          Filter: (task_type_id = ANY ('{B,CI,LL,R,W&B}'::text[]))
                          Rows Removed by Filter: 141
              ->  Seq Scan on bridge_snapshot_state  (cost=0.00..321.64 rows=19564 width=19) (actual time=0.002..1.189 rows=19564 loops=115948)
        ->  Seq Scan on dim_planning_state  (cost=0.00..1.04 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=115948)
              Filter: (planning_state_id = 'ACTUAL'::text)
              Rows Removed by Filter: 2
Planning time: 4.210 ms
Execution time: 374593.194 ms

Per node type stats

node type	count	sum of times	% of query
HashAggregate	1	4.086 ms	0.0 %
Index Scan	1	1,170.529 ms	0.3 %
Nested Loop	6	231,960.472 ms	61.9 %
Seq Scan	6	141,457.918 ms	37.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
bridge_snapshot_state	1	137,862.172 ms	36.8 %
Seq Scan	1	137,862.172 ms	100.0 %
dim_airline	1	0.324 ms	0.0 %
Seq Scan	1	0.324 ms	100.0 %
dim_date	1	1.015 ms	0.0 %
Seq Scan	1	1.015 ms	100.0 %
dim_planning_state	1	115.948 ms	0.0 %
Seq Scan	1	115.948 ms	100.0 %
dim_station	1	0.019 ms	0.0 %
Seq Scan	1	0.019 ms	100.0 %
dim_task_type	1	3,478.440 ms	0.9 %
Seq Scan	1	3,478.440 ms	100.0 %
fact_task	1	1,170.529 ms	0.3 %
Index Scan	1	1,170.529 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: haRy

Color mode:

Visible columns:

Per node type stats

Per table stats