explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LKBB

Settings
# exclusive inclusive rows x rows loops node
1. 0.064 9,399.736 ↑ 251.9 43 1

Sort (cost=930,632.76..930,659.84 rows=10,831 width=148) (actual time=9,399.727..9,399.736 rows=43 loops=1)

  • Sort Key: a.""time"", a.payments DESC
  • Sort Method: quicksort Memory: 29kB
2. 0.044 9,399.672 ↑ 251.9 43 1

Subquery Scan on a (cost=929,013.37..929,906.92 rows=10,831 width=148) (actual time=9,399.217..9,399.672 rows=43 loops=1)

3. 0.353 9,399.628 ↑ 251.9 43 1

GroupAggregate (cost=929,013.37..929,663.23 rows=10,831 width=148) (actual time=9,399.214..9,399.628 rows=43 loops=1)

  • Group Key: ((date_part('hour'::text, timezone(l.time_zone, to_timestamp(((a_1.start_time / 1000))::double precision))))::integer), pt.name
  • Filter: ((sum((- (sum(iil.patient_amount)))) / '1000000'::numeric) > '0'::numeric)
  • Rows Removed by Filter: 4
4. 0.348 9,399.275 ↑ 30.9 350 1

Sort (cost=929,013.37..929,040.44 rows=10,831 width=92) (actual time=9,399.201..9,399.275 rows=350 loops=1)

  • Sort Key: ((date_part('hour'::text, timezone(l.time_zone, to_timestamp(((a_1.start_time / 1000))::double precision))))::integer), pt.name
  • Sort Method: quicksort Memory: 52kB
5. 29.394 9,398.927 ↑ 30.9 350 1

Merge Join (cost=853,393.65..928,287.53 rows=10,831 width=92) (actual time=9,149.603..9,398.927 rows=350 loops=1)

  • Merge Cond: (i.patient_id = a_1.patient_id)
6. 168.307 6,240.426 ↑ 3.7 111,809 1

Finalize GroupAggregate (cost=477,671.78..529,473.27 rows=409,835 width=72) (actual time=5,792.009..6,240.426 rows=111,809 loops=1)

  • Group Key: i.patient_id, pt.name
7. 165.341 6,072.119 ↑ 2.2 158,440 1

Gather Merge (cost=477,671.78..520,935.03 rows=341,530 width=72) (actual time=5,792.001..6,072.119 rows=158,440 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 84.652 5,906.778 ↑ 3.2 52,821 3 / 3

Partial GroupAggregate (cost=476,671.76..480,513.97 rows=170,765 width=72) (actual time=5,768.769..5,906.778 rows=52,821 loops=3)

  • Group Key: i.patient_id, pt.name
9. 168.982 5,822.126 ↑ 1.3 135,438 3 / 3

Sort (cost=476,671.76..477,098.67 rows=170,765 width=48) (actual time=5,768.757..5,822.126 rows=135,438 loops=3)

  • Sort Key: i.patient_id, pt.name
  • Sort Method: external merge Disk: 5432kB
10. 79.139 5,653.144 ↑ 1.3 135,450 3 / 3

Hash Join (cost=335,529.57..456,577.37 rows=170,765 width=48) (actual time=3,063.417..5,653.144 rows=135,450 loops=3)

  • Hash Cond: (ii.payment_type_id = pt.id)
11. 625.256 5,573.954 ↑ 1.3 135,450 3 / 3

Hash Join (cost=335,527.31..456,123.47 rows=170,765 width=24) (actual time=3,063.348..5,573.954 rows=135,450 loops=3)

  • Hash Cond: (ii.invoice_id = i.id)
12. 1,383.128 2,987.259 ↑ 1.3 135,450 3 / 3

Hash Join (cost=186,039.48..291,341.38 rows=170,765 width=24) (actual time=1,097.865..2,987.259 rows=135,450 loops=3)

  • Hash Cond: (iil.invoice_item_id = ii.id)
13. 506.922 506.922 ↑ 1.3 1,367,316 3 / 3

Parallel Seq Scan on invoice_item_ledger iil (cost=0.00..81,690.84 rows=1,712,784 width=16) (actual time=0.006..506.922 rows=1,367,316 loops=3)

14. 187.099 1,097.209 ↑ 1.0 406,352 3 / 3

Hash (cost=178,561.71..178,561.71 rows=407,262 width=24) (actual time=1,097.208..1,097.209 rows=406,352 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 3314kB
15. 910.110 910.110 ↑ 1.0 406,352 3 / 3

Seq Scan on invoice_item ii (cost=0.00..178,561.71 rows=407,262 width=24) (actual time=0.015..910.110 rows=406,352 loops=3)

  • Filter: (payment_type_id IS NOT NULL)
  • Rows Removed by Filter: 3695606
16. 1,045.470 1,961.439 ↓ 1.0 2,687,665 3 / 3

Hash (cost=103,765.59..103,765.59 rows=2,630,259 width=16) (actual time=1,961.439..1,961.439 rows=2,687,665 loops=3)

  • Buckets: 131072 Batches: 64 Memory Usage: 2997kB
17. 915.969 915.969 ↓ 1.0 2,687,665 3 / 3

Seq Scan on invoice i (cost=0.00..103,765.59 rows=2,630,259 width=16) (actual time=0.007..915.969 rows=2,687,665 loops=3)

18. 0.025 0.051 ↑ 1.0 56 3 / 3

Hash (cost=1.56..1.56 rows=56 width=40) (actual time=0.051..0.051 rows=56 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
19. 0.026 0.026 ↑ 1.0 56 3 / 3

Seq Scan on payment_type pt (cost=0.00..1.56 rows=56 width=40) (actual time=0.009..0.026 rows=56 loops=3)

20. 0.178 3,129.107 ↑ 2.3 365 1

Materialize (cost=375,721.87..393,368.50 rows=841 width=64) (actual time=3,106.109..3,129.107 rows=365 loops=1)

21. 0.000 3,128.929 ↑ 3.6 231 1

Nested Loop (cost=375,721.87..393,366.40 rows=841 width=64) (actual time=3,106.106..3,128.929 rows=231 loops=1)

22. 0.000 3,129.415 ↑ 3.6 231 1

Gather Merge (cost=375,721.87..393,348.37 rows=841 width=52) (actual time=3,106.086..3,129.415 rows=231 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
23. 14.285 3,184.293 ↑ 4.5 77 3 / 3

Merge Left Join (cost=374,721.84..392,251.28 rows=350 width=52) (actual time=2,924.949..3,184.293 rows=77 loops=3)

  • Merge Cond: (p.id = a2.patient_id)
24. 0.071 21.011 ↑ 4.5 77 3 / 3

Sort (cost=20,015.83..20,016.70 rows=350 width=36) (actual time=20.987..21.011 rows=77 loops=3)

  • Sort Key: a_1.patient_id
  • Sort Method: quicksort Memory: 31kB
25. 0.063 20.940 ↑ 4.5 77 3 / 3

Nested Loop (cost=7,774.87..20,001.04 rows=350 width=36) (actual time=18.639..20.940 rows=77 loops=3)

26. 0.070 20.646 ↑ 4.5 77 3 / 3

Hash Join (cost=7,774.45..18,579.42 rows=350 width=28) (actual time=18.615..20.646 rows=77 loops=3)

  • Hash Cond: (a_1.chd_user_id = u.id)
27. 13.162 19.578 ↑ 4.5 77 3 / 3

Parallel Bitmap Heap Scan on appointment a_1 (cost=7,702.35..18,506.41 rows=350 width=36) (actual time=17.595..19.578 rows=77 loops=3)

  • Recheck Cond: ((appointment_type_id = 8) AND (location_id = 23))
  • Filter: ((appointment_status_id = 5) AND (to_timestamp(((start_time / 1000))::double precision) > '2020-01-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 1129
  • Heap Blocks: exact=897
28. 0.481 6.416 ↓ 0.0 0 1 / 3

BitmapAnd (cost=7,702.35..7,702.35 rows=3,190 width=0) (actual time=19.246..19.247 rows=0 loops=1)

29. 3.407 3.407 ↓ 1.1 88,868 1 / 3

Bitmap Index Scan on appointment_appointment_type_id_idx (cost=0.00..3,643.72 rows=81,505 width=0) (actual time=10.220..10.220 rows=88,868 loops=1)

  • Index Cond: (appointment_type_id = 8)
30. 2.528 2.528 ↓ 1.2 105,299 1 / 3

Bitmap Index Scan on appointment_location_id_idx (cost=0.00..4,057.96 rows=90,871 width=0) (actual time=7.585..7.585 rows=105,299 loops=1)

  • Index Cond: (location_id = 23)
31. 0.487 0.998 ↓ 1.0 1,473 3 / 3

Hash (cost=53.71..53.71 rows=1,471 width=8) (actual time=0.997..0.998 rows=1,473 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
32. 0.511 0.511 ↓ 1.0 1,473 3 / 3

Seq Scan on chd_user u (cost=0.00..53.71 rows=1,471 width=8) (actual time=0.010..0.511 rows=1,473 loops=3)

33. 0.231 0.231 ↑ 1.0 1 231 / 3

Index Only Scan using patient_pkey on patient p (cost=0.42..4.06 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=231)

  • Index Cond: (id = a_1.patient_id)
  • Heap Fetches: 44
34. 834.883 3,148.997 ↓ 1.4 44,527 3 / 3

GroupAggregate (cost=354,706.02..371,835.13 rows=31,822 width=16) (actual time=1,702.653..3,148.997 rows=44,527 loops=3)

  • Group Key: a2.patient_id
  • Filter: (count(DISTINCT a2.id) > 3)
  • Rows Removed by Filter: 35193
35. 1,548.405 2,314.114 ↓ 1.0 1,720,072 3 / 3

Sort (cost=354,706.02..358,908.74 rows=1,681,089 width=16) (actual time=1,702.634..2,314.114 rows=1,720,072 loops=3)

  • Sort Key: a2.patient_id
  • Sort Method: external merge Disk: 43856kB
36. 765.709 765.709 ↓ 1.0 1,720,088 3 / 3

Seq Scan on appointment a2 (cost=0.00..123,410.30 rows=1,681,089 width=16) (actual time=0.006..765.709 rows=1,720,088 loops=3)

  • Filter: ((appointment_type_id <> ALL ('{8,5}'::bigint[])) AND (appointment_status_id = 5))
  • Rows Removed by Filter: 617187
37. 0.210 0.231 ↑ 1.0 1 231

Materialize (cost=0.00..7.52 rows=1 width=24) (actual time=0.000..0.001 rows=1 loops=231)

38. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on location l (cost=0.00..7.51 rows=1 width=24) (actual time=0.011..0.021 rows=1 loops=1)

  • Filter: (id = 23)
  • Rows Removed by Filter: 40