explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Wb23

Settings
# exclusive inclusive rows x rows loops 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=)