explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4zsw

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 2,241.258 ↓ 0.0 0 1

HashAggregate (cost=306,872.69..306,956.81 rows=8,412 width=9) (actual time=2,241.258..2,241.258 rows=0 loops=1)

  • Group Key: x.administrationid, dst.orgcode
2. 0.041 2,241.201 ↓ 0.0 0 1

Hash Join (cost=21,365.37..301,734.22 rows=1,027,694 width=9) (actual time=2,241.201..2,241.201 rows=0 loops=1)

  • Hash Cond: ((x.administrationid = dst.administrationid) AND (sch.parent_organizationid = dst.organizationid))
3. 0.057 2,239.389 ↓ 0.0 0 1

Hash Join (cost=21,127.84..295,344.27 rows=1,171,410 width=24) (actual time=2,239.389..2,239.389 rows=0 loops=1)

  • Hash Cond: ((x.administrationid = sch.administrationid) AND (stu.organizationid = sch.organizationid))
4. 0.000 2,237.691 ↓ 0.0 0 1

Hash Join (cost=20,890.32..288,093.94 rows=1,335,224 width=20) (actual time=2,237.691..2,237.691 rows=0 loops=1)

  • Hash Cond: ((x.administrationid = stu.administrationid) AND (x.participantid = stu.participantid))
5. 662.745 2,237.691 ↓ 0.0 0 1

Hash Join (cost=1.07..241,140.30 rows=1,566,352 width=16) (actual time=2,237.691..2,237.691 rows=0 loops=1)

  • Hash Cond: (x.administrationid = ts.administrationid)
  • Join Filter: (x.updatedate >= ts.last_batch_load_time)
  • Rows Removed by Join Filter: 4694499
6. 1,574.940 1,574.940 ↑ 1.0 4,694,499 1

Seq Scan on dim_assessmentparticipant x (cost=0.00..221,690.37 rows=4,699,055 width=20) (actual time=0.003..1,574.940 rows=4,694,499 loops=1)

  • Filter: (((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text))
  • Rows Removed by Filter: 146230
7. 0.002 0.006 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=12) (actual time=0.006..0.006 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on ts_admin_config ts (cost=0.00..1.03 rows=3 width=12) (actual time=0.004..0.004 rows=3 loops=1)

9. 0.000 0.000 ↓ 0.0 0

Hash (cost=11,837.90..11,837.90 rows=433,890 width=20) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on dim_participant stu (cost=0.00..11,837.90 rows=433,890 width=20) (never executed)

11. 0.768 1.641 ↑ 1.0 4,981 1

Hash (cost=162.81..162.81 rows=4,981 width=20) (actual time=1.641..1.641 rows=4,981 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 337kB
12. 0.873 0.873 ↑ 1.0 4,981 1

Seq Scan on dim_organizations sch (cost=0.00..162.81 rows=4,981 width=20) (actual time=0.002..0.873 rows=4,981 loops=1)

13. 0.801 1.771 ↑ 1.0 4,981 1

Hash (cost=162.81..162.81 rows=4,981 width=17) (actual time=1.771..1.771 rows=4,981 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 321kB
14. 0.970 0.970 ↑ 1.0 4,981 1

Seq Scan on dim_organizations dst (cost=0.00..162.81 rows=4,981 width=17) (actual time=0.005..0.970 rows=4,981 loops=1)

Planning time : 2.659 ms
Execution time : 2,241.532 ms