#	exclusive	inclusive	rows x	node
1.	0.000	0.000	↓ 0.0	→ Hash Right Join (cost=205,705,620,978.23..212,229,868,287.67 rows=68,223,175,968 width=69) (actual rows= loops=) Hash Cond: (nat."Gitter_ID_100m" = (g.id)::text)
2.				CTE inh
3.	0.000	0.000	↓ 0.0	→ Seq Scan on sociodemo_grid100m_inh (cost=0.00..710,444.10 rows=3,254,046 width=21) (actual rows= loops=) Filter: (einwohner <> '-1'::integer)
4.				CTE hh
5.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=536,680.93..539,177.00 rows=249,607 width=25) (actual rows= loops=) Group Key: sociodemo_grid100m_hh."Gitter_ID_100m
6.	0.000	0.000	↓ 0.0	→ Seq Scan on sociodemo_grid100m_hh (cost=0.00..523,572.35 rows=2,621,717 width=21) (actual rows= loops=) Filter: (merkmal = 'INSGESAMT'::text)
7.				CTE nat
8.	0.000	0.000	↓ 0.0	→ Nested Loop Left Join (cost=4,371,113.47..205,699,214,524.11 rows=68,223,175,968 width=48) (actual rows= loops=) Join Filter: (inh_1."Gitter_ID_100m" = sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m")
9.	0.000	0.000	↓ 0.0	→ Merge Left Join (cost=2,386,322.76..4,351,825.85 rows=129,934,057 width=61) (actual rows= loops=) Merge Cond: (inh_1."Gitter_ID_100m" = sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m")
10.	0.000	0.000	↓ 0.0	→ Sort (cost=417,067.87..425,202.99 rows=3,254,046 width=36) (actual rows= loops=) Sort Key: inh_1."Gitter_ID_100m
11.	0.000	0.000	↓ 0.0	→ CTE Scan on inh inh_1 (cost=0.00..65,080.92 rows=3,254,046 width=36) (actual rows= loops=)
12.	0.000	0.000	↓ 0.0	→ Materialize (cost=1,969,254.89..1,969,496.86 rows=7,986 width=25) (actual rows= loops=)
13.	0.000	0.000	↓ 0.0	→ GroupAggregate (cost=1,969,254.89..1,969,397.03 rows=7,986 width=25) (actual rows= loops=) Group Key: sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m
14.	0.000	0.000	↓ 0.0	→ Sort (cost=1,969,254.89..1,969,275.65 rows=8,305 width=21) (actual rows= loops=) Sort Key: sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m
15.	0.000	0.000	↓ 0.0	→ Seq Scan on sociodemo_grid100m_inh_age_natlty_marstat (cost=0.00..1,968,714.24 rows=8,305 width=21) (actual rows= loops=) Filter: ((merkmal = 'STAATSANGE_KURZ'::text) AND (auspraegung_text = 'Ausland'::text))
16.	0.000	0.000	↓ 0.0	→ Materialize (cost=1,984,790.71..1,987,416.01 rows=105,012 width=8) (actual rows= loops=)
17.	0.000	0.000	↓ 0.0	→ Subquery Scan on non_european (cost=1,984,790.71..1,986,890.95 rows=105,012 width=8) (actual rows= loops=)
18.	0.000	0.000	↓ 0.0	→ HashAggregate (cost=1,984,790.71..1,985,840.83 rows=105,012 width=25) (actual rows= loops=) Group Key: sociodemo_grid100m_inh_age_natlty_marstat_1."Gitter_ID_100m
19.	0.000	0.000	↓ 0.0	→ Seq Scan on sociodemo_grid100m_inh_age_natlty_marstat sociodemo_grid100m_inh_age_natlty_marstat_1 (cost=0.00..1,968,714.24 rows=3,215,294 width=21) (actual rows= loops=) Filter: ((auspraegung_text <> ALL ('{Deutschland,EU27-Land}'::text[])) AND (merkmal = 'STAATSANGE_GRP'::text))
20.	0.000	0.000	↓ 0.0	→ CTE Scan on nat (cost=0.00..1,364,463,519.36 rows=68,223,175,968 width=40) (actual rows= loops=)
21.	0.000	0.000	↓ 0.0	→ Hash (cost=4,278,167.86..4,278,167.86 rows=37,804,732 width=61) (actual rows= loops=)
22.	0.000	0.000	↓ 0.0	→ Hash Right Join (cost=3,885,110.30..4,278,167.86 rows=37,804,732 width=61) (actual rows= loops=) Hash Cond: (hh."Gitter_ID_100m" = (g.id)::text)
23.	0.000	0.000	↓ 0.0	→ CTE Scan on hh (cost=0.00..4,992.14 rows=249,607 width=40) (actual rows= loops=)
24.	0.000	0.000	↓ 0.0	→ Hash (cost=3,043,364.15..3,043,364.15 rows=37,804,732 width=53) (actual rows= loops=)
25.	0.000	0.000	↓ 0.0	→ Hash Right Join (cost=2,363,007.47..3,043,364.15 rows=37,804,732 width=53) (actual rows= loops=) Hash Cond: (inh."Gitter_ID_100m" = (g.id)::text)
26.	0.000	0.000	↓ 0.0	→ CTE Scan on inh (cost=0.00..65,080.92 rows=3,254,046 width=36) (actual rows= loops=)
27.	0.000	0.000	↓ 0.0	→ Hash (cost=1,521,261.32..1,521,261.32 rows=37,804,732 width=49) (actual rows= loops=)
28.	0.000	0.000	↓ 0.0	→ Seq Scan on zones_grid_100m g (cost=0.00..1,521,261.32 rows=37,804,732 width=49) (actual rows= loops=)

Hash Right Join  (cost=205705620978.23..212229868287.67 rows=68223175968 width=69)
  Hash Cond: (nat."Gitter_ID_100m" = (g.id)::text)
  CTE inh
    ->  Seq Scan on sociodemo_grid100m_inh  (cost=0.00..710444.10 rows=3254046 width=21)
          Filter: (einwohner <> '-1'::integer)
  CTE hh
    ->  HashAggregate  (cost=536680.93..539177.00 rows=249607 width=25)
          Group Key: sociodemo_grid100m_hh."Gitter_ID_100m"
          ->  Seq Scan on sociodemo_grid100m_hh  (cost=0.00..523572.35 rows=2621717 width=21)
                Filter: (merkmal = 'INSGESAMT'::text)
  CTE nat
    ->  Nested Loop Left Join  (cost=4371113.47..205699214524.11 rows=68223175968 width=48)
          Join Filter: (inh_1."Gitter_ID_100m" = sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m")
          ->  Merge Left Join  (cost=2386322.76..4351825.85 rows=129934057 width=61)
                Merge Cond: (inh_1."Gitter_ID_100m" = sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m")
                ->  Sort  (cost=417067.87..425202.99 rows=3254046 width=36)
                      Sort Key: inh_1."Gitter_ID_100m"
                      ->  CTE Scan on inh inh_1  (cost=0.00..65080.92 rows=3254046 width=36)
                ->  Materialize  (cost=1969254.89..1969496.86 rows=7986 width=25)
                      ->  GroupAggregate  (cost=1969254.89..1969397.03 rows=7986 width=25)
                            Group Key: sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m"
                            ->  Sort  (cost=1969254.89..1969275.65 rows=8305 width=21)
                                  Sort Key: sociodemo_grid100m_inh_age_natlty_marstat."Gitter_ID_100m"
                                  ->  Seq Scan on sociodemo_grid100m_inh_age_natlty_marstat  (cost=0.00..1968714.24 rows=8305 width=21)
                                        Filter: ((merkmal = 'STAATSANGE_KURZ'::text) AND (auspraegung_text = 'Ausland'::text))
          ->  Materialize  (cost=1984790.71..1987416.01 rows=105012 width=8)
                ->  Subquery Scan on non_european  (cost=1984790.71..1986890.95 rows=105012 width=8)
                      ->  HashAggregate  (cost=1984790.71..1985840.83 rows=105012 width=25)
                            Group Key: sociodemo_grid100m_inh_age_natlty_marstat_1."Gitter_ID_100m"
                            ->  Seq Scan on sociodemo_grid100m_inh_age_natlty_marstat sociodemo_grid100m_inh_age_natlty_marstat_1  (cost=0.00..1968714.24 rows=3215294 width=21)
                                  Filter: ((auspraegung_text <> ALL ('{Deutschland,EU27-Land}'::text[])) AND (merkmal = 'STAATSANGE_GRP'::text))
  ->  CTE Scan on nat  (cost=0.00..1364463519.36 rows=68223175968 width=40)
  ->  Hash  (cost=4278167.86..4278167.86 rows=37804732 width=61)
        ->  Hash Right Join  (cost=3885110.30..4278167.86 rows=37804732 width=61)
              Hash Cond: (hh."Gitter_ID_100m" = (g.id)::text)
              ->  CTE Scan on hh  (cost=0.00..4992.14 rows=249607 width=40)
              ->  Hash  (cost=3043364.15..3043364.15 rows=37804732 width=53)
                    ->  Hash Right Join  (cost=2363007.47..3043364.15 rows=37804732 width=53)
                          Hash Cond: (inh."Gitter_ID_100m" = (g.id)::text)
                          ->  CTE Scan on inh  (cost=0.00..65080.92 rows=3254046 width=36)
                          ->  Hash  (cost=1521261.32..1521261.32 rows=37804732 width=49)
                                ->  Seq Scan on zones_grid_100m g  (cost=0.00..1521261.32 rows=37804732 width=49)

Per node type stats

node type	count	sum of times	% of query
CTE Scan	4	0.000 ms	0.0 %
GroupAggregate	1	0.000 ms	0.0 %
Hash	3	0.000 ms	0.0 %
Hash Right Join	3	0.000 ms	0.0 %
HashAggregate	2	0.000 ms	0.0 %
Materialize	2	0.000 ms	0.0 %
Merge Left Join	1	0.000 ms	0.0 %
Nested Loop Left Join	1	0.000 ms	0.0 %
Seq Scan	5	0.000 ms	0.0 %
Sort	2	0.000 ms	0.0 %
Subquery Scan	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
sociodemo_grid100m_hh	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
sociodemo_grid100m_inh	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
sociodemo_grid100m_inh_age_natlty_marstat	2	0.000 ms	0.0 %
Seq Scan	2	0.000 ms	0.0 %
zones_grid_100m	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Wb23

Color mode:

Visible columns:

Per node type stats

Per table stats