explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SI5y

Settings
# exclusive inclusive rows x rows loops node
1. 1,545.953 12,812.240 ↑ 1.0 410,901 1

GroupAggregate (cost=80,835,902.54..80,893,428.26 rows=410,901 width=24) (actual time=11,212.141..12,812.240 rows=410,901 loops=1)

  • Group Key: stat.id, (array_length((array_agg(DISTINCT _old_person.f_link2reestr) FILTER (WHERE (_old_person.f_link2reestr IS NOT NULL))), 1))
2. 378.761 11,266.287 ↑ 8.2 503,094 1

Sort (cost=80,835,902.54..80,846,174.98 rows=4,108,976 width=36) (actual time=11,212.095..11,266.287 rows=503,094 loops=1)

  • Sort Key: stat.id, (array_length((array_agg(DISTINCT _old_person.f_link2reestr) FILTER (WHERE (_old_person.f_link2reestr IS NOT NULL))), 1))
  • Sort Method: quicksort Memory: 51403kB
3. 315.725 10,887.526 ↑ 8.2 503,094 1

Hash Join (cost=191.21..80,272,167.39 rows=4,108,976 width=36) (actual time=0.098..10,887.526 rows=503,094 loops=1)

  • Hash Cond: (pn.f_data_source = dr.id)
4. 372.430 10,571.789 ↑ 8.2 503,094 1

Nested Loop (cost=190.09..80,205,395.42 rows=4,108,976 width=68) (actual time=0.079..10,571.789 rows=503,094 loops=1)

5. 251.745 8,966.656 ↑ 1.0 410,901 1

Nested Loop (cost=189.67..77,959,912.39 rows=410,901 width=66) (actual time=0.072..8,966.656 rows=410,901 loops=1)

6. 85.990 85.990 ↑ 1.0 410,901 1

Seq Scan on _drop_me_metaperson stat (cost=0.00..12,003.01 rows=410,901 width=34) (actual time=0.003..85.990 rows=410,901 loops=1)

7. 1,232.703 8,628.921 ↑ 1.0 1 410,901

Aggregate (cost=189.67..189.68 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=410,901)

8. 732.634 7,396.218 ↑ 5.0 1 410,901

Nested Loop (cost=0.85..189.65 rows=5 width=8) (actual time=0.013..0.018 rows=1 loops=410,901)

9. 1,643.604 1,643.604 ↑ 10.0 1 410,901

Index Scan using person_pkey on person _pn (cost=0.42..67.02 rows=10 width=15) (actual time=0.003..0.004 rows=1 loops=410,901)

  • Index Cond: (id = ANY (stat.ids))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
10. 5,019.980 5,019.980 ↑ 1.0 1 501,998

Index Scan using person_f_external_id_idx on person _old_person (cost=0.42..12.25 rows=1 width=23) (actual time=0.009..0.010 rows=1 loops=501,998)

  • Index Cond: ((f_external_id)::text = (_pn.f_external_id)::text)
  • Filter: ((NOT deleted) AND (_pn.f_data_source = f_data_source))
  • Rows Removed by Filter: 1
11. 1,232.703 1,232.703 ↑ 10.0 1 410,901

Index Scan using person_pkey on person pn (cost=0.42..5.36 rows=10 width=32) (actual time=0.002..0.003 rows=1 loops=410,901)

  • Index Cond: (id = ANY (stat.ids))
12. 0.002 0.012 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.012..0.012 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.010 0.010 ↑ 1.0 5 1

Seq Scan on dataresource dr (cost=0.00..1.05 rows=5 width=8) (actual time=0.008..0.010 rows=5 loops=1)