explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ht2r2 : Optimization for: Optimization for: Optimization for: Optimization for: plan #sftf; plan #2hZx; plan #je1I; plan #4N5g

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 578.109 18,755.968 ↓ 6.5 1,517,815 1

Finalize GroupAggregate (cost=79,665.17..108,631.18 rows=233,793 width=27) (actual time=13,055.289..18,755.968 rows=1,517,815 loops=1)

  • Group Key: pa.auditlog_id, pa.clinician_action_id, pa.hospital_id
2. 4,216.523 18,177.859 ↓ 7.8 1,520,063 1

Gather Merge (cost=79,665.17..104,344.97 rows=194,828 width=27) (actual time=13,055.276..18,177.859 rows=1,520,063 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 896.602 13,961.336 ↓ 5.2 506,688 3 / 3

Partial GroupAggregate (cost=78,665.15..80,856.96 rows=97,414 width=27) (actual time=12,523.731..13,961.336 rows=506,688 loops=3)

  • Group Key: pa.auditlog_id, pa.clinician_action_id, pa.hospital_id
4. 8,380.195 13,064.734 ↓ 18.9 1,841,463 3 / 3

Sort (cost=78,665.15..78,908.68 rows=97,414 width=27) (actual time=12,523.713..13,064.734 rows=1,841,463 loops=3)

  • Sort Key: pa.auditlog_id, pa.clinician_action_id, pa.hospital_id
  • Sort Method: external merge Disk: 55,320kB
5. 3,068.949 4,684.539 ↓ 18.9 1,841,463 3 / 3

Hash Join (cost=295.65..68,262.50 rows=97,414 width=27) (actual time=35.031..4,684.539 rows=1,841,463 loops=3)

  • Hash Cond: ((pa.hospital_id)::text = au.username)
  • Join Filter: ((pa."time" >= sub.creation_time) AND (pa."time" <= sub.renewal_time))
  • Rows Removed by Join Filter: 1,584,579
6. 1,581.006 1,581.006 ↑ 1.3 580,439 3 / 3

Parallel Seq Scan on bi_platform_actions pa (cost=0.00..52,093.49 rows=725,549 width=31) (actual time=0.342..1,581.006 rows=580,439 loops=3)

7. 1.255 34.584 ↑ 1.1 3,571 3 / 3

Hash (cost=247.04..247.04 rows=3,889 width=29) (actual time=34.584..34.584 rows=3,571 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 257kB
8. 1.246 33.329 ↑ 1.1 3,571 3 / 3

Hash Join (cost=112.31..247.04 rows=3,889 width=29) (actual time=29.573..33.329 rows=3,571 loops=3)

  • Hash Cond: (sub.plans_id = plans.id)
9. 1.933 27.691 ↑ 1.1 3,571 3 / 3

Hash Join (cost=107.41..230.85 rows=3,889 width=29) (actual time=25.144..27.691 rows=3,571 loops=3)

  • Hash Cond: (sub.users_id = au.id)
10. 9.296 9.296 ↑ 1.0 5,579 3 / 3

Seq Scan on bi_admin_subscriptions sub (cost=0.00..108.79 rows=5,579 width=24) (actual time=8.636..9.296 rows=5,579 loops=3)

11. 0.954 16.462 ↑ 1.0 3,218 3 / 3

Hash (cost=67.18..67.18 rows=3,218 width=13) (actual time=16.462..16.462 rows=3,218 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 179kB
12. 15.508 15.508 ↑ 1.0 3,218 3 / 3

Seq Scan on bi_admin_users au (cost=0.00..67.18 rows=3,218 width=13) (actual time=14.620..15.508 rows=3,218 loops=3)

13. 0.025 4.392 ↑ 1.0 40 3 / 3

Hash (cost=4.40..4.40 rows=40 width=8) (actual time=4.391..4.392 rows=40 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 4.367 4.367 ↑ 1.0 40 3 / 3

Seq Scan on bi_admin_plans plans (cost=0.00..4.40 rows=40 width=8) (actual time=4.354..4.367 rows=40 loops=3)

Planning time : 379.943 ms
Execution time : 19,441.166 ms