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. 79.027 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. 29.821 817.552 ↓ 0.0 156,841 3 / 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. 105.826 784.629 ↓ 0.0 156,841 3 / 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. 57.457 678.803 ↓ 0.0 156,841 3 / 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. 16.155 600.984 ↓ 0.0 156,841 3 / 3

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

10. 15.368 45.077 ↓ 0.0 89,959 3 / 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. 29.674 29.674 ↓ 0.0 90,041 3 / 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.005 0.035 ↓ 0.0 23 3 / 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.030 0.030 ↓ 0.0 23 3 / 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. 539.752 539.752 ↓ 0.0 2 269,876 / 3

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. 7.811 20.362 ↓ 0.0 34,684 3 / 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. 12.551 12.551 ↓ 0.0 34,684 3 / 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. 2.377 3.102 ↓ 0.0 584 3 / 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. 0.470 0.725 ↓ 0.0 1,320 3 / 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.255 0.255 ↓ 0.0 1,320 3 / 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. 61.809 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. 151.539 228.462 ↓ 0.0 65,205 3 / 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. 54.897 76.923 ↓ 0.0 65,205 3 / 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. 22.026 22.026 ↓ 0.0 65,205 3 / 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 / 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 / 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 / 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 / 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