explain.depesz.com

PostgreSQL's explain analyze made readable

Result: caO1w

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.071 11,143.674 ↓ 52.0 52 1

Unique (cost=123,226.86..123,226.94 rows=1 width=270) (actual time=11,143.599..11,143.674 rows=52 loops=1)

2. 0.194 11,143.603 ↓ 52.0 52 1

Sort (cost=123,226.86..123,226.86 rows=1 width=270) (actual time=11,143.598..11,143.603 rows=52 loops=1)

  • Sort Key: vrotaslotm0_.mag_surname, vrotaslotm0_.mag_forenames, vrotaslotm0_.mag_magistrates_id, magistrate2_.mla_id, (max(magistrate2_.mla_assignment_date)), vrotaslotm0_.adult_sitter, vrotaslotm0_.appraiser, vrotaslotm0_.family_sitter, vrotaslotm0_.financial_year_end, vrotaslotm0_.financial_year_start, vrotaslotm0_.highest_panel_status, vrotaslotm0_.mag_dob_date, vrotaslotm0_.mag_email_address, vrotaslotm0_.mag_gender, vrotaslotm0_.mag_local_authority_member, vrotaslotm0_.mag_local_authority_name, vrotaslotm0_.mag_sit_on_welsh_speaking, vrotaslotm0_.mag_special_requirements, vrotaslotm0_.mag_special_requirements_text, vrotaslotm0_.mag_title, vrotaslotm0_.mentor, vrotaslotm0_.phone_number, vrotaslotm0_.youth_sitter, magistrate2_.mla_family_percentage_id, magistrate2_.mla_home_court_id, magistrate2_.mla_assignment_date, magistrate2_.mla_family_percentage, magistrate2_.mla_home_court_percentage, magistrate2_.mla_youth_percentage, magistrate2_.mla_youth_percentage_id
  • Sort Method: quicksort Memory: 40kB
3. 0.168 11,143.409 ↓ 52.0 52 1

GroupAggregate (cost=123,226.77..123,226.85 rows=1 width=270) (actual time=11,143.250..11,143.409 rows=52 loops=1)

  • Group Key: vrotaslotm0_.mag_surname, vrotaslotm0_.mag_forenames, vrotaslotm0_.mag_magistrates_id, magistrate2_.mla_id, vrotaslotm0_.highest_panel_status, vrotaslotm0_.mag_title, vrotaslotm0_.mag_gender, vrotaslotm0_.mag_email_address, vrotaslotm0_.mag_local_authority_member, vrotaslotm0_.mag_local_authority_name, vrotaslotm0_.mag_dob_date, vrotaslotm0_.mag_special_requirements, vrotaslotm0_.mag_special_requirements_text, vrotaslotm0_.mag_sit_on_welsh_speaking, vrotaslotm0_.adult_sitter, vrotaslotm0_.family_sitter, vrotaslotm0_.youth_sitter, vrotaslotm0_.appraiser, vrotaslotm0_.mentor, vrotaslotm0_.pan_panel_type, vrotaslotm0_.financial_year_start, vrotaslotm0_.financial_year_end, vrotaslotm0_.phone_number
4. 1.388 11,143.241 ↓ 98.0 98 1

Sort (cost=123,226.77..123,226.78 rows=1 width=258) (actual time=11,143.232..11,143.241 rows=98 loops=1)

  • Sort Key: vrotaslotm0_.mag_surname, vrotaslotm0_.mag_forenames, vrotaslotm0_.mag_magistrates_id, magistrate2_.mla_id, vrotaslotm0_.highest_panel_status, vrotaslotm0_.mag_title, vrotaslotm0_.mag_gender, vrotaslotm0_.mag_email_address, vrotaslotm0_.mag_local_authority_member, vrotaslotm0_.mag_local_authority_name, vrotaslotm0_.mag_dob_date, vrotaslotm0_.mag_special_requirements, vrotaslotm0_.mag_special_requirements_text, vrotaslotm0_.mag_sit_on_welsh_speaking, vrotaslotm0_.adult_sitter, vrotaslotm0_.family_sitter, vrotaslotm0_.youth_sitter, vrotaslotm0_.appraiser, vrotaslotm0_.mentor, vrotaslotm0_.financial_year_start, vrotaslotm0_.financial_year_end, vrotaslotm0_.phone_number
  • Sort Method: quicksort Memory: 54kB
5. 56.338 11,141.853 ↓ 98.0 98 1

Nested Loop Anti Join (cost=13,745.47..123,226.76 rows=1 width=258) (actual time=2,302.964..11,141.853 rows=98 loops=1)

  • Join Filter: ((rcs.rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs.rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs.rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id))
  • Rows Removed by Join Filter: 49126
6. 1.311 10,988.555 ↓ 120.0 120 1

Nested Loop Semi Join (cost=11,473.29..120,948.09 rows=1 width=258) (actual time=2,287.587..10,988.555 rows=120 loops=1)

  • Join Filter: ((vrotaslotm0_.mag_magistrates_id = magistrate7_.mla_magistrate_id) AND (NOT (SubPlan 15)))
  • Rows Removed by Join Filter: 3
7. 0.717 10,986.260 ↓ 123.0 123 1

Nested Loop Anti Join (cost=11,473.00..120,939.43 rows=1 width=266) (actual time=2,287.562..10,986.260 rows=123 loops=1)

8. 2.292 10,985.051 ↓ 123.0 123 1

Nested Loop (cost=11,472.72..120,924.10 rows=1 width=266) (actual time=2,287.528..10,985.051 rows=123 loops=1)

9. 5.268 10,980.422 ↓ 123.0 123 1

Nested Loop (cost=11,472.72..120,921.62 rows=1 width=270) (actual time=2,287.510..10,980.422 rows=123 loops=1)

  • Join Filter: (((magistrate1_.mag_family_only = 1) AND (NOT (alternatives: SubPlan 9 or hashed SubPlan 10)) AND ((panel23_.pan_panel_type)::text = 'FAMILY'::text)) OR ((magistrate1_.mag_youth_only = 1) AND (NOT (alternatives: SubPlan 11 or hashed SubPlan 12)) AND ((panel23_.pan_panel_type)::text = 'YOUTH'::text)) OR ((magistrate1_.mag_family_only = 0) AND (magistrate1_.mag_youth_only = 0) AND (NOT (alternatives: SubPlan 13 or hashed SubPlan 14)) AND (panelparti5_.ppa_panel_id = ljalocatio4_.ll_panel_id)))
  • Rows Removed by Join Filter: 288
10. 1.040 10,972.277 ↓ 137.0 137 1

Nested Loop (cost=11,464.41..120,859.76 rows=1 width=274) (actual time=2,287.454..10,972.277 rows=137 loops=1)

11. 1.049 10,970.691 ↓ 60.7 182 1

Nested Loop (cost=11,464.27..120,859.28 rows=3 width=274) (actual time=2,287.442..10,970.691 rows=182 loops=1)

  • Join Filter: (vrotaslotm0_.mag_magistrates_id = panelparti5_.ppa_magistrate_id)
12. 18.647 10,968.850 ↓ 72.0 72 1

Nested Loop (cost=11,463.98..120,858.46 rows=1 width=266) (actual time=2,287.413..10,968.850 rows=72 loops=1)

  • Join Filter: (vrotaslotm0_.mag_magistrates_id = magistrate2_.mla_magistrate_id)
13. 49.539 10,923.978 ↓ 349.7 5,245 1

Nested Loop (cost=11,463.69..120,852.01 rows=15 width=226) (actual time=1,655.661..10,923.978 rows=5,245 loops=1)

14. 305.036 10,801.877 ↓ 323.9 10,366 1

Subquery Scan on vrotaslotm0_ (cost=11,463.40..120,617.69 rows=32 width=218) (actual time=1,655.642..10,801.877 rows=10,366 loops=1)

  • Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)) AND (((SubPlan 5) = 0) OR ((SubPlan 6) = 10) OR (alternatives: SubPlan 7 or hashed SubPlan 8)) AND ((NOT (SubPlan 3)) OR (SubPlan 4)))
  • Rows Removed by Filter: 9043
15. 40.513 1,701.179 ↓ 56.8 19,409 1

Unique (cost=6,019.96..6,037.06 rows=342 width=230) (actual time=1,643.727..1,701.179 rows=19,409 loops=1)

16. 58.534 1,660.666 ↓ 56.8 19,409 1

Sort (cost=6,019.96..6,020.81 rows=342 width=230) (actual time=1,643.726..1,660.666 rows=19,409 loops=1)

  • Sort Key: m.mag_magistrates_id, m.mag_title, m.mag_surname, m.mag_forenames, m.mag_gender, m.mag_email_address, m.mag_local_authority_member, m.mag_local_authority_name, m.mag_dob_date, m.mag_special_requirements, m.mag_special_requirements_text, m.mag_sit_on_welsh_speaking, (CASE WHEN (pp_apr.* IS NULL) THEN false ELSE true END), (CASE WHEN (pp_men.* IS NULL) THEN false ELSE true END), (CASE WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'CHAIR'::text) THEN 'CHAIR'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'TRAINEE_CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'TRAINEE_CHAIR'::text) THEN 'TRAINEE_CHAIR'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'WINGER'::text) THEN 'WINGER'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'NOVICE_WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'NOVICE_WINGER'::text) THEN 'NOVICE_WINGER'::text ELSE NULL::text END), (CASE WHEN ((adult_mp.* IS NULL) OR (m_1.mag_youth_only = 1) OR (m_1.mag_family_only = 1)) THEN false ELSE true END), (CASE WHEN ((family_mp.* IS NULL) OR (m_1.mag_youth_only = 1)) THEN false ELSE true END), (CASE WHEN ((youth_mp.* IS NULL) OR (m_1.mag_family_only = 1)) THEN false ELSE true END), (string_agg((contact_number.con_contact_number)::text, ', '::text ORDER BY contact_number.con_contact_id))
  • Sort Method: external merge Disk: 3928kB
17. 167.308 1,602.132 ↓ 56.8 19,409 1

GroupAggregate (cost=5,981.62..6,005.56 rows=342 width=230) (actual time=1,381.667..1,602.132 rows=19,409 loops=1)

  • Group Key: m.mag_magistrates_id, (CASE WHEN (pp_apr.* IS NULL) THEN false ELSE true END), (CASE WHEN (pp_men.* IS NULL) THEN false ELSE true END), p_3.pan_panel_type, (CASE WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'CHAIR'::text) THEN 'CHAIR'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'TRAINEE_CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'TRAINEE_CHAIR'::text) THEN 'TRAINEE_CHAIR'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'WINGER'::text) THEN 'WINGER'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'NOVICE_WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'NOVICE_WINGER'::text) THEN 'NOVICE_WINGER'::text ELSE NULL::text END), 0, (CASE WHEN ((adult_mp.* IS NULL) OR (m_1.mag_youth_only = 1) OR (m_1.mag_family_only = 1)) THEN false ELSE true END), 0, (CASE WHEN ((family_mp.* IS NULL) OR (m_1.mag_youth_only = 1)) THEN false ELSE true END), 0, (CASE WHEN ((youth_mp.* IS NULL) OR (m_1.mag_family_only = 1)) THEN false ELSE true END), '2018-04-01'::date, '2019-03-31'::date
18. 318.730 1,434.824 ↓ 249.4 85,278 1

Sort (cost=5,981.62..5,982.48 rows=342 width=214) (actual time=1,381.629..1,434.824 rows=85,278 loops=1)

  • Sort Key: m.mag_magistrates_id, (CASE WHEN (pp_apr.* IS NULL) THEN false ELSE true END), (CASE WHEN (pp_men.* IS NULL) THEN false ELSE true END), (CASE WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'CHAIR'::text) THEN 'CHAIR'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'TRAINEE_CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'TRAINEE_CHAIR'::text) THEN 'TRAINEE_CHAIR'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'WINGER'::text) THEN 'WINGER'::text WHEN ((min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'NOVICE_WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text)) = 'NOVICE_WINGER'::text) THEN 'NOVICE_WINGER'::text ELSE NULL::text END), (CASE WHEN ((adult_mp.* IS NULL) OR (m_1.mag_youth_only = 1) OR (m_1.mag_family_only = 1)) THEN false ELSE true END), (CASE WHEN ((family_mp.* IS NULL) OR (m_1.mag_youth_only = 1)) THEN false ELSE true END), (CASE WHEN ((youth_mp.* IS NULL) OR (m_1.mag_family_only = 1)) THEN false ELSE true END)
  • Sort Method: external merge Disk: 13912kB
19. 158.099 1,116.094 ↓ 249.4 85,278 1

Hash Right Join (cost=5,282.41..5,967.23 rows=342 width=214) (actual time=952.057..1,116.094 rows=85,278 loops=1)

  • Hash Cond: (ofr.off_mag_magistrates_id = m.mag_magistrates_id)
20. 6.070 6.070 ↑ 1.0 30,513 1

Seq Scan on offer ofr (cost=0.00..560.13 rows=30,513 width=4) (actual time=0.013..6.070 rows=30,513 loops=1)

21. 77.452 951.925 ↓ 172.2 37,538 1

Hash (cost=5,279.69..5,279.69 rows=218 width=1,695) (actual time=951.925..951.925 rows=37,538 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3896kB
22. 30.721 874.473 ↓ 172.2 37,538 1

Nested Loop Left Join (cost=4,915.62..5,279.69 rows=218 width=1,695) (actual time=383.862..874.473 rows=37,538 loops=1)

23. 26.060 746.707 ↓ 168.8 19,409 1

Nested Loop Left Join (cost=4,915.33..5,231.14 rows=115 width=1,679) (actual time=383.851..746.707 rows=19,409 loops=1)

24. 20.788 643.011 ↓ 168.8 19,409 1

Nested Loop Left Join (cost=4,915.04..5,165.26 rows=115 width=1,105) (actual time=383.839..643.011 rows=19,409 loops=1)

25. 27.068 505.769 ↓ 168.8 19,409 1

Nested Loop (cost=4,914.75..5,099.38 rows=115 width=531) (actual time=383.819..505.769 rows=19,409 loops=1)

26. 13.798 401.065 ↓ 168.8 19,409 1

Hash Right Join (cost=4,914.46..5,051.24 rows=115 width=456) (actual time=383.798..401.065 rows=19,409 loops=1)

  • Hash Cond: (youth_mp.ppa_magistrate_id = m_1.mag_magistrates_id)
27. 1.789 15.863 ↑ 1.7 3,360 1

Subquery Scan on youth_mp (cost=896.73..1,011.63 rows=5,745 width=90) (actual time=12.370..15.863 rows=3,360 loops=1)

28. 6.969 14.074 ↑ 1.7 3,360 1

HashAggregate (cost=896.73..954.18 rows=5,745 width=62) (actual time=12.356..14.074 rows=3,360 loops=1)

  • Group Key: pp.ppa_magistrate_id, p.pan_panel_type
29. 2.351 7.105 ↓ 1.7 9,929 1

Nested Loop (cost=127.67..868.01 rows=5,745 width=62) (actual time=1.632..7.105 rows=9,929 loops=1)

30. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on panel p (cost=0.00..1.19 rows=1 width=62) (actual time=0.025..0.030 rows=1 loops=1)

  • Filter: ((pan_panel_type)::text = 'YOUTH'::text)
  • Rows Removed by Filter: 14
31. 3.249 4.724 ↓ 1.5 9,929 1

Bitmap Heap Scan on panel_participant pp (cost=127.67..800.53 rows=6,629 width=8) (actual time=1.597..4.724 rows=9,929 loops=1)

  • Recheck Cond: (ppa_panel_id = p.pan_panel_id)
  • Heap Blocks: exact=589
32. 1.475 1.475 ↓ 1.5 9,929 1

Bitmap Index Scan on panel_participant_ppa_panel_id_idx (cost=0.00..126.01 rows=6,629 width=0) (actual time=1.475..1.475 rows=9,929 loops=1)

  • Index Cond: (ppa_panel_id = p.pan_panel_id)
33. 13.634 371.404 ↓ 168.8 19,409 1

Hash (cost=4,016.29..4,016.29 rows=115 width=370) (actual time=371.404..371.404 rows=19,409 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2634kB
34. 12.123 357.770 ↓ 168.8 19,409 1

Hash Right Join (cost=3,879.51..4,016.29 rows=115 width=370) (actual time=342.410..357.770 rows=19,409 loops=1)

  • Hash Cond: (family_mp.ppa_magistrate_id = m_1.mag_magistrates_id)
35. 1.803 17.956 ↑ 1.4 4,150 1

Subquery Scan on family_mp (cost=896.73..1,011.63 rows=5,745 width=90) (actual time=14.695..17.956 rows=4,150 loops=1)

36. 7.818 16.153 ↑ 1.4 4,150 1

HashAggregate (cost=896.73..954.18 rows=5,745 width=62) (actual time=14.681..16.153 rows=4,150 loops=1)

  • Group Key: pp_1.ppa_magistrate_id, p_1.pan_panel_type
37. 2.914 8.335 ↓ 1.9 11,022 1

Nested Loop (cost=127.67..868.01 rows=5,745 width=62) (actual time=1.648..8.335 rows=11,022 loops=1)

38. 0.031 0.031 ↑ 1.0 1 1

Seq Scan on panel p_1 (cost=0.00..1.19 rows=1 width=62) (actual time=0.026..0.031 rows=1 loops=1)

  • Filter: ((pan_panel_type)::text = 'FAMILY'::text)
  • Rows Removed by Filter: 14
39. 3.894 5.390 ↓ 1.7 11,022 1

Bitmap Heap Scan on panel_participant pp_1 (cost=127.67..800.53 rows=6,629 width=8) (actual time=1.613..5.390 rows=11,022 loops=1)

  • Recheck Cond: (ppa_panel_id = p_1.pan_panel_id)
  • Heap Blocks: exact=589
40. 1.496 1.496 ↓ 1.7 11,022 1

Bitmap Index Scan on panel_participant_ppa_panel_id_idx (cost=0.00..126.01 rows=6,629 width=0) (actual time=1.496..1.496 rows=11,022 loops=1)

  • Index Cond: (ppa_panel_id = p_1.pan_panel_id)
41. 13.425 327.691 ↓ 168.8 19,409 1

Hash (cost=2,981.34..2,981.34 rows=115 width=284) (actual time=327.691..327.691 rows=19,409 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2457kB
42. 16.134 314.266 ↓ 168.8 19,409 1

Hash Right Join (cost=2,844.55..2,981.34 rows=115 width=284) (actual time=282.360..314.266 rows=19,409 loops=1)

  • Hash Cond: (adult_mp.ppa_magistrate_id = m_1.mag_magistrates_id)
43. 7.946 82.281 ↓ 3.4 19,416 1

Subquery Scan on adult_mp (cost=896.73..1,011.63 rows=5,745 width=90) (actual time=66.486..82.281 rows=19,416 loops=1)

44. 41.218 74.335 ↓ 3.4 19,416 1

HashAggregate (cost=896.73..954.18 rows=5,745 width=62) (actual time=66.471..74.335 rows=19,416 loops=1)

  • Group Key: pp_2.ppa_magistrate_id, p_2.pan_panel_type
45. 13.327 33.117 ↓ 9.7 55,854 1

Nested Loop (cost=127.67..868.01 rows=5,745 width=62) (actual time=7.725..33.117 rows=55,854 loops=1)

46. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on panel p_2 (cost=0.00..1.19 rows=1 width=62) (actual time=0.016..0.021 rows=1 loops=1)

  • Filter: ((pan_panel_type)::text = 'ADULT'::text)
  • Rows Removed by Filter: 14
47. 12.188 19.769 ↓ 8.4 55,854 1

Bitmap Heap Scan on panel_participant pp_2 (cost=127.67..800.53 rows=6,629 width=8) (actual time=7.700..19.769 rows=55,854 loops=1)

  • Recheck Cond: (ppa_panel_id = p_2.pan_panel_id)
  • Heap Blocks: exact=590
48. 7.581 7.581 ↓ 8.4 55,854 1

Bitmap Index Scan on panel_participant_ppa_panel_id_idx (cost=0.00..126.01 rows=6,629 width=0) (actual time=7.581..7.581 rows=55,854 loops=1)

  • Index Cond: (ppa_panel_id = p_2.pan_panel_id)
49. 14.876 215.851 ↓ 168.8 19,409 1

Hash (cost=1,946.38..1,946.38 rows=115 width=198) (actual time=215.850..215.851 rows=19,409 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1852kB
50. 28.951 200.975 ↓ 168.8 19,409 1

Nested Loop (cost=1,126.82..1,946.38 rows=115 width=198) (actual time=89.170..200.975 rows=19,409 loops=1)

51. 78.399 113.797 ↓ 168.8 19,409 1

HashAggregate (cost=1,126.53..1,270.16 rows=115 width=222) (actual time=89.144..113.797 rows=19,409 loops=1)

  • Group Key: pp_3.ppa_magistrate_id, p_3.pan_panel_type
  • Filter: (CASE WHEN (min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text) = 'CHAIR'::text) THEN 'CHAIR'::text WHEN (min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'TRAINEE_CHAIR'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text) = 'TRAINEE_CHAIR'::text) THEN 'TRAINEE_CHAIR'::text WHEN (min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text) = 'WINGER'::text) THEN 'WINGER'::text WHEN (min((CASE WHEN ((pp_3.ppa_participant_type)::text = 'NOVICE_WINGER'::text) THEN pp_3.ppa_participant_type ELSE NULL::character varying END)::text) = 'NOVICE_WINGER'::text) THEN 'NOVICE_WINGER'::text ELSE NULL::text END = ANY ('{NOVICE_WINGER,WINGER,TRAINEE_CHAIR,CHAIR}'::text[]))
  • Rows Removed by Filter: 7
52. 15.245 35.398 ↓ 9.7 55,854 1

Nested Loop (cost=127.67..868.01 rows=5,745 width=72) (actual time=7.117..35.398 rows=55,854 loops=1)

53. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on panel p_3 (cost=0.00..1.19 rows=1 width=62) (actual time=0.016..0.021 rows=1 loops=1)

  • Filter: ((pan_panel_type)::text = 'ADULT'::text)
  • Rows Removed by Filter: 14
54. 13.152 20.132 ↓ 8.4 55,854 1

Bitmap Heap Scan on panel_participant pp_3 (cost=127.67..800.53 rows=6,629 width=18) (actual time=7.090..20.132 rows=55,854 loops=1)

  • Recheck Cond: (ppa_panel_id = p_3.pan_panel_id)
  • Heap Blocks: exact=590
55. 6.980 6.980 ↓ 8.4 55,854 1

Bitmap Index Scan on panel_participant_ppa_panel_id_idx (cost=0.00..126.01 rows=6,629 width=0) (actual time=6.980..6.980 rows=55,854 loops=1)

  • Index Cond: (ppa_panel_id = p_3.pan_panel_id)
56. 58.227 58.227 ↑ 1.0 1 19,409

Index Scan using magistrate_idx_id on magistrate m_1 (cost=0.29..5.87 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=19,409)

  • Index Cond: (mag_magistrates_id = pp_3.ppa_magistrate_id)
57. 77.636 77.636 ↑ 1.0 1 19,409

Index Scan using magistrate_idx_id on magistrate m (cost=0.29..0.42 rows=1 width=83) (actual time=0.004..0.004 rows=1 loops=19,409)

  • Index Cond: (mag_magistrates_id = m_1.mag_magistrates_id)
58. 116.454 116.454 ↓ 0.0 0 19,409

Index Scan using panel_participant_ppa_magistrate_id_idx on panel_participant pp_apr (cost=0.29..0.56 rows=1 width=578) (actual time=0.006..0.006 rows=0 loops=19,409)

  • Index Cond: (ppa_magistrate_id = m.mag_magistrates_id)
  • Filter: ((ppa_participant_type)::text = 'APPRAISER'::text)
  • Rows Removed by Filter: 4
59. 77.636 77.636 ↓ 0.0 0 19,409

Index Scan using panel_participant_ppa_magistrate_id_idx on panel_participant pp_men (cost=0.29..0.56 rows=1 width=578) (actual time=0.004..0.004 rows=0 loops=19,409)

  • Index Cond: (ppa_magistrate_id = m.mag_magistrates_id)
  • Filter: ((ppa_participant_type)::text = 'MENTOR'::text)
  • Rows Removed by Filter: 4
60. 97.045 97.045 ↑ 1.0 2 19,409

Index Scan using contact_number_con_magistrate_id_idx on contact_number (cost=0.29..0.40 rows=2 width=20) (actual time=0.004..0.005 rows=2 loops=19,409)

  • Index Cond: (m.mag_magistrates_id = con_magistrate_id)
61.          

SubPlan (forSubquery Scan)

62. 0.841 0.841 ↑ 1.1 553 1

Seq Scan on inactivity inactivity9_ (cost=0.00..87.29 rows=597 width=4) (actual time=0.022..0.841 rows=553 loops=1)

  • Filter: ((ina_start_date <= '2018-04-04'::date) AND ((ina_end_date IS NULL) OR (ina_end_date >= '2018-04-04'::date)))
  • Rows Removed by Filter: 3000
63. 1.338 7.175 ↓ 4.7 5,889 1

Nested Loop (cost=0.71..5,351.52 rows=1,258 width=4) (actual time=0.050..7.175 rows=5,889 loops=1)

64. 0.019 0.019 ↑ 1.0 2 1

Index Scan using rota_session_date on rota_session rotasessio11_ (cost=0.28..10.52 rows=2 width=4) (actual time=0.018..0.019 rows=2 loops=1)

  • Index Cond: (rs_date = '2018-04-04'::date)
65. 5.818 5.818 ↓ 1.1 2,944 2

Index Scan using magistrate_non_availability_ma_rota_session_id_idx on magistrate_non_availability magistrate10_ (cost=0.43..2,643.10 rows=2,740 width=8) (actual time=0.018..2.909 rows=2,944 loops=2)

  • Index Cond: (ma_rota_session_id = rotasessio11_.rs_id)
66. 104.656 274.722 ↑ 1.0 1 13,082

Aggregate (cost=41.49..41.50 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=13,082)

67. 78.492 170.066 ↑ 1.4 7 13,082

Bitmap Heap Scan on sitting_pattern sittingpat15_ (cost=4.50..41.47 rows=10 width=2) (actual time=0.011..0.013 rows=7 loops=13,082)

  • Recheck Cond: (sp_magistrate_id = vrotaslotm0_.mag_magistrates_id)
  • Heap Blocks: exact=13266
68. 91.574 91.574 ↑ 1.4 7 13,082

Bitmap Index Scan on sitting_pattern_magistrate_idx (cost=0.00..4.50 rows=10 width=0) (actual time=0.007..0.007 rows=7 loops=13,082)

  • Index Cond: (sp_magistrate_id = vrotaslotm0_.mag_magistrates_id)
69. 55.656 194.796 ↑ 1.0 1 9,276

Aggregate (cost=41.51..41.52 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=9,276)

70. 74.208 139.140 ↑ 1.0 9 9,276

Bitmap Heap Scan on sitting_pattern sittingpat16_ (cost=4.50..41.49 rows=9 width=2) (actual time=0.011..0.015 rows=9 loops=9,276)

  • Recheck Cond: (sp_magistrate_id = vrotaslotm0_.mag_magistrates_id)
  • Filter: (sp_available = 0)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=13266
71. 64.932 64.932 ↑ 1.0 10 9,276

Bitmap Index Scan on sitting_pattern_magistrate_idx (cost=0.00..4.50 rows=10 width=0) (actual time=0.007..0.007 rows=10 loops=9,276)

  • Index Cond: (sp_magistrate_id = vrotaslotm0_.mag_magistrates_id)
72. 14.790 32.045 ↓ 0.0 0 2,465

Bitmap Heap Scan on sitting_pattern sittingpat17_ (cost=4.50..41.54 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=2,465)

  • Recheck Cond: (sp_magistrate_id = vrotaslotm0_.mag_magistrates_id)
  • Filter: ((sp_day_in_period = 3) AND (sp_available = 1) AND ((sp_sitting)::text = 'PM'::text))
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=3307
73. 17.255 17.255 ↑ 1.0 10 2,465

Bitmap Index Scan on sitting_pattern_magistrate_idx (cost=0.00..4.50 rows=10 width=0) (actual time=0.007..0.007 rows=10 loops=2,465)

  • Index Cond: (sp_magistrate_id = vrotaslotm0_.mag_magistrates_id)
74. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on sitting_pattern sittingpat17__1 (cost=268.17..1,421.75 rows=1,435 width=4) (never executed)

  • Recheck Cond: (sp_available = 1)
  • Filter: ((sp_day_in_period = 3) AND ((sp_sitting)::text = 'PM'::text))
75. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on sitting_pattern_available_idx (cost=0.00..267.81 rows=14,319 width=0) (never executed)

  • Index Cond: (sp_available = 1)
76. 11.593 7,547.043 ↓ 0.0 0 11,593

Subquery Scan on sittings (cost=84.56..84.79 rows=2 width=0) (actual time=0.651..0.651 rows=0 loops=11,593)

77. 23.186 7,535.450 ↓ 0.0 0 11,593

Unique (cost=84.56..84.77 rows=2 width=1,452) (actual time=0.650..0.650 rows=0 loops=11,593)

78. 289.825 7,512.264 ↓ 0.0 0 11,593

Sort (cost=84.56..84.57 rows=2 width=1,452) (actual time=0.648..0.648 rows=0 loops=11,593)

  • Sort Key: rcs_2.rcs_id, rp_1.rp_lja_id, ('LJA'::text), rs_2.rs_date, rs_2.rs_sitting_type, rv_2.rv_id, rv_2.rv_name, loc_2.loc_id, loc_2.loc_name, pan_2.pan_panel_type, bus_type_2.bt_code, bus_type_2.bt_description, rcs_2.rcs_chair_mag_magistrates_id, rcs_2.rcs_winger1_mag_magistrates_id, rcs_2.rcs_winger2_mag_magistrates_id, rcs_2.rcs_returning_bench_single_justice, rcs_2.rcs_returning_bench_single_justice_reason, rcs_2.rcs_returning_bench_chair, rcs_2.rcs_returning_bench_chair_reason, rcs_2.rcs_returning_bench_winger1, rcs_2.rcs_returning_bench_winger1_reason, rcs_2.rcs_returning_bench_winger2, rcs_2.rcs_returning_bench_winger2_reason, rcs_2.rcs_last_sitting_single_justice, rcs_2.rcs_last_sitting_chair, rcs_2.rcs_last_sitting_winger1, rcs_2.rcs_last_sitting_winger2, rcs_2.rcs_applications_single_justice, rcs_2.rcs_applications_chair, rcs_2.rcs_applications_winger1, rcs_2.rcs_applications_winger2, rcs_2.rcs_single_justice, rcs_2.rcs_language, rcs_2.rcs_confirmed, rcs_2.rcs_confirmed_timestamp, rcs_2.rcs_sitting_identifier_chair, rcs_2.rcs_sitting_identifier_winger1, rcs_2.rcs_sitting_identifier_winger2, rcs_2.rcs_parent_rcs, rcs_2.rcs_duration
  • Sort Method: quicksort Memory: 25kB
79. 672.394 7,222.439 ↓ 0.0 0 11,593

Append (cost=27.78..84.55 rows=2 width=1,452) (actual time=0.594..0.623 rows=0 loops=11,593)

80. 8.728 3,628.609 ↓ 0.0 0 11,593

Nested Loop (cost=27.78..42.32 rows=1 width=278) (actual time=0.313..0.313 rows=0 loops=11,593)

81. 7.773 3,617.016 ↓ 0.0 0 11,593

Nested Loop (cost=27.64..42.08 rows=1 width=230) (actual time=0.312..0.312 rows=0 loops=11,593)

82. 18.411 3,605.423 ↓ 0.0 0 11,593

Nested Loop (cost=27.50..41.83 rows=1 width=176) (actual time=0.310..0.311 rows=0 loops=11,593)

83. 5.863 3,582.237 ↓ 0.0 0 11,593

Nested Loop (cost=27.23..41.52 rows=1 width=156) (actual time=0.309..0.309 rows=0 loops=11,593)

84. 5.863 3,570.644 ↓ 0.0 0 11,593

Nested Loop (cost=26.96..41.20 rows=1 width=156) (actual time=0.308..0.308 rows=0 loops=11,593)

85. 16.981 3,559.051 ↓ 0.0 0 11,593

Nested Loop (cost=26.68..40.90 rows=1 width=152) (actual time=0.306..0.307 rows=0 loops=11,593)

86. 14.499 3,535.865 ↓ 0.0 0 11,593

Nested Loop (cost=26.40..40.57 rows=1 width=152) (actual time=0.305..0.305 rows=0 loops=11,593)

87. 69.558 3,512.679 ↓ 0.0 0 11,593

Nested Loop (cost=26.11..38.16 rows=1 width=136) (actual time=0.303..0.303 rows=0 loops=11,593)

88. 115.930 115.930 ↑ 1.0 1 11,593

Index Scan using rota_session_idx on rota_session rs_2 (cost=0.28..8.30 rows=1 width=11) (actual time=0.009..0.010 rows=1 loops=11,593)

  • Index Cond: ((rs_date = '2018-04-04'::date) AND ((rs_sitting_type)::text = 'AM'::text))
89. 81.151 3,327.191 ↓ 0.0 0 11,593

Bitmap Heap Scan on rota_court_session rcs_2 (cost=25.83..29.85 rows=1 width=133) (actual time=0.287..0.287 rows=0 loops=11,593)

  • Recheck Cond: ((rcs_rota_session_id = rs_2.rs_id) AND ((rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)))
  • Heap Blocks: exact=1241
90. 602.836 3,246.040 ↓ 0.0 0 11,593

BitmapAnd (cost=25.83..25.83 rows=1 width=0) (actual time=0.280..0.280 rows=0 loops=11,593)

91. 2,295.414 2,295.414 ↓ 1.6 581 11,593

Bitmap Index Scan on rota_court_session_idx_id_id_id_id (cost=0.00..11.10 rows=356 width=0) (actual time=0.198..0.198 rows=581 loops=11,593)

  • Index Cond: (rcs_rota_session_id = rs_2.rs_id)
92. 34.779 347.790 ↓ 0.0 0 11,593

BitmapOr (cost=14.49..14.49 rows=147 width=0) (actual time=0.030..0.030 rows=0 loops=11,593)

93. 115.930 115.930 ↑ 3.1 23 11,593

Bitmap Index Scan on rota_court_session_chair_idx (cost=0.00..4.96 rows=72 width=0) (actual time=0.010..0.010 rows=23 loops=11,593)

  • Index Cond: (rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
94. 104.337 104.337 ↑ 1.6 24 11,593

Bitmap Index Scan on rota_court_session_winger1_idx (cost=0.00..4.72 rows=39 width=0) (actual time=0.009..0.009 rows=24 loops=11,593)

  • Index Cond: (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
95. 92.744 92.744 ↑ 1.7 21 11,593

Bitmap Index Scan on rota_court_session_winger2_idx (cost=0.00..4.69 rows=36 width=0) (actual time=0.008..0.008 rows=21 loops=11,593)

  • Index Cond: (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
96. 8.687 8.687 ↑ 1.0 1 1,241

Index Scan using rv_id_pk on rota_venue rv_2 (cost=0.28..2.41 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=1,241)

  • Index Cond: (rv_id = rcs_2.rcs_rv_id)
97. 6.205 6.205 ↑ 1.0 1 1,241

Index Scan using rlv_id_pk on rota_lja_venue rlv_1 (cost=0.28..0.32 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1,241)

  • Index Cond: (rlv_id = rv_2.rv_rlv_id)
98. 5.730 5.730 ↑ 1.0 1 955

Index Scan using rll_id_pk on rota_lja_location rll_1 (cost=0.28..0.31 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=955)

  • Index Cond: (rll_id = rlv_1.rlv_rll_id)
99. 5.730 5.730 ↑ 1.0 1 955

Index Scan using rp_rota_period_pk on rota_period rp_1 (cost=0.28..0.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=955)

  • Index Cond: (rp_id = rll_1.rll_rp_id)
100. 4.775 4.775 ↑ 1.0 1 955

Index Scan using loc_location_pk on location loc_2 (cost=0.27..0.31 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=955)

  • Index Cond: (loc_id = rll_1.rll_loc_id)
101. 3.820 3.820 ↑ 1.0 1 955

Index Only Scan using panel_idx_id_type on panel pan_2 (cost=0.14..0.24 rows=1 width=62) (actual time=0.004..0.004 rows=1 loops=955)

  • Index Cond: (pan_panel_id = rcs_2.rcs_panel_type_id)
  • Heap Fetches: 955
102. 2.865 2.865 ↑ 1.0 1 955

Index Scan using bt_id_pk on business_type bus_type_2 (cost=0.14..0.24 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=955)

  • Index Cond: (bt_id = rcs_2.rcs_specialist_business_type_id)
103. 10.735 2,921.436 ↓ 0.0 0 11,593

Nested Loop (cost=27.65..42.20 rows=1 width=278) (actual time=0.252..0.252 rows=0 loops=11,593)

104. 0.000 2,909.843 ↓ 0.0 0 11,593

Nested Loop (cost=27.51..41.96 rows=1 width=230) (actual time=0.251..0.251 rows=0 loops=11,593)

105. 10.163 2,909.843 ↓ 0.0 0 11,593

Nested Loop (cost=27.37..41.71 rows=1 width=176) (actual time=0.251..0.251 rows=0 loops=11,593)

106. 10.449 2,898.250 ↓ 0.0 0 11,593

Nested Loop (cost=27.10..41.38 rows=1 width=156) (actual time=0.250..0.250 rows=0 loops=11,593)

107. 0.000 2,886.657 ↓ 0.0 0 11,593

Nested Loop (cost=26.95..41.19 rows=1 width=156) (actual time=0.249..0.249 rows=0 loops=11,593)

108. 10.352 2,886.657 ↓ 0.0 0 11,593

Nested Loop (cost=26.68..40.88 rows=1 width=152) (actual time=0.249..0.249 rows=0 loops=11,593)

109. 6.629 2,875.064 ↓ 0.0 0 11,593

Nested Loop (cost=26.40..40.57 rows=1 width=152) (actual time=0.248..0.248 rows=0 loops=11,593)

110. 46.372 2,863.471 ↓ 0.0 0 11,593

Nested Loop (cost=26.11..38.16 rows=1 width=136) (actual time=0.247..0.247 rows=0 loops=11,593)

111. 81.151 81.151 ↑ 1.0 1 11,593

Index Scan using rota_session_idx on rota_session rs_3 (cost=0.28..8.30 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=11,593)

  • Index Cond: ((rs_date = '2018-04-04'::date) AND ((rs_sitting_type)::text = 'AM'::text))
112. 69.558 2,735.948 ↓ 0.0 0 11,593

Bitmap Heap Scan on rota_court_session rcs_3 (cost=25.83..29.85 rows=1 width=133) (actual time=0.236..0.236 rows=0 loops=11,593)

  • Recheck Cond: ((rcs_rota_session_id = rs_3.rs_id) AND ((rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)))
  • Heap Blocks: exact=1241
113. 521.685 2,666.390 ↓ 0.0 0 11,593

BitmapAnd (cost=25.83..25.83 rows=1 width=0) (actual time=0.230..0.230 rows=0 loops=11,593)

114. 1,889.659 1,889.659 ↓ 1.6 581 11,593

Bitmap Index Scan on rota_court_session_idx_id_id_id_id (cost=0.00..11.10 rows=356 width=0) (actual time=0.163..0.163 rows=581 loops=11,593)

  • Index Cond: (rcs_rota_session_id = rs_3.rs_id)
115. 11.593 255.046 ↓ 0.0 0 11,593

BitmapOr (cost=14.49..14.49 rows=147 width=0) (actual time=0.022..0.022 rows=0 loops=11,593)

116. 81.151 81.151 ↑ 3.1 23 11,593

Bitmap Index Scan on rota_court_session_chair_idx (cost=0.00..4.96 rows=72 width=0) (actual time=0.007..0.007 rows=23 loops=11,593)

  • Index Cond: (rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
117. 81.151 81.151 ↑ 1.6 24 11,593

Bitmap Index Scan on rota_court_session_winger1_idx (cost=0.00..4.72 rows=39 width=0) (actual time=0.007..0.007 rows=24 loops=11,593)

  • Index Cond: (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
118. 81.151 81.151 ↑ 1.7 21 11,593

Bitmap Index Scan on rota_court_session_winger2_idx (cost=0.00..4.69 rows=36 width=0) (actual time=0.007..0.007 rows=21 loops=11,593)

  • Index Cond: (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
119. 4.964 4.964 ↑ 1.0 1 1,241

Index Scan using rv_id_pk on rota_venue rv_3 (cost=0.28..2.41 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1,241)

  • Index Cond: (rv_id = rcs_3.rcs_rv_id)
120. 1.241 1.241 ↓ 0.0 0 1,241

Index Scan using rcpv_id_pk on rota_combined_panel_venue rcpv_1 (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,241)

  • Index Cond: (rcpv_id = rv_3.rv_rcpv_id)
121. 1.430 1.430 ↑ 1.0 1 286

Index Scan using rcpl_id_pk on rota_combined_panel_location rcpl_1 (cost=0.28..0.31 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=286)

  • Index Cond: (rcpl_id = rcpv_1.rcpv_rcpl_id)
122. 1.144 1.144 ↑ 1.0 1 286

Index Scan using cprp_combined_panel_rota_period_pk on combined_panel_rota_period cprp_1 (cost=0.15..0.18 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=286)

  • Index Cond: (cprp_id = rcpl_1.rcpl_cprp_id)
123. 1.430 1.430 ↑ 1.0 1 286

Index Scan using loc_location_pk on location loc_3 (cost=0.27..0.33 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=286)

  • Index Cond: (loc_id = rcpl_1.rcpl_loc_id)
124. 1.144 1.144 ↑ 1.0 1 286

Index Only Scan using panel_idx_id_type on panel pan_3 (cost=0.14..0.24 rows=1 width=62) (actual time=0.004..0.004 rows=1 loops=286)

  • Index Cond: (pan_panel_id = rcs_3.rcs_panel_type_id)
  • Heap Fetches: 286
125. 0.858 0.858 ↑ 1.0 1 286

Index Scan using bt_id_pk on business_type bus_type_3 (cost=0.14..0.24 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=286)

  • Index Cond: (bt_id = rcs_3.rcs_specialist_business_type_id)
126. 1.240 739.040 ↓ 0.0 0 1,240

Subquery Scan on sittings_1 (cost=109.75..109.97 rows=2 width=0) (actual time=0.596..0.596 rows=0 loops=1,240)

127. 2.480 737.800 ↓ 0.0 0 1,240

Unique (cost=109.75..109.95 rows=2 width=1,452) (actual time=0.595..0.595 rows=0 loops=1,240)

128. 24.800 735.320 ↓ 0.0 0 1,240

Sort (cost=109.75..109.75 rows=2 width=1,452) (actual time=0.593..0.593 rows=0 loops=1,240)

  • Sort Key: rcs_4.rcs_id, rp_2.rp_lja_id, ('LJA'::text), rs_4.rs_date, rs_4.rs_sitting_type, rv_4.rv_id, rv_4.rv_name, loc_4.loc_id, loc_4.loc_name, pan_4.pan_panel_type, bus_type_4.bt_code, bus_type_4.bt_description, rcs_4.rcs_chair_mag_magistrates_id, rcs_4.rcs_winger1_mag_magistrates_id, rcs_4.rcs_winger2_mag_magistrates_id, rcs_4.rcs_returning_bench_single_justice, rcs_4.rcs_returning_bench_single_justice_reason, rcs_4.rcs_returning_bench_chair, rcs_4.rcs_returning_bench_chair_reason, rcs_4.rcs_returning_bench_winger1, rcs_4.rcs_returning_bench_winger1_reason, rcs_4.rcs_returning_bench_winger2, rcs_4.rcs_returning_bench_winger2_reason, rcs_4.rcs_last_sitting_single_justice, rcs_4.rcs_last_sitting_chair, rcs_4.rcs_last_sitting_winger1, rcs_4.rcs_last_sitting_winger2, rcs_4.rcs_applications_single_justice, rcs_4.rcs_applications_chair, rcs_4.rcs_applications_winger1, rcs_4.rcs_applications_winger2, rcs_4.rcs_single_justice, rcs_4.rcs_language, rcs_4.rcs_confirmed, rcs_4.rcs_confirmed_timestamp, rcs_4.rcs_sitting_identifier_chair, rcs_4.rcs_sitting_identifier_winger1, rcs_4.rcs_sitting_identifier_winger2, rcs_4.rcs_parent_rcs, rcs_4.rcs_duration
  • Sort Method: quicksort Memory: 25kB
129. 2.480 710.520 ↓ 0.0 0 1,240

Append (cost=27.78..109.74 rows=2 width=1,452) (actual time=0.570..0.573 rows=0 loops=1,240)

130. 0.000 359.600 ↓ 0.0 0 1,240

Nested Loop (cost=27.78..56.32 rows=1 width=278) (actual time=0.290..0.290 rows=0 loops=1,240)

131. 1.201 359.600 ↓ 0.0 0 1,240

Nested Loop (cost=27.64..56.08 rows=1 width=230) (actual time=0.289..0.290 rows=0 loops=1,240)

132. 1.188 358.360 ↓ 0.0 0 1,240

Nested Loop (cost=27.50..55.83 rows=1 width=176) (actual time=0.289..0.289 rows=0 loops=1,240)

133. 1.188 357.120 ↓ 0.0 0 1,240

Nested Loop (cost=27.23..47.53 rows=1 width=156) (actual time=0.288..0.288 rows=0 loops=1,240)

134. 0.185 355.880 ↓ 0.0 0 1,240

Nested Loop (cost=26.96..41.22 rows=1 width=156) (actual time=0.287..0.287 rows=0 loops=1,240)

135. 3.717 350.920 ↑ 1.0 1 1,240

Nested Loop (cost=26.68..40.90 rows=1 width=152) (actual time=0.281..0.283 rows=1 loops=1,240)

136. 3.716 343.480 ↑ 1.0 1 1,240

Nested Loop (cost=26.40..40.57 rows=1 width=152) (actual time=0.276..0.277 rows=1 loops=1,240)

137. 8.680 334.800 ↑ 1.0 1 1,240

Nested Loop (cost=26.11..38.16 rows=1 width=136) (actual time=0.269..0.270 rows=1 loops=1,240)

138. 8.680 8.680 ↑ 1.0 1 1,240

Index Scan using rota_session_idx on rota_session rs_4 (cost=0.28..8.30 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=1,240)

  • Index Cond: ((rs_date = '2018-04-04'::date) AND ((rs_sitting_type)::text = 'AM'::text))
139. 9.920 317.440 ↑ 1.0 1 1,240

Bitmap Heap Scan on rota_court_session rcs_4 (cost=25.83..29.85 rows=1 width=133) (actual time=0.256..0.256 rows=1 loops=1,240)

  • Recheck Cond: ((rcs_rota_session_id = rs_4.rs_id) AND ((rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)))
  • Heap Blocks: exact=1241
140. 62.000 307.520 ↓ 0.0 0 1,240

BitmapAnd (cost=25.83..25.83 rows=1 width=0) (actual time=0.248..0.248 rows=0 loops=1,240)

141. 205.840 205.840 ↓ 1.6 581 1,240

Bitmap Index Scan on rota_court_session_idx_id_id_id_id (cost=0.00..11.10 rows=356 width=0) (actual time=0.166..0.166 rows=581 loops=1,240)

  • Index Cond: (rcs_rota_session_id = rs_4.rs_id)
142. 2.480 39.680 ↓ 0.0 0 1,240

BitmapOr (cost=14.49..14.49 rows=147 width=0) (actual time=0.032..0.032 rows=0 loops=1,240)

143. 13.640 13.640 ↑ 1.5 48 1,240

Bitmap Index Scan on rota_court_session_chair_idx (cost=0.00..4.96 rows=72 width=0) (actual time=0.011..0.011 rows=48 loops=1,240)

  • Index Cond: (rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
144. 12.400 12.400 ↓ 1.1 42 1,240

Bitmap Index Scan on rota_court_session_winger1_idx (cost=0.00..4.72 rows=39 width=0) (actual time=0.010..0.010 rows=42 loops=1,240)

  • Index Cond: (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
145. 11.160 11.160 ↓ 1.1 38 1,240

Bitmap Index Scan on rota_court_session_winger2_idx (cost=0.00..4.69 rows=36 width=0) (actual time=0.009..0.009 rows=38 loops=1,240)

  • Index Cond: (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
146. 4.964 4.964 ↑ 1.0 1 1,241

Index Scan using rv_id_pk on rota_venue rv_4 (cost=0.28..2.41 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1,241)

  • Index Cond: (rv_id = rcs_4.rcs_rv_id)
147. 3.723 3.723 ↑ 1.0 1 1,241

Index Scan using rlv_id_pk on rota_lja_venue rlv_2 (cost=0.28..0.32 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,241)

  • Index Cond: (rlv_id = rv_4.rv_rlv_id)
148. 4.775 4.775 ↓ 0.0 0 955

Index Scan using rll_id_pk on rota_lja_location rll_2 (cost=0.28..0.31 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=955)

  • Index Cond: (rll_id = rlv_2.rlv_rll_id)
  • Filter: (rll_loc_id = 287)
  • Rows Removed by Filter: 1
149. 0.052 0.052 ↑ 1.0 1 13

Index Scan using rp_rota_period_pk on rota_period rp_2 (cost=0.28..6.29 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=13)

  • Index Cond: (rp_id = rll_2.rll_rp_id)
150. 0.052 0.052 ↑ 1.0 1 13

Index Scan using loc_location_pk on location loc_4 (cost=0.27..8.29 rows=1 width=24) (actual time=0.003..0.004 rows=1 loops=13)

  • Index Cond: (loc_id = 287)
151. 0.039 0.039 ↑ 1.0 1 13

Index Only Scan using panel_idx_id_type on panel pan_4 (cost=0.14..0.24 rows=1 width=62) (actual time=0.003..0.003 rows=1 loops=13)

  • Index Cond: (pan_panel_id = rcs_4.rcs_panel_type_id)
  • Heap Fetches: 13
152. 0.026 0.026 ↑ 1.0 1 13

Index Scan using bt_id_pk on business_type bus_type_4 (cost=0.14..0.24 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (bt_id = rcs_4.rcs_specialist_business_type_id)
153. 1.240 348.440 ↓ 0.0 0 1,240

Nested Loop (cost=27.65..53.39 rows=1 width=278) (actual time=0.281..0.281 rows=0 loops=1,240)

154. 0.000 347.200 ↓ 0.0 0 1,240

Nested Loop (cost=27.51..53.14 rows=1 width=230) (actual time=0.280..0.280 rows=0 loops=1,240)

155. 1.240 347.200 ↓ 0.0 0 1,240

Nested Loop (cost=27.37..52.89 rows=1 width=176) (actual time=0.280..0.280 rows=0 loops=1,240)

156. 0.000 345.960 ↓ 0.0 0 1,240

Nested Loop (cost=27.10..44.59 rows=1 width=156) (actual time=0.279..0.279 rows=0 loops=1,240)

157. 1.050 345.960 ↓ 0.0 0 1,240

Nested Loop (cost=26.95..41.21 rows=1 width=156) (actual time=0.279..0.279 rows=0 loops=1,240)

158. 2.479 343.480 ↓ 0.0 0 1,240

Nested Loop (cost=26.68..40.88 rows=1 width=152) (actual time=0.276..0.277 rows=0 loops=1,240)

159. 3.716 339.760 ↑ 1.0 1 1,240

Nested Loop (cost=26.40..40.57 rows=1 width=152) (actual time=0.273..0.274 rows=1 loops=1,240)

160. 8.680 331.080 ↑ 1.0 1 1,240

Nested Loop (cost=26.11..38.16 rows=1 width=136) (actual time=0.266..0.267 rows=1 loops=1,240)

161. 7.440 7.440 ↑ 1.0 1 1,240

Index Scan using rota_session_idx on rota_session rs_5 (cost=0.28..8.30 rows=1 width=11) (actual time=0.006..0.006 rows=1 loops=1,240)

  • Index Cond: ((rs_date = '2018-04-04'::date) AND ((rs_sitting_type)::text = 'AM'::text))
162. 9.920 314.960 ↑ 1.0 1 1,240

Bitmap Heap Scan on rota_court_session rcs_5 (cost=25.83..29.85 rows=1 width=133) (actual time=0.254..0.254 rows=1 loops=1,240)

  • Recheck Cond: ((rcs_rota_session_id = rs_5.rs_id) AND ((rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) OR (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)))
  • Heap Blocks: exact=1241
163. 60.760 305.040 ↓ 0.0 0 1,240

BitmapAnd (cost=25.83..25.83 rows=1 width=0) (actual time=0.246..0.246 rows=0 loops=1,240)

164. 204.600 204.600 ↓ 1.6 581 1,240

Bitmap Index Scan on rota_court_session_idx_id_id_id_id (cost=0.00..11.10 rows=356 width=0) (actual time=0.165..0.165 rows=581 loops=1,240)

  • Index Cond: (rcs_rota_session_id = rs_5.rs_id)
165. 1.240 39.680 ↓ 0.0 0 1,240

BitmapOr (cost=14.49..14.49 rows=147 width=0) (actual time=0.032..0.032 rows=0 loops=1,240)

166. 13.640 13.640 ↑ 1.5 48 1,240

Bitmap Index Scan on rota_court_session_chair_idx (cost=0.00..4.96 rows=72 width=0) (actual time=0.011..0.011 rows=48 loops=1,240)

  • Index Cond: (rcs_chair_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
167. 12.400 12.400 ↓ 1.1 42 1,240

Bitmap Index Scan on rota_court_session_winger1_idx (cost=0.00..4.72 rows=39 width=0) (actual time=0.010..0.010 rows=42 loops=1,240)

  • Index Cond: (rcs_winger1_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
168. 12.400 12.400 ↓ 1.1 38 1,240

Bitmap Index Scan on rota_court_session_winger2_idx (cost=0.00..4.69 rows=36 width=0) (actual time=0.010..0.010 rows=38 loops=1,240)

  • Index Cond: (rcs_winger2_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
169. 4.964 4.964 ↑ 1.0 1 1,241

Index Scan using rv_id_pk on rota_venue rv_5 (cost=0.28..2.41 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=1,241)

  • Index Cond: (rv_id = rcs_5.rcs_rv_id)
170. 1.241 1.241 ↓ 0.0 0 1,241

Index Scan using rcpv_id_pk on rota_combined_panel_venue rcpv_2 (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,241)

  • Index Cond: (rcpv_id = rv_5.rv_rcpv_id)
171. 1.430 1.430 ↓ 0.0 0 286

Index Scan using rcpl_id_pk on rota_combined_panel_location rcpl_2 (cost=0.28..0.31 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=286)

  • Index Cond: (rcpl_id = rcpv_2.rcpv_rcpl_id)
  • Filter: (rcpl_loc_id = 287)
  • Rows Removed by Filter: 1
172. 0.000 0.000 ↓ 0.0 0

Index Scan using cprp_combined_panel_rota_period_pk on combined_panel_rota_period cprp_2 (cost=0.15..3.37 rows=1 width=8) (never executed)

  • Index Cond: (cprp_id = rcpl_2.rcpl_cprp_id)
173. 0.000 0.000 ↓ 0.0 0

Index Scan using loc_location_pk on location loc_5 (cost=0.27..8.29 rows=1 width=24) (never executed)

  • Index Cond: (loc_id = 287)
174. 0.000 0.000 ↓ 0.0 0

Index Only Scan using panel_idx_id_type on panel pan_5 (cost=0.14..0.24 rows=1 width=62) (never executed)

  • Index Cond: (pan_panel_id = rcs_5.rcs_panel_type_id)
  • Heap Fetches: 0
175. 0.000 0.000 ↓ 0.0 0

Index Scan using bt_id_pk on business_type bus_type_5 (cost=0.14..0.24 rows=1 width=24) (never executed)

  • Index Cond: (bt_id = rcs_5.rcs_specialist_business_type_id)
176. 72.562 72.562 ↑ 1.0 1 10,366

Index Scan using magistrate_idx_id on magistrate magistrate1_ (cost=0.29..7.32 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10,366)

  • Index Cond: (mag_magistrates_id = vrotaslotm0_.mag_magistrates_id)
  • Filter: (((mag_mags_leaving_date IS NULL) OR (mag_mags_leaving_date > '2018-04-04'::date)) AND (mag_family_only <> 1) AND (mag_youth_only <> 1) AND ((mag_mags_leaving_date IS NULL) OR (mag_mags_leaving_date >= now())) AND ((mag_half_day_sitting_ref IS NULL) OR ((mag_half_day_sitting_ref)::text = 'PM'::text) OR ((mag_half_day_sitting_ref)::text = 'EITHER'::text)))
  • Rows Removed by Filter: 0
177. 26.225 26.225 ↓ 0.0 0 5,245

Index Scan using magistrate_lja_assignment_mla_magistrate_id_idx on magistrate_lja_assignment magistrate2_ (cost=0.29..0.42 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=5,245)

  • Index Cond: (mla_magistrate_id = magistrate1_.mag_magistrates_id)
  • Filter: (mla_lja_id = 75)
  • Rows Removed by Filter: 1
178. 0.792 0.792 ↑ 1.0 3 72

Index Scan using panel_participant_ppa_magistrate_id_idx on panel_participant panelparti5_ (cost=0.29..0.77 rows=3 width=8) (actual time=0.006..0.011 rows=3 loops=72)

  • Index Cond: (ppa_magistrate_id = magistrate1_.mag_magistrates_id)
  • Filter: (((ppa_participant_type)::text <> 'APPRAISER'::text) AND ((ppa_participant_type)::text <> 'MENTOR'::text) AND ((ppa_participant_type)::text = ANY ('{NOVICE_WINGER,WINGER}'::text[])))
  • Rows Removed by Filter: 2
179. 0.546 0.546 ↑ 1.0 1 182

Index Only Scan using panel_idx_id_type on panel panel33_ (cost=0.14..0.16 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=182)

  • Index Cond: ((pan_panel_id = panelparti5_.ppa_panel_id) AND (pan_panel_type = 'ADULT'::text))
  • Heap Fetches: 137
180. 0.669 1.233 ↓ 3.0 3 137

Hash Join (cost=8.31..9.51 rows=1 width=70) (actual time=0.003..0.009 rows=3 loops=137)

  • Hash Cond: (panel23_.pan_panel_id = ljalocatio4_.ll_panel_id)
181. 0.548 0.548 ↑ 1.0 15 137

Seq Scan on panel panel23_ (cost=0.00..1.15 rows=15 width=62) (actual time=0.002..0.004 rows=15 loops=137)

182. 0.004 0.016 ↓ 3.0 3 1

Hash (cost=8.30..8.30 rows=1 width=12) (actual time=0.016..0.016 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
183. 0.012 0.012 ↓ 3.0 3 1

Index Scan using ll_lja_location_panel_pk on lja_location ljalocatio4_ (cost=0.28..8.30 rows=1 width=12) (actual time=0.010..0.012 rows=3 loops=1)

  • Index Cond: ((ll_lja_id = 75) AND (ll_loc_id = 287))
184.          

SubPlan (forNested Loop)

185. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..17.85 rows=1 width=0) (never executed)

186. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.62 rows=1 width=4) (never executed)

187. 0.000 0.000 ↓ 0.0 0

Index Only Scan using mll_lja_location_panel_pk on magistrate_lja_location magistrate18_ (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ((mll_magistrates_id = magistrate1_.mag_magistrates_id) AND (mll_ll_id = ljalocatio4_.ll_id))
  • Heap Fetches: 0
188. 0.000 0.000 ↓ 0.0 0

Index Scan using ll_lja_location_pk on lja_location ljalocatio19_ (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (ll_id = ljalocatio4_.ll_id)
  • Filter: ((ll_panel_id = ljalocatio4_.ll_panel_id) AND (ll_loc_id = 287))
189. 0.000 0.000 ↓ 0.0 0

Seq Scan on panel panel22_ (cost=0.00..1.23 rows=1 width=4) (never executed)

  • Filter: ((pan_panel_id = ljalocatio4_.ll_panel_id) AND ((pan_panel_type)::text = 'FAMILY'::text))
190. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=428.24..533.97 rows=4 width=12) (never executed)

191. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.29..10.07 rows=1 width=8) (never executed)

  • Join Filter: (ljalocatio19__1.ll_panel_id = panel22__1.pan_panel_id)
192. 0.000 0.000 ↓ 0.0 0

Seq Scan on panel panel22__1 (cost=0.00..1.19 rows=1 width=4) (never executed)

  • Filter: ((pan_panel_type)::text = 'FAMILY'::text)
193. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on lja_location ljalocatio19__1 (cost=4.29..8.85 rows=2 width=8) (never executed)

  • Recheck Cond: (ll_loc_id = 287)
194. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on lja_location_ll_loc_id_idx (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (ll_loc_id = 287)
195. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on magistrate_lja_location magistrate18__1 (cost=423.94..523.42 rows=48 width=8) (never executed)

  • Recheck Cond: (mll_ll_id = ljalocatio19__1.ll_id)
196. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mll_lja_location_panel_pk (cost=0.00..423.93 rows=48 width=0) (never executed)

  • Index Cond: (mll_ll_id = ljalocatio19__1.ll_id)
197. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..17.85 rows=1 width=0) (never executed)

198. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.62 rows=1 width=4) (never executed)

199. 0.000 0.000 ↓ 0.0 0

Index Only Scan using mll_lja_location_panel_pk on magistrate_lja_location magistrate24_ (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ((mll_magistrates_id = magistrate1_.mag_magistrates_id) AND (mll_ll_id = ljalocatio4_.ll_id))
  • Heap Fetches: 0
200. 0.000 0.000 ↓ 0.0 0

Index Scan using ll_lja_location_pk on lja_location ljalocatio25_ (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (ll_id = ljalocatio4_.ll_id)
  • Filter: ((ll_panel_id = ljalocatio4_.ll_panel_id) AND (ll_loc_id = 287))
201. 0.000 0.000 ↓ 0.0 0

Seq Scan on panel panel28_ (cost=0.00..1.23 rows=1 width=4) (never executed)

  • Filter: ((pan_panel_id = ljalocatio4_.ll_panel_id) AND ((pan_panel_type)::text = 'YOUTH'::text))
202. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=428.24..533.97 rows=4 width=12) (never executed)

203. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.29..10.07 rows=1 width=8) (never executed)

  • Join Filter: (ljalocatio25__1.ll_panel_id = panel28__1.pan_panel_id)
204. 0.000 0.000 ↓ 0.0 0

Seq Scan on panel panel28__1 (cost=0.00..1.19 rows=1 width=4) (never executed)

  • Filter: ((pan_panel_type)::text = 'YOUTH'::text)
205. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on lja_location ljalocatio25__1 (cost=4.29..8.85 rows=2 width=8) (never executed)

  • Recheck Cond: (ll_loc_id = 287)
206. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on lja_location_ll_loc_id_idx (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (ll_loc_id = 287)
207. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on magistrate_lja_location magistrate24__1 (cost=423.94..523.42 rows=48 width=8) (never executed)

  • Recheck Cond: (mll_ll_id = ljalocatio25__1.ll_id)
208. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mll_lja_location_panel_pk (cost=0.00..423.93 rows=48 width=0) (never executed)

  • Index Cond: (mll_ll_id = ljalocatio25__1.ll_id)
209. 0.341 1.644 ↓ 0.0 0 411

Nested Loop (cost=0.56..16.62 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=411)

210. 1.233 1.233 ↓ 0.0 0 411

Index Only Scan using mll_lja_location_panel_pk on magistrate_lja_location magistrate30_ (cost=0.29..8.31 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=411)

  • Index Cond: ((mll_magistrates_id = magistrate1_.mag_magistrates_id) AND (mll_ll_id = ljalocatio4_.ll_id))
  • Heap Fetches: 14
211. 0.070 0.070 ↑ 1.0 1 14

Index Scan using ll_lja_location_pk on lja_location ljalocatio31_ (cost=0.28..8.30 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=14)

  • Index Cond: (ll_id = ljalocatio4_.ll_id)
  • Filter: ((ll_loc_id = 287) AND (ljalocatio4_.ll_panel_id = ll_panel_id))
212. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.88..590.99 rows=62 width=12) (never executed)

  • Hash Cond: (magistrate30__1.mll_ll_id = ljalocatio31__1.ll_id)
213. 0.000 0.000 ↓ 0.0 0

Seq Scan on magistrate_lja_location magistrate30__1 (cost=0.00..497.19 rows=32,219 width=8) (never executed)

214. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.85..8.85 rows=2 width=8) (never executed)

215. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on lja_location ljalocatio31__1 (cost=4.29..8.85 rows=2 width=8) (never executed)

  • Recheck Cond: (ll_loc_id = 287)
216. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on lja_location_ll_loc_id_idx (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (ll_loc_id = 287)
217. 2.337 2.337 ↑ 1.0 1 123

Seq Scan on lja lja3_ (cost=0.00..2.46 rows=1 width=4) (actual time=0.007..0.019 rows=1 loops=123)

  • Filter: (lja_id = 75)
  • Rows Removed by Filter: 116
218. 0.492 0.492 ↓ 0.0 0 123

Index Scan using offer_off_rcs_id_off_mag_magistrates_id_off_position_idx on offer offer6_ (cost=0.29..7.81 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=123)

  • Index Cond: ((off_rcs_id = 1344677) AND (off_mag_magistrates_id = vrotaslotm0_.mag_magistrates_id) AND ((off_position)::text = 'WINGER2'::text))
  • Filter: ((off_status)::text <> 'NOT_OFFERED'::text)
219. 0.492 0.492 ↑ 1.0 1 123

Index Scan using magistrate_lja_assignment_mla_magistrate_id_idx on magistrate_lja_assignment magistrate7_ (cost=0.29..0.33 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=123)

  • Index Cond: (mla_magistrate_id = panelparti5_.ppa_magistrate_id)
  • Filter: ((mla_assignment_date <= '2018-04-04'::date) AND (mla_lja_id = 75))
  • Rows Removed by Filter: 0
220.          

SubPlan (forNested Loop Semi Join)

221. 0.492 0.492 ↓ 0.0 0 123

Index Scan using magistrate_lja_assignment_mla_magistrate_id_idx on magistrate_lja_assignment magistrate8_ (cost=0.29..8.31 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=123)

  • Index Cond: (mla_magistrate_id = vrotaslotm0_.mag_magistrates_id)
  • Filter: ((mla_assignment_date <= '2018-04-04'::date) AND (mla_assignment_date > magistrate7_.mla_assignment_date) AND (mla_lja_id <> magistrate2_.mla_lja_id))
  • Rows Removed by Filter: 1
222. 83.068 96.960 ↓ 1.7 410 120

HashAggregate (cost=2,272.18..2,274.60 rows=242 width=1,452) (actual time=0.135..0.808 rows=410 loops=120)

  • Group Key: rcs.rcs_id, rp.rp_lja_id, ('LJA'::text), rs.rs_date, rs.rs_sitting_type, rv.rv_id, rv.rv_name, loc.loc_id, loc.loc_name, pan.pan_panel_type, bus_type.bt_code, bus_type.bt_description, rcs.rcs_chair_mag_magistrates_id, rcs.rcs_winger1_mag_magistrates_id, rcs.rcs_winger2_mag_magistrates_id, rcs.rcs_returning_bench_single_justice, rcs.rcs_returning_bench_single_justice_reason, rcs.rcs_returning_bench_chair, rcs.rcs_returning_bench_chair_reason, rcs.rcs_returning_bench_winger1, rcs.rcs_returning_bench_winger1_reason, rcs.rcs_returning_bench_winger2, rcs.rcs_returning_bench_winger2_reason, rcs.rcs_last_sitting_single_justice, rcs.rcs_last_sitting_chair, rcs.rcs_last_sitting_winger1, rcs.rcs_last_sitting_winger2, rcs.rcs_applications_single_justice, rcs.rcs_applications_chair, rcs.rcs_applications_winger1, rcs.rcs_applications_winger2, rcs.rcs_single_justice, rcs.rcs_language, rcs.rcs_confirmed, rcs.rcs_confirmed_timestamp, rcs.rcs_sitting_identifier_chair, rcs.rcs_sitting_identifier_winger1, rcs.rcs_sitting_identifier_winger2, rcs.rcs_parent_rcs, rcs.rcs_duration
223. 0.090 13.892 ↓ 1.9 448 1

Append (cost=36.57..2,247.98 rows=242 width=1,452) (actual time=0.768..13.892 rows=448 loops=1)

224. 0.299 8.458 ↓ 2.9 351 1

Hash Join (cost=36.57..1,134.17 rows=121 width=278) (actual time=0.767..8.458 rows=351 loops=1)

  • Hash Cond: (rcs.rcs_specialist_business_type_id = bus_type.bt_id)
225. 0.287 8.117 ↓ 2.9 351 1

Hash Join (cost=34.29..1,131.54 rows=121 width=230) (actual time=0.717..8.117 rows=351 loops=1)

  • Hash Cond: (rcs.rcs_panel_type_id = pan.pan_panel_id)
226. 0.309 7.817 ↓ 2.9 351 1

Hash Join (cost=32.95..1,129.80 rows=121 width=176) (actual time=0.696..7.817 rows=351 loops=1)

  • Hash Cond: (rll.rll_loc_id = loc.loc_id)
227. 0.322 7.285 ↓ 2.9 351 1

Hash Join (cost=20.02..1,116.55 rows=121 width=156) (actual time=0.466..7.285 rows=351 loops=1)

  • Hash Cond: (rll.rll_rp_id = rp.rp_id)
228. 0.305 6.581 ↓ 2.9 351 1

Nested Loop (cost=1.55..1,097.76 rows=121 width=156) (actual time=0.065..6.581 rows=351 loops=1)

229. 0.707 5.223 ↓ 2.9 351 1

Nested Loop (cost=1.27..1,060.78 rows=121 width=152) (actual time=0.056..5.223 rows=351 loops=1)

230. 0.891 3.620 ↓ 3.7 448 1

Nested Loop (cost=0.99..1,021.71 rows=121 width=152) (actual time=0.040..3.620 rows=448 loops=1)

231. 0.370 1.385 ↓ 3.7 448 1

Nested Loop (cost=0.71..985.35 rows=121 width=136) (actual time=0.029..1.385 rows=448 loops=1)

232. 0.008 0.008 ↑ 1.0 1 1

Index Scan using rota_session_idx on rota_session rs (cost=0.28..8.30 rows=1 width=11) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: ((rs_date = '2018-04-04'::date) AND ((rs_sitting_type)::text = 'PM'::text))
233. 1.007 1.007 ↓ 1.3 448 1

Index Scan using rota_court_session_idx_id_id_id_id on rota_court_session rcs (cost=0.42..973.56 rows=349 width=133) (actual time=0.016..1.007 rows=448 loops=1)

  • Index Cond: (rcs_rota_session_id = rs.rs_id)
  • Filter: ((rcs_chair_mag_magistrates_id IS NOT NULL) OR (rcs_winger1_mag_magistrates_id IS NOT NULL) OR (rcs_winger2_mag_magistrates_id IS NOT NULL))
  • Rows Removed by Filter: 99
234. 1.344 1.344 ↑ 1.0 1 448

Index Scan using rv_id_pk on rota_venue rv (cost=0.28..0.30 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=448)

  • Index Cond: (rv_id = rcs.rcs_rv_id)
235. 0.896 0.896 ↑ 1.0 1 448

Index Scan using rlv_id_pk on rota_lja_venue rlv (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=448)

  • Index Cond: (rlv_id = rv.rv_rlv_id)
236. 1.053 1.053 ↑ 1.0 1 351

Index Scan using rll_id_pk on rota_lja_location rll (cost=0.28..0.31 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=351)

  • Index Cond: (rll_id = rlv.rlv_rll_id)
237. 0.195 0.382 ↑ 1.0 643 1

Hash (cost=10.43..10.43 rows=643 width=8) (actual time=0.382..0.382 rows=643 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
238. 0.187 0.187 ↑ 1.0 643 1

Seq Scan on rota_period rp (cost=0.00..10.43 rows=643 width=8) (actual time=0.014..0.187 rows=643 loops=1)

239. 0.116 0.223 ↑ 1.0 397 1

Hash (cost=7.97..7.97 rows=397 width=24) (actual time=0.223..0.223 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
240. 0.107 0.107 ↑ 1.0 397 1

Seq Scan on location loc (cost=0.00..7.97 rows=397 width=24) (actual time=0.010..0.107 rows=397 loops=1)

241. 0.005 0.013 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=62) (actual time=0.013..0.013 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
242. 0.008 0.008 ↑ 1.0 15 1

Seq Scan on panel pan (cost=0.00..1.15 rows=15 width=62) (actual time=0.004..0.008 rows=15 loops=1)

243. 0.022 0.042 ↑ 1.0 57 1

Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.042..0.042 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
244. 0.020 0.020 ↑ 1.0 57 1

Seq Scan on business_type bus_type (cost=0.00..1.57 rows=57 width=24) (actual time=0.008..0.020 rows=57 loops=1)

245. 0.097 5.344 ↑ 1.2 97 1

Hash Join (cost=50.97..1,110.19 rows=121 width=278) (actual time=3.863..5.344 rows=97 loops=1)

  • Hash Cond: (rcs_1.rcs_specialist_business_type_id = bus_type_1.bt_id)
246. 0.081 5.196 ↑ 1.2 97 1

Hash Join (cost=48.68..1,107.56 rows=121 width=230) (actual time=3.798..5.196 rows=97 loops=1)

  • Hash Cond: (rcs_1.rcs_panel_type_id = pan_1.pan_panel_id)
247. 0.088 5.098 ↑ 1.2 97 1

Hash Join (cost=47.35..1,105.82 rows=121 width=176) (actual time=3.774..5.098 rows=97 loops=1)

  • Hash Cond: (rcpl.rcpl_loc_id = loc_1.loc_id)
248. 0.085 4.428 ↑ 1.2 97 1

Hash Join (cost=34.41..1,092.57 rows=121 width=156) (actual time=3.185..4.428 rows=97 loops=1)

  • Hash Cond: (rcpl.rcpl_cprp_id = cprp.cprp_id)
249. 0.096 4.128 ↑ 1.2 97 1

Hash Join (cost=24.59..1,082.42 rows=121 width=156) (actual time=2.959..4.128 rows=97 loops=1)

  • Hash Cond: (rcpv.rcpv_rcpl_id = rcpl.rcpl_id)
250. 0.161 3.698 ↑ 1.2 97 1

Nested Loop (cost=1.27..1,058.79 rows=121 width=152) (actual time=2.614..3.698 rows=97 loops=1)

251. 0.505 3.089 ↓ 3.7 448 1

Nested Loop (cost=0.99..1,021.71 rows=121 width=152) (actual time=0.053..3.089 rows=448 loops=1)

252. 0.370 1.240 ↓ 3.7 448 1

Nested Loop (cost=0.71..985.35 rows=121 width=136) (actual time=0.042..1.240 rows=448 loops=1)

253. 0.018 0.018 ↑ 1.0 1 1

Index Scan using rota_session_idx on rota_session rs_1 (cost=0.28..8.30 rows=1 width=11) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: ((rs_date = '2018-04-04'::date) AND ((rs_sitting_type)::text = 'PM'::text))
254. 0.852 0.852 ↓ 1.3 448 1

Index Scan using rota_court_session_idx_id_id_id_id on rota_court_session rcs_1 (cost=0.42..973.56 rows=349 width=133) (actual time=0.019..0.852 rows=448 loops=1)

  • Index Cond: (rcs_rota_session_id = rs_1.rs_id)
  • Filter: ((rcs_chair_mag_magistrates_id IS NOT NULL) OR (rcs_winger1_mag_magistrates_id IS NOT NULL) OR (rcs_winger2_mag_magistrates_id IS NOT NULL))
  • Rows Removed by Filter: 99
255. 1.344 1.344 ↑ 1.0 1 448

Index Scan using rv_id_pk on rota_venue rv_1 (cost=0.28..0.30 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=448)

  • Index Cond: (rv_id = rcs_1.rcs_rv_id)
256. 0.448 0.448 ↓ 0.0 0 448

Index Scan using rcpv_id_pk on rota_combined_panel_venue rcpv (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=448)

  • Index Cond: (rcpv_id = rv_1.rv_rcpv_id)
257. 0.198 0.334 ↑ 1.0 814 1

Hash (cost=13.14..13.14 rows=814 width=12) (actual time=0.334..0.334 rows=814 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
258. 0.136 0.136 ↑ 1.0 814 1

Seq Scan on rota_combined_panel_location rcpl (cost=0.00..13.14 rows=814 width=12) (actual time=0.012..0.136 rows=814 loops=1)

259. 0.108 0.215 ↑ 1.0 348 1

Hash (cost=5.48..5.48 rows=348 width=8) (actual time=0.215..0.215 rows=348 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
260. 0.107 0.107 ↑ 1.0 348 1

Seq Scan on combined_panel_rota_period cprp (cost=0.00..5.48 rows=348 width=8) (actual time=0.023..0.107 rows=348 loops=1)

261. 0.430 0.582 ↑ 1.0 397 1

Hash (cost=7.97..7.97 rows=397 width=24) (actual time=0.582..0.582 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
262. 0.152 0.152 ↑ 1.0 397 1

Seq Scan on location loc_1 (cost=0.00..7.97 rows=397 width=24) (actual time=0.011..0.152 rows=397 loops=1)

263. 0.006 0.017 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=62) (actual time=0.017..0.017 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
264. 0.011 0.011 ↑ 1.0 15 1

Seq Scan on panel pan_1 (cost=0.00..1.15 rows=15 width=62) (actual time=0.008..0.011 rows=15 loops=1)

265. 0.024 0.051 ↑ 1.0 57 1

Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.050..0.051 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
266. 0.027 0.027 ↑ 1.0 57 1

Seq Scan on business_type bus_type_1 (cost=0.00..1.57 rows=57 width=24) (actual time=0.014..0.027 rows=57 loops=1)