explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tBgK : Nova query

Settings
# exclusive inclusive rows x rows loops node
1. 1,027.534 3,389.465 ↓ 0.0 470,524 1

WindowAgg (cost=0..0 rows=0 width=0) (actual time=2,271.746..3,389.465 rows=470,524 loops=1)

2. 311.484 2,361.931 ↓ 0.0 470,524 1

Sort (cost=0..0 rows=0 width=0) (actual time=2,271.715..2,361.931 rows=470,524 loops=1)

  • Sort Key: p.id, a.created_time
  • Sort Method: external merge Disk: 41008kB
3. 65.579 2,050.447 ↓ 0.0 470,524 1

Hash Left Join (cost=0..0 rows=0 width=0) (actual time=1,070.975..2,050.447 rows=470,524 loops=1)

  • Hash Cond: (a.id = _pe.attendance_id)
4. 106.495 1,983.623 ↓ 0.0 470,524 1

Hash Left Join (cost=0..0 rows=0 width=0) (actual time=1,069.726..1,983.623 rows=470,524 loops=1)

  • Hash Cond: (a.id = _pm.added_attendance_id)
5. 218.949 1,277.482 ↓ 0.0 470,524 1

Hash Left Join (cost=0..0 rows=0 width=0) (actual time=470.062..1,277.482 rows=470,524 loops=1)

  • Hash Cond: (a.id = _pbp.attendance_id)
6. 0.000 612.452 ↓ 0.0 470,524 1

Gather (cost=0..0 rows=0 width=0) (actual time=23.910..612.452 rows=470,524 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 57.769 668.652 ↓ 0.0 156,841 3

Parallel Hash Left Join (cost=0..0 rows=0 width=0) (actual time=21.297..668.652 rows=156,841 loops=3)

  • Hash Cond: (a.id = procedure_blood_glucoses.attendance_id)
8. 544.257 589.993 ↓ 0.0 156,841 3

Nested Loop (cost=0..0 rows=0 width=0) (actual time=0.110..589.993 rows=156,841 loops=3)

9. 15.189 45.730 ↓ 0.0 89,959 3

Hash Join (cost=0..0 rows=0 width=0) (actual time=0.076..45.730 rows=89,959 loops=3)

  • Hash Cond: (p.tenancy_id = t.id)
10. 30.499 30.499 ↓ 0.0 90,041 3

Parallel Seq Scan on patients p (cost=0..0 rows=0 width=0) (actual time=0.020..30.499 rows=90,041 loops=3)

  • Filter: (NOT is_user_test)
  • Rows Removed by Filter: 103
11. 0.005 0.042 ↓ 0.0 23 3

Hash (cost=0..0 rows=0 width=0) (actual time=0.042..0.042 rows=23 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.037 0.037 ↓ 0.0 23 3

Seq Scan on tenancies t (cost=0..0 rows=0 width=0) (actual time=0.012..0.037 rows=23 loops=3)

  • Filter: (subscription_id ~~ 'a-%'::text)
  • Rows Removed by Filter: 67
13. 0.006 0.006 ↓ 0.0 2 269,876

Index Scan using index_attendances_patient_id on attendances a (cost=0..0 rows=0 width=0) (actual time=0.004..0.006 rows=2 loops=269,876)

  • Index Cond: (patient_id = p.id)
14. 7.591 20.890 ↓ 0.0 34,684 3

Parallel Hash (cost=0..0 rows=0 width=0) (actual time=20.890..20.890 rows=34,684 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 8992kB
15. 13.299 13.299 ↓ 0.0 34,684 3

Parallel Seq Scan on procedure_blood_glucoses (cost=0..0 rows=0 width=0) (actual time=0.010..13.299 rows=34,684 loops=3)

16. 30.796 446.081 ↓ 0.0 195,614 1

Hash (cost=0..0 rows=0 width=0) (actual time=446.081..446.081 rows=195,614 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 1767kB
17. 13.592 415.285 ↓ 0.0 195,614 1

Subquery Scan on _pbp (cost=0..0 rows=0 width=0) (actual time=61.684..415.285 rows=195,614 loops=1)

18. 138.594 401.693 ↓ 0.0 195,614 1

Finalize GroupAggregate (cost=0..0 rows=0 width=0) (actual time=61.683..401.693 rows=195,614 loops=1)

  • Group Key: procedure_blood_pressures.attendance_id
19. 0.000 263.099 ↓ 0.0 195,614 1

Gather Merge (cost=0..0 rows=0 width=0) (actual time=61.674..263.099 rows=195,614 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 472.629 694.302 ↓ 0.0 65,205 3

Partial GroupAggregate (cost=0..0 rows=0 width=0) (actual time=58.544..231.434 rows=65,205 loops=3)

  • Group Key: procedure_blood_pressures.attendance_id
21. 156.279 221.673 ↓ 0.0 65,205 3

Sort (cost=0..0 rows=0 width=0) (actual time=58.524..73.891 rows=65,205 loops=3)

  • Sort Key: procedure_blood_pressures.attendance_id
  • Sort Method: external merge Disk: 21896kB
  • Worker 0: Sort Method: external merge Disk: 20848kB
  • Worker 1: Sort Method: external merge Disk: 20064kB
22. 65.394 65.394 ↓ 0.0 65,205 3

Parallel Seq Scan on procedure_blood_pressures (cost=0..0 rows=0 width=0) (actual time=0.010..21.798 rows=65,205 loops=3)

23. 6.340 599.646 ↓ 0.0 28,337 1

Hash (cost=0..0 rows=0 width=0) (actual time=599.646..599.646 rows=28,337 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2830kB
24. 2.159 593.306 ↓ 0.0 28,337 1

Subquery Scan on _pm (cost=0..0 rows=0 width=0) (actual time=105.967..593.306 rows=28,337 loops=1)

25. 460.336 591.147 ↓ 0.0 28,337 1

GroupAggregate (cost=0..0 rows=0 width=0) (actual time=105.966..591.147 rows=28,337 loops=1)

  • Group Key: patient_medicines.added_attendance_id
26. 96.929 130.811 ↓ 0.0 57,903 1

Sort (cost=0..0 rows=0 width=0) (actual time=105.913..130.811 rows=57,903 loops=1)

  • Sort Key: patient_medicines.added_attendance_id
  • Sort Method: external merge Disk: 59360kB
27. 33.882 33.882 ↓ 0.0 57,903 1

Seq Scan on patient_medicines (cost=0..0 rows=0 width=0) (actual time=0.017..33.882 rows=57,903 loops=1)

  • Filter: (type = ANY ('{views.attendance.medicine_profile.type.ACTIVE_PRINCIPLE,views.attendance.medicine_profile.type.MEDICINE}'::text[]))
  • Rows Removed by Filter: 18128
28. 0.069 1.245 ↓ 0.0 584 1

Hash (cost=0..0 rows=0 width=0) (actual time=1.245..1.245 rows=584 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
29. 0.038 1.176 ↓ 0.0 584 1

Subquery Scan on _pe (cost=0..0 rows=0 width=0) (actual time=0.500..1.176 rows=584 loops=1)

30. 0.597 1.138 ↓ 0.0 584 1

GroupAggregate (cost=0..0 rows=0 width=0) (actual time=0.499..1.138 rows=584 loops=1)

  • Group Key: procedure_exams.attendance_id
31. 0.292 0.541 ↓ 0.0 1,320 1

Sort (cost=0..0 rows=0 width=0) (actual time=0.494..0.541 rows=1,320 loops=1)

  • Sort Key: procedure_exams.attendance_id
  • Sort Method: quicksort Memory: 167kB
32. 0.249 0.249 ↓ 0.0 1,320 1

Seq Scan on procedure_exams (cost=0..0 rows=0 width=0) (actual time=0.013..0.249 rows=1,320 loops=1)

Planning time : 1.317 ms
Execution time : 3,427.940 ms