explain.depesz.com

PostgreSQL's explain analyze made readable

Result: thZt

Settings
# exclusive inclusive rows x rows loops node
1. 7.810 5,099.978 ↓ 3,870.0 3,870 1

Subquery Scan on get_dashboard_data (cost=4,475.32..4,475.39 rows=1 width=776) (actual time=5,089.820..5,099.978 rows=3,870 loops=1)

2. 15.160 5,092.168 ↓ 3,870.0 3,870 1

Sort (cost=4,475.32..4,475.33 rows=1 width=1,476) (actual time=5,089.800..5,092.168 rows=3,870 loops=1)

  • Sort Key: (f_cast_isots((form.doc ->> 'reported_date'::text))) DESC
  • Sort Method: quicksort Memory: 2,126kB
3. 684.320 5,077.008 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=648.26..4,475.31 rows=1 width=1,476) (actual time=13.341..5,077.008 rows=3,870 loops=1)

4. 52.733 4,346.248 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=647.84..4,472.50 rows=1 width=2,192) (actual time=12.838..4,346.248 rows=3,870 loops=1)

5. 9.408 4,247.075 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=647.42..4,470.04 rows=1 width=1,700) (actual time=12.759..4,247.075 rows=3,870 loops=1)

6. 52.996 4,206.707 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=647.00..4,469.57 rows=1 width=1,718) (actual time=12.735..4,206.707 rows=3,870 loops=1)

7. 51.999 4,122.751 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=646.59..4,467.13 rows=1 width=1,662) (actual time=12.676..4,122.751 rows=3,870 loops=1)

8. 53.156 4,035.922 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=646.17..4,464.69 rows=1 width=1,643) (actual time=12.613..4,035.922 rows=3,870 loops=1)

9. 1,395.308 3,944.066 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=645.76..4,462.25 rows=1 width=1,624) (actual time=12.547..3,944.066 rows=3,870 loops=1)

  • Join Filter: ((signal_tbl1.signal_type = (form.doc ->> 'form'::text)) OR (signal_tbl1.signal_type = (form.doc #>> '{fields,signal_type}'::text[])))
  • Rows Removed by Join Filter: 65,790
10. 12.261 2,498.448 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=645.76..4,460.71 rows=1 width=1,492) (actual time=12.210..2,498.448 rows=3,870 loops=1)

11. 14.886 2,459.097 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=631.01..4,442.85 rows=1 width=1,492) (actual time=12.193..2,459.097 rows=3,870 loops=1)

12. 19.652 2,053.341 ↓ 3,870.0 3,870 1

Nested Loop Left Join (cost=616.25..4,424.75 rows=1 width=992) (actual time=11.949..2,053.341 rows=3,870 loops=1)

13. 59.878 501.169 ↓ 3,870.0 3,870 1

Nested Loop (cost=601.50..4,406.64 rows=1 width=492) (actual time=10.916..501.169 rows=3,870 loops=1)

14. 318.870 328.742 ↓ 3,881.0 3,881 1

Bitmap Heap Scan on couchdb form (cost=601.08..4,404.15 rows=1 width=492) (actual time=10.781..328.742 rows=3,881 loops=1)

  • Recheck Cond: (((((doc ->> 'form'::text) = ANY ('{1,2,3,4,5,6,7,8,9,10}'::text[])) AND (((doc ->> 'type'::text) = 'data_record'::text) IS NOT NULL)) OR (((doc ->> 'form'::text) = 'CHA_SIGNAL_REPORTING'::text) AND (((doc ->> 'type'::text) = 'data_record'::text) IS NOT NULL))) AND ((doc ->> 'type'::text) = 'data_record'::text))
  • Filter: (((doc ->> 'patient_id'::text) IS NOT NULL) AND (((doc #>> '{errors,0}'::text[]) IS NOT NULL) IS FALSE) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= ((now())::timestamp without time zone - '6 mons'::interval)) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((now())::timestamp without time zone + '1 day'::interval)))
  • Rows Removed by Filter: 1,573
  • Heap Blocks: exact=2,596
15. 0.227 9.872 ↓ 0.0 0 1

BitmapAnd (cost=601.08..601.08 rows=2,655 width=0) (actual time=9.872..9.872 rows=0 loops=1)

16. 0.002 1.294 ↓ 0.0 0 1

BitmapOr (cost=80.49..80.49 rows=7,178 width=0) (actual time=1.293..1.294 rows=0 loops=1)

17. 1.233 1.233 ↑ 1.2 5,331 1

Bitmap Index Scan on couchdb_doc_form (cost=0.00..73.17 rows=6,525 width=0) (actual time=1.232..1.233 rows=5,331 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = ANY ('{1,2,3,4,5,6,7,8,9,10}'::text[]))
18. 0.059 0.059 ↑ 3.1 212 1

Bitmap Index Scan on couchdb_doc_form (cost=0.00..7.32 rows=653 width=0) (actual time=0.058..0.059 rows=212 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'CHA_SIGNAL_REPORTING'::text)
19. 8.351 8.351 ↓ 1.0 48,660 1

Bitmap Index Scan on couchdb_doc_type (cost=0.00..520.33 rows=48,522 width=0) (actual time=8.350..8.351 rows=48,660 loops=1)

  • Index Cond: ((doc ->> 'type'::text) = 'data_record'::text)
20. 112.549 112.549 ↑ 1.0 1 3,881

Index Scan using couchdb_doc_uuid on couchdb submitter_place (cost=0.42..2.48 rows=1 width=492) (actual time=0.028..0.029 rows=1 loops=3,881)

  • Index Cond: ((doc ->> '_id'::text) = (form.doc #>> '{contact,parent,_id}'::text[]))
  • Filter: ((((doc ->> 'type'::text) = 'clinic'::text) OR ((doc ->> 'type'::text) = 'health_center'::text)) AND ((((doc ->> 'type'::text) = 'clinic'::text) AND ((form.doc ->> 'form'::text) = ANY ('{1,2,3,4,5,6,7,8,9,10}'::text[]))) OR (((doc ->> 'type'::text) = 'health_center'::text) AND ((form.doc ->> 'form'::text) = 'CHA_SIGNAL_REPORTING'::text))))
  • Rows Removed by Filter: 0
21. 158.670 1,532.520 ↑ 1.0 1 3,870

Bitmap Heap Scan on couchdb form_1 (cost=14.75..18.10 rows=1 width=500) (actual time=0.395..0.396 rows=1 loops=3,870)

  • Recheck Cond: (((doc #>> '{fields,signal_id}'::text[]) = (form.doc ->> 'patient_id'::text)) AND ((doc ->> 'form'::text) IS NOT NULL) AND ((doc ->> 'form'::text) = 'CHA_SIGNAL_VERIFICATION'::text) AND (((doc ->> 'type'::text) = 'data_record'::text) IS NOT NULL))
  • Filter: (((doc #>> '{errors,0}'::text[]) IS NULL) AND ((doc ->> 'type'::text) = 'data_record'::text))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=3,047
22. 51.660 1,373.850 ↓ 0.0 0 3,870

BitmapAnd (cost=14.75..14.75 rows=3 width=0) (actual time=0.355..0.355 rows=0 loops=3,870)

23. 27.090 27.090 ↑ 653.0 1 3,870

Bitmap Index Scan on couchdb_doc_signal_id_3 (cost=0.00..7.19 rows=653 width=0) (actual time=0.007..0.007 rows=1 loops=3,870)

  • Index Cond: ((doc #>> '{fields,signal_id}'::text[]) = (form.doc ->> 'patient_id'::text))
24. 1,295.100 1,295.100 ↓ 5.2 3,426 2,878

Bitmap Index Scan on couchdb_doc_form (cost=0.00..7.32 rows=653 width=0) (actual time=0.450..0.450 rows=3,426 loops=2,878)

  • Index Cond: ((doc ->> 'form'::text) = 'CHA_SIGNAL_VERIFICATION'::text)
25. 34.830 390.870 ↓ 0.0 0 3,870

Bitmap Heap Scan on couchdb form_2 (cost=14.75..18.10 rows=1 width=500) (actual time=0.101..0.101 rows=0 loops=3,870)

  • Recheck Cond: (((doc #>> '{fields,signal_id}'::text[]) = (form_1.doc #>> '{fields,signal_id}'::text[])) AND ((doc ->> 'form'::text) IS NOT NULL) AND ((doc ->> 'form'::text) = 'SCDSC_INVESTIGATION'::text) AND (((doc ->> 'type'::text) = 'data_record'::text) IS NOT NULL))
  • Filter: (((doc #>> '{errors,0}'::text[]) IS NULL) AND ((doc ->> 'type'::text) = 'data_record'::text))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=670
26. 37.564 356.040 ↓ 0.0 0 3,870

BitmapAnd (cost=14.75..14.75 rows=3 width=0) (actual time=0.092..0.092 rows=0 loops=3,870)

27. 23.220 23.220 ↑ 653.0 1 3,870

Bitmap Index Scan on couchdb_doc_signal_id_3 (cost=0.00..7.19 rows=653 width=0) (actual time=0.006..0.006 rows=1 loops=3,870)

  • Index Cond: ((doc #>> '{fields,signal_id}'::text[]) = (form_1.doc #>> '{fields,signal_id}'::text[]))
28. 295.256 295.256 ↓ 1.2 776 2,839

Bitmap Index Scan on couchdb_doc_form (cost=0.00..7.32 rows=653 width=0) (actual time=0.104..0.104 rows=776 loops=2,839)

  • Index Cond: ((doc ->> 'form'::text) = 'SCDSC_INVESTIGATION'::text)
29. 3.870 27.090 ↓ 0.0 0 3,870

Bitmap Heap Scan on couchdb form_3 (cost=14.75..17.84 rows=1 width=492) (actual time=0.007..0.007 rows=0 loops=3,870)

  • Recheck Cond: (((doc #>> '{fields,signal_id}'::text[]) = (form_2.doc #>> '{fields,signal_id}'::text[])) AND ((doc ->> 'form'::text) IS NOT NULL) AND ((doc ->> 'form'::text) = 'SCDSC_LAB_RESULTS'::text) AND (((doc ->> 'type'::text) = 'data_record'::text) IS NOT NULL))
  • Filter: (((doc #>> '{errors,0}'::text[]) IS NULL) AND ((doc ->> 'type'::text) = 'data_record'::text))
30. 11.664 23.220 ↓ 0.0 0 3,870

BitmapAnd (cost=14.75..14.75 rows=3 width=0) (actual time=0.006..0.006 rows=0 loops=3,870)

31. 7.740 7.740 ↓ 0.0 0 3,870

Bitmap Index Scan on couchdb_doc_signal_id_3 (cost=0.00..7.19 rows=653 width=0) (actual time=0.002..0.002 rows=0 loops=3,870)

  • Index Cond: ((doc #>> '{fields,signal_id}'::text[]) = (form_2.doc #>> '{fields,signal_id}'::text[]))
32. 3.816 3.816 ↓ 0.0 0 636

Bitmap Index Scan on couchdb_doc_form (cost=0.00..7.32 rows=653 width=0) (actual time=0.006..0.006 rows=0 loops=636)

  • Index Cond: ((doc ->> 'form'::text) = 'SCDSC_LAB_RESULTS'::text)
33. 50.310 50.310 ↑ 1.0 18 3,870

Seq Scan on signal signal_tbl1 (cost=0.00..1.18 rows=18 width=134) (actual time=0.002..0.013 rows=18 loops=3,870)

34. 38.700 38.700 ↑ 1.0 1 3,870

Index Scan using contactview_metadata_uuid on contactview_metadata reported_by (cost=0.41..2.43 rows=1 width=55) (actual time=0.009..0.010 rows=1 loops=3,870)

  • Index Cond: ((form.doc #>> '{contact,_id}'::text[]) = uuid)
35. 34.830 34.830 ↑ 1.0 1 3,870

Index Scan using contactview_metadata_uuid on contactview_metadata reported_by_parent (cost=0.41..2.43 rows=1 width=55) (actual time=0.008..0.009 rows=1 loops=3,870)

  • Index Cond: ((form.doc #>> '{contact,parent,_id}'::text[]) = uuid)
36. 30.960 30.960 ↑ 1.0 1 3,870

Index Scan using contactview_metadata_uuid on contactview_metadata reported_by_parent_parent (cost=0.41..2.43 rows=1 width=92) (actual time=0.007..0.008 rows=1 loops=3,870)

  • Index Cond: ((form.doc #>> '{contact,parent,parent,_id}'::text[]) = uuid)
37. 30.960 30.960 ↑ 1.0 1 3,870

Index Scan using contactview_metadata_uuid on contactview_metadata reported_by_parent_parent_contact (cost=0.41..0.46 rows=1 width=55) (actual time=0.007..0.008 rows=1 loops=3,870)

  • Index Cond: (reported_by_parent_parent.contact_uuid = uuid)
38. 46.440 46.440 ↑ 1.0 1 3,870

Index Scan using couchdb_doc_uuid on couchdb (cost=0.42..2.45 rows=1 width=492) (actual time=0.011..0.012 rows=1 loops=3,870)

  • Index Cond: ((doc ->> '_id'::text) = (form.doc #>> '{contact,parent,parent,parent,_id}'::text[]))
  • Filter: ((doc ->> 'type'::text) = ANY ('{contact,clinic,district_hospital,health_center,person}'::text[]))
39. 46.440 46.440 ↑ 1.0 1 3,870

Index Scan using couchdb_doc_uuid on couchdb couchdb_1 (cost=0.42..2.45 rows=1 width=492) (actual time=0.011..0.012 rows=1 loops=3,870)

  • Index Cond: ((doc ->> '_id'::text) = (form.doc #>> '{contact,parent,parent,_id}'::text[]))
  • Filter: ((doc ->> 'type'::text) = ANY ('{contact,clinic,district_hospital,health_center,person}'::text[]))
Planning time : 20.389 ms
Execution time : 5,102.490 ms