Improving performance of the /getSyncDetails call

getSyncDetails.sql

-- Improving /getSyncDetails
-- Current scenario - POST /getSyncDetails call is responsible for about 15% of our workload in our server. Average response times are at 4.17 seconds with 95% at 9.75 seconds. Throughput can go upto 10-15 rpm during peak (which is pretty bad given the response times).
-- We have already stopped checking values for programEnrolment, programEncounter and encounter to make response times better. This is resulting in extra calls to these endpoints.
-- One way to handle this can be to have a view that provides the latest update date time for each kind of entity to be synced. We should expect about 100,000 rows in this new table for all organisations together. Retrieving data will be a small number of queries, and will be fast because of indexing (not done in this gist). 
-- This sql provides a POC of such a solution that might work

reset role;
drop table if exists sync_statistics;

create table sync_statistics as
select 'subject' entity_type, organisation_id, subject_type_id as entity_type_id, address_id, sync_concept_1_value, sync_concept_2_value, max(last_modified_date_time) max_last_modified_date_time from individual
group by address_id, sync_concept_1_value, sync_concept_2_value, subject_type_id, organisation_id;


select enable_rls_on_tx_table('sync_statistics');

insert into sync_statistics
(select 'encounter' entity_type, organisation_id, encounter_type_id, address_id, sync_concept_1_value, sync_concept_2_value, max(last_modified_date_time)
    from encounter
group by address_id, sync_concept_1_value, sync_concept_2_value, encounter_type_id, organisation_id);

insert into sync_statistics
    (select 'programEncounter' entity_type, organisation_id, encounter_type_id, address_id, sync_concept_1_value, sync_concept_2_value, max(last_modified_date_time)
     from program_encounter
     group by address_id, sync_concept_1_value, sync_concept_2_value, encounter_type_id, organisation_id);


insert into sync_statistics
    (select 'programEnrolment', organisation_id, program_id, address_id, sync_concept_1_value, sync_concept_2_value, max(last_modified_date_time)
     from program_enrolment
     group by address_id, sync_concept_1_value, sync_concept_2_value, program_id, organisation_id);

grant all on public.sync_statistics to jscs;

select count(*) from sync_statistics; -- 43015

set role jscs;

-- catchment style query
select entity_type, entity_type_id, max(max_last_modified_date_time) from sync_statistics
where address_id in (109677,109678,109679,109680,109681,117479,117480,117481,117482,117483,117484,117485,117486,117487,117488,117489,117490,117491,117492,117493,117494,117495,117496,117497,117498,117499,117500,117501,117502,117503,117504,117505,117506,117507,117508,117509,117510,117511,117512,117513,117514,117515,117516,117517,117518,117519,117520,117521,117522,117523,117524,117525,117526,117527,117528,117529,117530,117531,117532,117533,117534,117535,117536,117537,117538,117539,117540,117541,117542,117543,117544,117545,117546,117547,117548,117549,117550,117551,117552,117553,117554,117555,117556,117557,117558,117559,117560,117561,117562,117563,117564,117565,117566,117567,117568,117569,117570,117571,117572,117573,117574,117575,117576,117577,117578,117579,117580,117581,117582,117583,117584,117585,117586,117587,117588,117589,117590,117591,117592,117593,117594,117595,117596,117597,117598,117599,117600,117601,117602,117603,117604,117605,117606,117607,117608,117609,117610,117611,117612,117613,117614,117615,117616,117617,117618,117619,117620,117621,117622,117623,117624,117625,117626,117627,117628,117629,117630,117631,117632,117633,117634,117635,117636,117637,117638,117639,117640,117641,117642,117643,117644,117645,117646,117647,117648,117649,117650,117651,117652,117653,117654,117655,117656,117657,117658,117659,117660,117661,117662,117663,117664,117665,117666,117667,117668,117669,117670,117671,117672,117673,117674,117675,117676,117677,117678,117679,117680,117681,117682,117683,117684,117685,117686,117687,117688,117689,117690,117691,117692,117693,117694,117695,117696,117697,117698,117699,117700,117701,117702,117703,117704,117705,117706,117707,117708,117709,117710,117711,117712,117713,117714,117715,117716,117717,117718,117719,117720,117721,117722,117723,117724,117725,117726,117727,117728,117729,117730,117731,117732,117733,117734,117735,117736,117737,117738,117739,117740,117741,117742,117743,117744,117745,117746,117747,117748,117749,117750,117751,117752,117753,117754,117755,117756,117757,117758,117759,117760,117761,117762,117763,117764,117765,117766,117767,117768,117769,117770,117771,117772,117773,117774,117775,117776,117777,117778,117779,117780,117781,117782,117783,117784,117785,117786,117787,117788,117789,117790,117791,117792,117793,117794,117795,117796,117797,117798,117799,117800,117801,117802,117803,117804,117805,117806,117807,117808,117809,117810,117811,117812,117813,117814,117815,117816,117817,117818,117819,117820,117821,117822,117823,117824,117825,117826,117827,117828,117829,117830,117831,117832,117833,117834,117835,117836,117837,117838,117839,117840,117841,117842,117843,117844,117845,117846,117847,117848,117849,117850,117851,117852,117853,117854,117855,117856,117857,117858,117859,117860,117861,117862,117863,117864,117865,117866,117867,117868,117869,117870,117871,117872,117873,117874,117875,117876,117877,117878,117879,117880,117881,117882,117883,117884,117885,117886,117887,117888,117889,117890,117891,117892,117893,117894,117895,117896,117897,117898,117899,117900,117901,117902,117903,117904,117905,117906,117907,117908,117909,117910,117911,117912,117913,117914,117915,117916,117917,117918,117919,117920,117921,117922,117923,117924,117925,117926,117927,117928,117929,117930,117931,117932,117933,117934,117935,117936,117937,117938,117939,117940,117941,117942,117943,117944,117945,117946,117947,117948,117949,117950,117951,117952,117953,117954,117955,117956,117957,117958,117959,117960,117961,117962,117963,117964,117965,117966,117967,117968,117969,117970,117971,117972,117973,117974,117975,117976,117977,117978,117979,117980,117981,117982,117983,117984,117985,117986,117987,117988,117989,117990,117991,117992,117993,117994,117995,117996,117997,117998,117999,118000,118001,118002,118003,118004,118005,118006,118007,118008,118009,118010,118011,118012,118013,118014,118015,118016,118017,118018,118019,118020,118021,118022,118023,118024,118025,118026,118027,118028,118029,118030,118031,118032,118033,118034,118035,118036,118037,118038,118039,118040,118041,118042,118043,118044,118045,118046,118047,118048,118049,118050,118051,118052,118053,118054,118055,118056,118057,118058,118059,118060,118061,118062,118063,118064,118065,118066,118067,118068,118069,118070,118071,118072,118073,118074,118075,118076,118077,118078,118079,118080,118081,118082,118083,118084,118085,118086,118087,118088,118089,118090,118091,118092,118093,118094,118095,118096,118097,118098,118099,118100,118101,118102,118103,118104,118105,118106,118107,118108,118109,118110,118111,118112,118113,118114,118115,118116,118117,118118,118119,118120,118121,118122,118123,118124,118125,118126,118127,118128,118129,118130,118131,118132,118133,118134,118135,118136,118137,118138,118139,118140,118141,118142,118143,118144,118145,118146,118147,118148,118149,118150,118151,118152,118153,118154,118155,118156,118157,118158,118159,118160,118161,118162,118163,118164,118165,118166,118167,118168,118169,118170,118171,118172,118173,118174,118175,118176,118177,118178,118179,118180,118181,118182,118183,118184,118185,118186,118187,118188,118189,118190,118191,118192,118193,118194,118195,118196,118197,118198,118199,118200,118201,118202,118203,118204,118205,118206,118207,118208,118209,118210,118211,118212,118213,118214,118215,118216,118217,118218,118219,118220,118221,118222,118223,118224,118225,118226,118227,118228,118229,118230,118231,118232,118233,118234,118235,118236,118237,118238,118239,118240,118241,118242,118243,118244,118245,118246,118247,118248,118249,118250,118251,118252,118253,118254,118255,118256,118257,118258,118259,118260,118261,118262,118263,118264,118265,118266,118267,118268,118269,118270,118271,118272,118273,118274,118275,118276,118277,118278,118279,118280,118281,118282,118283,118284,118285,118286,118287,118288,118289,118290,118291,118292,118293,118294,118295,118296,118297,118298,118299,118300,118301,118302,118303,118304,118305,118306,118307,118308,118309,118310,118311,118312,118313,118314,118315,118316,118317,118318,118319,118320,118321,118322,118323,118324,118325,118326,118327,118328,118329,118330,118331,118332,118333,118334,118335,118336,118337,118338,118339,118340,118341,118342,118343,118344,118345,118346,118347,118348,118349,118350,118351,118352,118353,118354,118355,118356,118357,118358,118359,118360,118361,118362,118363,118364,118365,118366,118367,118368,118369,118370,118371,118372,118373,118374,118375,118376,118377,118378,118379,118380,118381,118382,118383,118384,118385,118386,118387,118388,118389,118390,118391,118392,118393,118394,118395,118396,118397,118398,118399,118400,118401,118402,118403,118404,118405,118406,118407,118408,118409,118410,118411,118412,118413,118414,118415,118416,118417,118418,118419,118420,118421,118422,118423,118424,118425,118426,118427,118428,118429,118430,118431,118432,118433,118434,118435,118436,118437,118438,118439,118440,118441,118442,118443,118444,118445,118446,118447,118448,118449,118450,118451,118452,118453,118454,118455,118456,118457,118458,118459,118460,118461,118462,118463,118464,118465,118466,118467,118468,118469,118470,118471,118472,118473,118474,118475,118476,118477,118478,118479,118480,118481,118482,118483,118484,118485,118486,118487,118488,118489,118490,118491,118492,118493,118494,118495,118496,118497,118498,118499,118500,118501,118502,118503,118504,118505,118506,118507,118508,118509,118510,118511,118512,118513,118514,118515,118516,118517,118518,118519,118520,118521,118522,118523,118524,118525,118526,118527,118528,118529,118530,118531,118532,118533,118534,118535,118536,118537,118538,118539,118540,118541,118542,118543,118544,118545,118546,118547,118548,118549,118550,118551,118552,118553,118554,118555,118556,118557,118558,118559,118560,118561,118562,118563,118564,118565,118566,118567,118568,118569,118570,118571,118572,118573,118574,118575,118576,118577,118578,118579,118580,118581,118582,118583,118584,118585,118586,118587,118588,118589,118590,118591,118592,118593,118594,118595,118596,118597,118598,118599,118600,118601,118602,118603,118604,118605,118606,118607,118608,118609,118610,118611,118612,118613,118614,118615,118616,118617,118618,118619,118620,118621,118622,118623,118624,118625,118626,118627,118628,118629,118630,118631,118632,118633,118634,118635,118636,118637,118638,118639,118640,118641,118642,118643,118644,118645,118646,118647,118648,118649,118650,118651,118652,118653,118654,118655,118656,118657,118658,118659,118660,118661,118662,118663,118664,118665,118666,118667,118668,118669,118670,118671,118672,118673,118674,118675,118676,118677,118678,118679,118680,118681,118682,118683,118684,118685,118686,118687,118688,118689,118690,118691,118692,118693,118694,118695,118696,118697,118698,118699,118700,118701,118702,118703,118704,118705,118706,118707,118708,118709,118710,118711,118712,118713,118715,118716,118717,118718,118719,118720)
group by entity_type, entity_type_id;


-- For the actual call, we might need to do this separately for different subject types and encounters because the list of addresses will be different per subject type. 
-- To implement, we need to 
-- - Update the table on insert/update of sync_statistics either through Java or triggers. 
-- - Change POST /getSyncDetails to use the new table