explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qo2l : Optimization for: Old BM; plan #IvMd

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.741 18,003.785 ↓ 2.2 13 1

GroupAggregate (cost=530,239.74..530,240.99 rows=6 width=321) (actual time=18,003.065..18,003.785 rows=13 loops=1)

  • Group Key: chwlist.branch_uuid, chwlist.branch_name, 'multiple'::text, 'multiple'::text, ''::text, ''::text, ''::text, ''::text, (date(chwlist.interval_start)), chwlist.interval_number
2. 0.821 18,003.044 ↓ 37.5 225 1

Sort (cost=530,239.74..530,239.76 rows=6 width=477) (actual time=18,002.940..18,003.044 rows=225 loops=1)

  • Sort Key: chwlist.branch_uuid, chwlist.branch_name, (date(chwlist.interval_start)), chwlist.interval_number
  • Sort Method: quicksort Memory: 68kB
3. 0.769 18,002.223 ↓ 37.5 225 1

Merge Left Join (cost=405,623.07..530,239.67 rows=6 width=477) (actual time=16,622.905..18,002.223 rows=225 loops=1)

  • Merge Cond: (chwlist.supervisor_uuid = chp_2.supervisor_uuid)
  • Join Filter: (chwlist.interval_number = (0))
4. 0.635 17,770.544 ↓ 37.5 225 1

Merge Left Join (cost=405,622.23..502,595.17 rows=6 width=317) (actual time=16,617.309..17,770.544 rows=225 loops=1)

  • Merge Cond: (chwlist.supervisor_uuid = chp_1.supervisor_uuid)
  • Join Filter: (chwlist.interval_number = (0))
5. 1.779 4,419.028 ↓ 37.5 225 1

Merge Left Join (cost=43,940.75..45,093.07 rows=6 width=301) (actual time=4,383.709..4,419.028 rows=225 loops=1)

  • Merge Cond: (chwlist.supervisor_uuid = meta.chw)
  • Join Filter: (chwlist.interval_number = (0))
6. 0.646 165.401 ↓ 37.5 225 1

Merge Left Join (cost=1,322.39..1,323.72 rows=6 width=261) (actual time=164.489..165.401 rows=225 loops=1)

  • Merge Cond: ((chwlist.supervisor_uuid = stocks.supervisor_uuid) AND (chwlist.interval_number = stocks.interval_number))
7. 0.949 17.672 ↓ 37.5 225 1

Sort (cost=80.71..80.73 rows=6 width=101) (actual time=17.472..17.672 rows=225 loops=1)

  • Sort Key: chwlist.supervisor_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 56kB
8. 0.223 16.723 ↓ 37.5 225 1

Subquery Scan on chwlist (cost=80.41..80.64 rows=6 width=101) (actual time=15.832..16.723 rows=225 loops=1)

9. 0.548 16.500 ↓ 37.5 225 1

Group (cost=80.41..80.58 rows=6 width=118) (actual time=15.831..16.500 rows=225 loops=1)

  • Group Key: branch.uuid, branch.name, cmeta.uuid, cmeta.name
10. 1.002 15.952 ↓ 37.5 225 1

Sort (cost=80.41..80.43 rows=6 width=106) (actual time=15.817..15.952 rows=225 loops=1)

  • Sort Key: branch.uuid, branch.name, cmeta.uuid, cmeta.name
  • Sort Method: quicksort Memory: 56kB
11. 0.328 14.950 ↓ 37.5 225 1

Nested Loop (cost=0.85..80.33 rows=6 width=106) (actual time=0.760..14.950 rows=225 loops=1)

12. 0.062 0.062 ↓ 1.6 13 1

Index Scan using contactview_metadata_type on contactview_metadata branch (cost=0.43..3.16 rows=8 width=53) (actual time=0.029..0.062 rows=13 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
  • Filter: ((name <> 'HQ'::text) AND (name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 1
13. 14.560 14.560 ↓ 2.8 17 13

Index Scan using contactview_metadata_parent_uuid on contactview_metadata cmeta (cost=0.43..9.59 rows=6 width=89) (actual time=0.500..1.120 rows=17 loops=13)

  • Index Cond: (parent_uuid = branch.uuid)
  • Filter: (type = 'person'::text)
  • Rows Removed by Filter: 181
14. 0.347 147.083 ↑ 1.8 93 1

Sort (cost=1,241.68..1,242.10 rows=169 width=200) (actual time=147.008..147.083 rows=93 loops=1)

  • Sort Key: stocks.supervisor_uuid, stocks.interval_number
  • Sort Method: quicksort Memory: 38kB
15. 0.104 146.736 ↑ 1.8 93 1

Subquery Scan on stocks (cost=1,229.93..1,235.43 rows=169 width=200) (actual time=146.365..146.736 rows=93 loops=1)

16. 10.611 146.632 ↑ 1.8 93 1

HashAggregate (cost=1,229.93..1,233.74 rows=169 width=200) (actual time=146.364..146.632 rows=93 loops=1)

  • Group Key: useview_chp_visit.reported_by, 0
17. 136.021 136.021 ↓ 1.0 4,587 1

Index Scan using useview_chp_visit_reported_reported_by_uuid on useview_chp_visit (cost=0.45..978.36 rows=4,574 width=50) (actual time=0.623..136.021 rows=4,587 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
18. 1.443 4,251.848 ↑ 2.2 1,067 1

Materialize (cost=42,618.36..43,762.72 rows=2,303 width=80) (actual time=4,219.005..4,251.848 rows=1,067 loops=1)

19. 24.353 4,250.405 ↑ 2.2 1,067 1

GroupAggregate (cost=42,618.36..43,733.94 rows=2,303 width=80) (actual time=4,219.002..4,250.405 rows=1,067 loops=1)

  • Group Key: meta.chw, 0
20. 29.588 4,226.052 ↑ 4.2 9,497 1

Sort (cost=42,618.36..42,717.68 rows=39,729 width=136) (actual time=4,218.967..4,226.052 rows=9,497 loops=1)

  • Sort Key: meta.chw
  • Sort Method: quicksort Memory: 2293kB
21. 14.976 4,196.464 ↑ 4.2 9,497 1

Hash Left Join (cost=88.23..39,583.48 rows=39,729 width=136) (actual time=7.763..4,196.464 rows=9,497 loops=1)

  • Hash Cond: (meta.contact_id = chp.area_uuid)
22. 485.411 4,175.248 ↑ 4.2 9,497 1

Hash Semi Join (cost=0.52..39,344.21 rows=39,729 width=132) (actual time=1.510..4,175.248 rows=9,497 loops=1)

  • Hash Cond: (meta.formname = "*VALUES*".column1)
23. 3,689.829 3,689.829 ↑ 1.0 714,954 1

Index Scan using form_metadata_reported on form_metadata meta (cost=0.47..37,024.97 rows=715,125 width=132) (actual time=0.132..3,689.829 rows=714,954 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
24. 0.003 0.008 ↑ 1.0 2 1

Hash (cost=0.03..0.03 rows=2 width=32) (actual time=0.008..0.008 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.005 0.005 ↑ 1.0 2 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32) (actual time=0.003..0.005 rows=2 loops=1)

26. 4.204 6.240 ↑ 1.0 2,452 1

Hash (cost=57.06..57.06 rows=2,452 width=36) (actual time=6.240..6.240 rows=2,452 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 198kB
27. 2.036 2.036 ↑ 1.0 2,452 1

Index Only Scan using contactview_chp_area_uuid on contactview_chp chp (cost=0.28..57.06 rows=2,452 width=36) (actual time=0.015..2.036 rows=2,452 loops=1)

  • Heap Fetches: 326
28. 0.214 13,350.881 ↑ 1.3 87 1

Materialize (cost=361,681.48..457,501.72 rows=116 width=56) (actual time=12,233.572..13,350.881 rows=87 loops=1)

29. 923.454 13,350.667 ↑ 1.3 87 1

GroupAggregate (cost=361,681.48..457,500.27 rows=116 width=56) (actual time=12,233.568..13,350.667 rows=87 loops=1)

  • Group Key: chp_1.supervisor_uuid, 0
30. 938.013 12,427.207 ↑ 5.8 289,466 1

Sort (cost=361,681.48..365,847.46 rows=1,666,393 width=136) (actual time=12,203.527..12,427.207 rows=289,466 loops=1)

  • Sort Key: chp_1.supervisor_uuid
  • Sort Method: external merge Disk: 41704kB
31. 627.898 11,489.194 ↑ 5.8 289,466 1

Hash Join (cost=26,938.01..124,379.95 rows=1,666,393 width=136) (actual time=4,494.055..11,489.194 rows=289,466 loops=1)

  • Hash Cond: (meta_2.chw = chp_1.uuid)
32. 493.210 6,367.412 ↓ 3.2 522,370 1

Append (cost=0.54..78,360.55 rows=161,191 width=36) (actual time=0.124..6,367.412 rows=522,370 loops=1)

33. 466.922 1,111.200 ↑ 12.6 4,800 1

Hash Semi Join (cost=0.54..40,202.12 rows=60,552 width=36) (actual time=0.123..1,111.200 rows=4,800 loops=1)

  • Hash Cond: (meta_2.formname = "*VALUES*_1".column1)
34. 644.269 644.269 ↑ 1.0 723,669 1

Index Scan using form_metadata_reported on form_metadata meta_2 (cost=0.47..37,621.01 rows=726,627 width=52) (actual time=0.077..644.269 rows=723,669 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, ((((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
35. 0.005 0.009 ↑ 1.0 3 1

Hash (cost=0.04..0.04 rows=3 width=32) (actual time=0.009..0.009 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.004 0.004 ↑ 1.0 3 1

Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=32) (actual time=0.002..0.004 rows=3 loops=1)

37. 541.770 4,750.403 ↓ 5.4 512,832 1

Nested Loop Left Join (cost=2,817.62..33,611.23 rows=94,397 width=36) (actual time=43.774..4,750.403 rows=512,832 loops=1)

38. 7.728 49.906 ↑ 1.1 3,791 1

Bitmap Heap Scan on contactview_metadata clinic (cost=2,817.19..7,057.66 rows=4,356 width=36) (actual time=42.365..49.906 rows=3,791 loops=1)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, ((((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (type = 'clinic'::text))
  • Heap Blocks: exact=899
39. 0.450 42.178 ↓ 0.0 0 1

BitmapAnd (cost=2,817.19..2,817.19 rows=4,356 width=0) (actual time=42.178..42.178 rows=0 loops=1)

40. 6.742 6.742 ↓ 1.1 24,089 1

Bitmap Index Scan on contactview_metadata_reported (cost=0.00..291.92 rows=22,345 width=0) (actual time=6.742..6.742 rows=24,089 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, ((((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
41. 34.986 34.986 ↓ 1.0 238,653 1

Bitmap Index Scan on contactview_metadata_type (cost=0.00..2,522.85 rows=237,256 width=0) (actual time=34.986..34.986 rows=238,653 loops=1)

  • Index Cond: (type = 'clinic'::text)
42. 4,158.727 4,158.727 ↓ 16.9 135 3,791

Index Scan using contactview_metadata_parent_uuid on contactview_metadata chw (cost=0.43..6.02 rows=8 width=72) (actual time=0.434..1.097 rows=135 loops=3,791)

  • Index Cond: (clinic.parent_uuid = parent_uuid)
43. 12.599 12.599 ↑ 1.3 4,738 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care pnc (cost=0.46..2,935.29 rows=6,242 width=36) (actual time=0.170..12.599 rows=4,738 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, ((((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::date)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
44. 55.609 4,493.884 ↓ 1.7 42,110 1

Hash (cost=26,620.60..26,620.60 rows=25,349 width=168) (actual time=4,493.884..4,493.884 rows=42,110 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 8735kB
45. 66.427 4,438.275 ↓ 1.7 42,110 1

Nested Loop Left Join (cost=0.56..26,620.60 rows=25,349 width=168) (actual time=65.492..4,438.275 rows=42,110 loops=1)

46. 2.384 2.384 ↑ 1.0 2,452 1

Seq Scan on contactview_chp chp_1 (cost=0.00..103.52 rows=2,452 width=108) (actual time=0.010..2.384 rows=2,452 loops=1)

47. 4,369.464 4,369.464 ↓ 1.7 17 2,452

Index Scan using form_metadata_contact_id on form_metadata meta_1 (cost=0.56..10.71 rows=10 width=60) (actual time=0.368..1.782 rows=17 loops=2,452)

  • Index Cond: (chp_1.area_uuid = contact_id)
48.          

SubPlan (forGroupAggregate)

49. 0.006 0.006 ↑ 1.0 2 1

Values Scan on "*VALUES*_3" (cost=0.00..0.03 rows=2 width=32) (actual time=0.004..0.006 rows=2 loops=1)

50. 0.142 230.910 ↑ 1.3 86 1

Materialize (cost=0.84..27,644.10 rows=116 width=48) (actual time=5.582..230.910 rows=86 loops=1)

51. 9.385 230.768 ↑ 1.3 86 1

GroupAggregate (cost=0.84..27,642.65 rows=116 width=48) (actual time=5.578..230.768 rows=86 loops=1)

  • Group Key: chp_2.supervisor_uuid, 0
52. 10.734 221.383 ↓ 17.8 4,584 1

Nested Loop Semi Join (cost=0.84..27,639.55 rows=258 width=76) (actual time=0.192..221.383 rows=4,584 loops=1)

  • Join Filter: (meta_3.formname = "*VALUES*_2".column1)
  • Rows Removed by Join Filter: 1948
53. 12.648 205.091 ↓ 1.2 5,558 1

Nested Loop (cost=0.84..27,500.08 rows=4,648 width=88) (actual time=0.179..205.091 rows=5,558 loops=1)

54. 3.639 3.639 ↑ 1.0 2,452 1

Index Scan using contactview_chp_supervisor_uuid on contactview_chp chp_2 (cost=0.28..137.06 rows=2,452 width=108) (actual time=0.037..3.639 rows=2,452 loops=1)

55. 188.804 188.804 ↑ 1.0 2 2,452

Index Scan using form_metadata_contact_id on form_metadata meta_3 (cost=0.56..11.14 rows=2 width=52) (actual time=0.065..0.077 rows=2 loops=2,452)

  • Index Cond: (contact_id = chp_2.area_uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 15
56. 5.554 5.558 ↑ 2.0 1 5,558

Materialize (cost=0.00..0.04 rows=2 width=32) (actual time=0.001..0.001 rows=1 loops=5,558)

57. 0.004 0.004 ↑ 1.0 2 1

Values Scan on "*VALUES*_2" (cost=0.00..0.03 rows=2 width=32) (actual time=0.003..0.004 rows=2 loops=1)

Planning time : 23.378 ms
Execution time : 18,019.235 ms