explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xgO2 : Optimization for: Relatorio Geral; plan #2gNp

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,021.657 2,946.837 ↓ 0.0 470,524 1

WindowAgg (cost=0..0 rows=0 width=0) (actual time=1,836.376..2,946.837 rows=470,524 loops=1)

2. 315.260 1,925.180 ↓ 0.0 470,524 1

Sort (cost=0..0 rows=0 width=0) (actual time=1,836.351..1,925.180 rows=470,524 loops=1)

  • Sort Key: p.id, a.created_time
  • Sort Method: external merge Disk: 39952kB
3. 50.940 1,609.920 ↓ 0.0 470,524 1

Merge Left Join (cost=0..0 rows=0 width=0) (actual time=885.286..1,609.920 rows=470,524 loops=1)

  • Merge Cond: (a.id = _pp.attendance_id)
4. 92.965 1,422.783 ↓ 0.0 470,524 1

Merge Left Join (cost=0..0 rows=0 width=0) (actual time=823.144..1,422.783 rows=470,524 loops=1)

  • Merge Cond: (a.id = procedure_blood_pressures.attendance_id)
5. 0.000 896.579 ↓ 0.0 470,524 1

Gather Merge (cost=0..0 rows=0 width=0) (actual time=758.916..896.579 rows=470,524 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 89.463 2,452.656 ↓ 0.0 156,841 3

Merge Left Join (cost=0..0 rows=0 width=0) (actual time=755.053..817.552 rows=156,841 loops=3)

  • Merge Cond: (a.id = procedure_exams.attendance_id)
7. 317.478 2,353.887 ↓ 0.0 156,841 3

Sort (cost=0..0 rows=0 width=0) (actual time=754.496..784.629 rows=156,841 loops=3)

  • Sort Key: a.id
  • Sort Method: external merge Disk: 13280kB
  • Worker 0: Sort Method: external merge Disk: 13224kB
  • Worker 1: Sort Method: external merge Disk: 13432kB
8. 172.371 2,036.409 ↓ 0.0 156,841 3

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

  • Hash Cond: (a.id = procedure_blood_glucoses.attendance_id)
9. 48.465 1,802.952 ↓ 0.0 156,841 3

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

10. 46.104 135.231 ↓ 0.0 89,959 3

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

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

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

  • Filter: (NOT is_user_test)
  • Rows Removed by Filter: 103
12. 0.015 0.105 ↓ 0.0 23 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.090 0.090 ↓ 0.0 23 3

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

  • Filter: (subscription_id ~~ 'a-%'::text)
  • Rows Removed by Filter: 67
14. 1,619.256 1,619.256 ↓ 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)
15. 23.433 61.086 ↓ 0.0 34,684 3

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

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

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

17. 7.131 9.306 ↓ 0.0 584 3

GroupAggregate (cost=0..0 rows=0 width=0) (actual time=0.553..3.102 rows=584 loops=3)

  • Group Key: procedure_exams.attendance_id
18. 1.410 2.175 ↓ 0.0 1,320 3

Sort (cost=0..0 rows=0 width=0) (actual time=0.518..0.725 rows=1,320 loops=3)

  • Sort Key: procedure_exams.attendance_id
  • Sort Method: quicksort Memory: 167kB
  • Worker 0: Sort Method: quicksort Memory: 167kB
  • Worker 1: Sort Method: quicksort Memory: 167kB
19. 0.765 0.765 ↓ 0.0 1,320 3

Seq Scan on procedure_exams (cost=0..0 rows=0 width=0) (actual time=0.018..0.255 rows=1,320 loops=3)

20. 142.968 433.239 ↓ 0.0 195,614 1

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

  • Group Key: procedure_blood_pressures.attendance_id
21. 0.000 290.271 ↓ 0.0 195,614 1

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

  • Workers Planned: 2
  • Workers Launched: 2
22. 454.617 685.386 ↓ 0.0 65,205 3

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

  • Group Key: procedure_blood_pressures.attendance_id
23. 164.691 230.769 ↓ 0.0 65,205 3

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

  • Sort Key: procedure_blood_pressures.attendance_id
  • Sort Method: external merge Disk: 24160kB
  • Worker 0: Sort Method: external merge Disk: 18688kB
  • Worker 1: Sort Method: external merge Disk: 19928kB
24. 66.078 66.078 ↓ 0.0 65,205 3

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

25. 71.371 136.197 ↓ 0.0 6,548 1

GroupAggregate (cost=0..0 rows=0 width=0) (actual time=62.139..136.197 rows=6,548 loops=1)

  • Group Key: _pp.attendance_id
26. 6.262 64.826 ↓ 0.0 7,961 1

Sort (cost=0..0 rows=0 width=0) (actual time=62.124..64.826 rows=7,961 loops=1)

  • Sort Key: _pp.attendance_id
  • Sort Method: external merge Disk: 6184kB
27. 7.886 58.564 ↓ 0.0 7,961 1

Gather (cost=0..0 rows=0 width=0) (actual time=46.550..58.564 rows=7,961 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
28. 12.520 50.678 ↓ 0.0 2,654 3

Parallel Hash Join (cost=0..0 rows=0 width=0) (actual time=43.326..50.678 rows=2,654 loops=3)

  • Hash Cond: (_pm.patient_prescription_id = _pp.id)
29. 13.197 13.197 ↓ 0.0 19,301 3

Parallel Seq Scan on patient_medicines _pm (cost=0..0 rows=0 width=0) (actual time=0.020..13.197 rows=19,301 loops=3)

  • Filter: (type = ANY ('{views.attendance.medicine_profile.type.ACTIVE_PRINCIPLE,views.attendance.medicine_profile.type.MEDICINE}'::text[]))
  • Rows Removed by Filter: 6043
30. 12.491 24.961 ↓ 0.0 89,430 3

Parallel Hash (cost=0..0 rows=0 width=0) (actual time=24.961..24.961 rows=89,430 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2656kB
31. 12.470 12.470 ↓ 0.0 89,430 3

Parallel Seq Scan on patient_prescriptions _pp (cost=0..0 rows=0 width=0) (actual time=0.012..12.470 rows=89,430 loops=3)

Planning time : 1.603 ms
Execution time : 2,990.555 ms