explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2TgX

Settings
# exclusive inclusive rows x rows loops node
1. 1,293.021 39,337.202 ↑ 515.9 108 1

Unique (cost=7,562.27..8,955.20 rows=55,717 width=51) (actual time=35,900.918..39,337.202 rows=108 loops=1)

2. 30,000.407 38,044.181 ↓ 108.0 6,017,327 1

Sort (cost=7,562.27..7,701.57 rows=55,717 width=51) (actual time=35,900.917..38,044.181 rows=6,017,327 loops=1)

  • Sort Key: loaded_data.id, loaded_data.f_external_id, loaded_data.deleted, loaded_data.declaration_id, loaded_data.organization_id, stored_data.id, declaration.id, declaration.f_child, _organization.id
  • Sort Method: external merge Disk: 388240kB
3. 1,217.963 8,043.774 ↓ 108.0 6,017,327 1

Nested Loop (cost=5.46..3,170.15 rows=55,717 width=51) (actual time=1.813..8,043.774 rows=6,017,327 loops=1)

  • Join Filter: (((_organization.f_external_id)::text IS DISTINCT FROM (organization.f_external_id)::text) OR ((_person.f_external_id)::text IS DISTINCT FROM (person.f_external_id)::text) OR (loaded_data.deleted IS DISTINCT FROM stored_data.deleted))
  • Rows Removed by Join Filter: 109
4. 0.491 6,110.743 ↓ 108.0 108 1

Nested Loop (cost=5.17..952.06 rows=1 width=66) (actual time=1.405..6,110.743 rows=108 loops=1)

  • Join Filter: (stored_data.f_child = person.id)
5. 0.447 5,558.372 ↓ 108.0 108 1

Nested Loop (cost=4.76..951.02 rows=1 width=68) (actual time=1.088..5,558.372 rows=108 loops=1)

6. 0.263 2,990.225 ↓ 108.0 108 1

Nested Loop (cost=4.48..949.64 rows=1 width=56) (actual time=0.859..2,990.225 rows=108 loops=1)

7. 0.514 897.786 ↓ 108.0 108 1

Nested Loop (cost=4.20..949.17 rows=1 width=60) (actual time=0.628..897.786 rows=108 loops=1)

  • Join Filter: ((loaded_data.f_external_id)::text = (stored_data.f_external_id)::text)
  • Rows Removed by Join Filter: 107
8. 4.465 895.419 ↓ 2.3 109 1

Hash Join (cost=3.79..909.47 rows=47 width=39) (actual time=0.620..895.419 rows=109 loops=1)

  • Hash Cond: ((declaration.f_external_id)::text = (loaded_data.declaration_id)::text)
9. 890.923 890.923 ↓ 1.0 9,539 1

Index Scan using declaration_f_data_source_idx on declaration (cost=0.29..834.82 rows=9,425 width=17) (actual time=0.577..890.923 rows=9,539 loops=1)

  • Index Cond: (f_data_source = 7)
  • Filter: f_uptodate
10. 0.019 0.031 ↑ 1.0 111 1

Hash (cost=2.11..2.11 rows=111 width=27) (actual time=0.031..0.031 rows=111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
11. 0.012 0.012 ↑ 1.0 111 1

Seq Scan on declarations_organizations loaded_data (cost=0.00..2.11 rows=111 width=27) (actual time=0.006..0.012 rows=111 loops=1)

12. 1.853 1.853 ↓ 2.0 2 109

Index Scan using f_child_idx on declaration2child stored_data (cost=0.42..0.83 rows=1 width=27) (actual time=0.012..0.017 rows=2 loops=109)

  • Index Cond: (f_child = declaration.f_child)
  • Filter: (f_uptodate AND (f_data_source = 7))
13. 2,092.176 2,092.176 ↑ 1.0 1 108

Index Scan using organization_pkey on organization (cost=0.28..0.46 rows=1 width=12) (actual time=19.371..19.372 rows=1 loops=108)

  • Index Cond: (id = stored_data.f_org)
  • Filter: (f_uptodate AND (f_data_source = 7))
14. 2,567.700 2,567.700 ↑ 1.0 1 108

Index Scan using organization_f_external_id_idx on organization _organization (cost=0.28..1.37 rows=1 width=12) (actual time=2.143..23.775 rows=1 loops=108)

  • Index Cond: ((f_external_id)::text = (loaded_data.organization_id)::text)
  • Filter: (f_uptodate AND (f_data_source = 7))
  • Rows Removed by Filter: 1
15. 551.880 551.880 ↑ 1.0 1 108

Index Scan using person_pkey on person (cost=0.41..1.03 rows=1 width=14) (actual time=5.109..5.110 rows=1 loops=108)

  • Index Cond: (id = declaration.f_child)
16. 715.068 715.068 ↑ 1.0 55,717 108

Index Only Scan using person_f_external_id_idx on person _person (cost=0.29..1,243.05 rows=55,717 width=6) (actual time=0.015..6.621 rows=55,717 loops=108)

  • Heap Fetches: 0
Planning time : 32.053 ms
Execution time : 39,532.319 ms