explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F6ow

Settings
# exclusive inclusive rows x rows loops node
1. 0.704 1,994.922 ↑ 7.4 1,139 1

Finalize GroupAggregate (cost=271,555.55..271,807.91 rows=8,412 width=17) (actual time=1,994.064..1,994.922 rows=1,139 loops=1)

  • Group Key: x.administrationid, dst.orgcode
2. 4.836 1,994.218 ↑ 8.0 2,115 1

Sort (cost=271,555.55..271,597.61 rows=16,824 width=17) (actual time=1,994.058..1,994.218 rows=2,115 loops=1)

  • Sort Key: x.administrationid, dst.orgcode
  • Sort Method: quicksort Memory: 202kB
3. 20.790 1,989.382 ↑ 8.0 2,115 1

Gather (cost=268,608.14..270,374.66 rows=16,824 width=17) (actual time=1,988.875..1,989.382 rows=2,115 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 278.545 1,968.592 ↑ 11.9 705 3

Partial HashAggregate (cost=267,608.14..267,692.26 rows=8,412 width=17) (actual time=1,968.410..1,968.592 rows=705 loops=3)

  • Group Key: x.administrationid, dst.orgcode
5. 632.127 1,690.047 ↓ 1.2 1,564,833 3

Hash Join (cost=25,960.25..257,973.50 rows=1,284,618 width=17) (actual time=423.916..1,690.047 rows=1,564,833 loops=3)

  • Hash Cond: ((x.administrationid = stu.administrationid) AND (x.participantid = stu.participantid))
6. 634.538 634.538 ↑ 1.3 1,564,833 3

Parallel Seq Scan on dim_assessmentparticipant x (cost=0.00..179,310.99 rows=1,957,940 width=20) (actual time=0.006..634.538 rows=1,564,833 loops=3)

  • Filter: (((is_reportable)::text = 'YES'::text) AND ((reportphase)::text = 'IR'::text))
  • Rows Removed by Filter: 48743
7. 91.982 423.382 ↓ 1.3 433,890 3

Hash (cost=18,695.20..18,695.20 rows=332,670 width=29) (actual time=423.382..423.382 rows=433,890 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 4058kB
8. 78.476 331.400 ↓ 1.3 433,890 3

Hash Join (cost=476.12..18,695.20 rows=332,670 width=29) (actual time=5.057..331.400 rows=433,890 loops=3)

  • Hash Cond: (stu.administrationid = ts.administrationid)
9. 80.691 252.904 ↓ 1.3 433,890 3

Hash Join (cost=475.05..16,587.23 rows=332,670 width=25) (actual time=5.027..252.904 rows=433,890 loops=3)

  • Hash Cond: ((stu.administrationid = dst.administrationid) AND (sch.parent_organizationid = dst.organizationid))
10. 127.987 169.726 ↓ 1.1 433,890 3

Hash Join (cost=237.53..14,354.28 rows=379,924 width=24) (actual time=2.501..169.726 rows=433,890 loops=3)

  • Hash Cond: ((stu.administrationid = sch.administrationid) AND (stu.organizationid = sch.organizationid))
11. 39.294 39.294 ↑ 1.0 433,890 3

Seq Scan on dim_participant stu (cost=0.00..11,837.90 rows=433,890 width=20) (actual time=0.009..39.294 rows=433,890 loops=3)

12. 1.141 2.445 ↑ 1.0 4,981 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 337kB
13. 1.304 1.304 ↑ 1.0 4,981 3

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

14. 1.134 2.487 ↑ 1.0 4,981 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 321kB
15. 1.353 1.353 ↑ 1.0 4,981 3

Seq Scan on dim_organizations dst (cost=0.00..162.81 rows=4,981 width=17) (actual time=0.018..1.353 rows=4,981 loops=3)

16. 0.004 0.020 ↑ 1.0 3 3

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.020..0.020 rows=3 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.016 0.016 ↑ 1.0 3 3

Seq Scan on ts_admin_config ts (cost=0.00..1.03 rows=3 width=4) (actual time=0.015..0.016 rows=3 loops=3)

Planning time : 2.356 ms
Execution time : 1,995.900 ms