Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sXE5Y-002Y0t-9V for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 06:06:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sXE5W-0067DD-Hl for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 06:05:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sXE5V-0067CD-Pl for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 06:05:58 +0000 Received: from sonic314-21.consmr.mail.sg3.yahoo.com ([106.10.240.145]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sXE5Q-001UlL-72 for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 06:05:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1721973946; bh=xxxFqIV8BWtv0/PPyTdsdpbS0nspg2MzY0+TGtabr68=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=Guv9hhEoksjXIfoGCbA9qXsJeAwVa+FtuS8wMDa4RWEtY3BS1lIhnACq0aa7kPMx5e+ltibu0Y6dl6XnoXpMOzaw69VBuyyZh7lu3ehLw7exGBNQl45JyPmSljwTncLXIbdLsR7O24K69fPD96sUj9PGF5Phl/B1+vCCBa8ViWdhBVRErL7F0eXkkXXJTlL6ZvdANLv6XK7+rIuSQRvy3VUZDWkMRTJbXvyIZpCkCVsbhUIJDvmOlK0MwYfYhEGQzrIQNBp3bCupDuYNhtNNxARpQzLoSVwAYFB6kAXTNHFAyfC+Ql6ZW8Hg6r9XZPcAR2jH40NBlUdx2ClsjvCpLQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1721973946; bh=mBQzzZN0orHSNJQ3790Bt4nlpfQBuLJsChMP/yHVa8/=; h=X-Sonic-MF:X-Sonic-MF:Date:From:To:Subject:From:Subject; b=boO+FaI8i7/IcnX/VEXiacGM6/ojfRuAwls300o0+DzCEffWm4h8GL/Dx5/Pn9L948nJ4T6qgoBomzj2ilOph+4knxici5Z9v6vEu2MnefCm0zFah66acLs6DYSrdChBDhlYx+MrZ/VLLwZPcjx7XhQF8skX22e/LK06VvRyURMej8+53+uuOEZjipL2WUpHsmv6pZE/DEozqbtgrDoazkv8YOdXdOJL43VP6dJt1CZJtgd4BFRr1AsbJRkcTXXoEsPJjAZyPqSjN9Jjr+GM3lvjNsg9YH0Gc2A0ABxTjSvUWhpH0oMIF38ncSYjiso3otZ76+05mAERzc5sjcZdfg== X-YMail-OSG: k4w1JCAVM1nOl8NKByDc6V_pBpj9TrhLrIhnGHUBCMVF1u8vVegtATt2wx3Uy0b 5ERHm_KNGUwDZZH7TajYx.QS4uptJp84Mwvar93g7y6V6DYLrtqXwuh6v_dSLMY3akX6F_wNiVyD Eg1iKncPd28dXuXs4XSerHqV05OTVkNpH.Ia.kh7wvBaaYKu_WaBiRwfhX_yhSoYqbkn1r_uQ7hX zDQ0go4ybj15oj7AmQZ9vXuV7iY12SXlI4LU_gOnbAwsQ_DpCSQ7Y2_9D8GHzn6EXYPU.cpivGAl PWHOvcMu4vaTge81czDokcYom0ejqkhdB6rwkvW8hM_PvNE6Jh_c5EPUDfFHHuV6LLM3BIyz4Eml z0wegCg7zUfp0IbID0WKv1_IoS3UBdGv5n6uPVi5.lqYYxnY.jqUiIaQ9N0okd59Ko2owKSMEur7 osfkXWvL_kUOCj3NVoHyACW1GV8xvIC5Y9espX._wwRbU7Rw0bJqiRNSN4BiB9vbmfIIQeIx2tSf 2XiDTTnNpAdlXiphY6a._inOA_.CGuQqLYSbfIBpQwCMxXqpz4kB.Tar.CrG_gHsAxOb_0f8SyHG s1sLhkWukJ77elsnOEio6lYyQ1o.3fXSkUwWYp5ZDXBNRxJNbGzOaWjwU7xcZMxsLD_pA7DeLcNn vtbpgD1dmNrdGc_f54ZcoMLq35UIaitEy5nwnB4a222xSuV0bqHznnWtE4ZRCi91YIMYFmhLOHJk _pjVbLDXtSyd5yWCsRtbO4MDHxjGgNA.k_xX_ogBDLbt7S55dQmM2NA8hXukboDgf69XbJAeqcRE gF2E34NIJ7Rny1WeX64MfwhGcWReoh2Rq0tvjWzbJ2OtMlawPRDVRClZOKwJTShHO8sfJqNaGG.0 yzQWZdGVmd3oWTFC4xQt3BdPDnk2JOXD29K5MGcP47AhuWHDRXPjVs532_Q7p2WKscubk4mX4QRy VPFwvTMplKWHyg4teab4emZT7wqKaIptP1zkaINm6B_pUxs4zOiUpU.TzsZBFCljyLS4b5r6.ZyI ZxqXtA9o5PCQVaGOmzRwZ6ryrXTln68AZu7w0_URYVoQ.RlbcUS9Yvgixa0aI2J3KnZYxB_jla34 EhLOLm5UslCyEBBBI1Am2X2Yj23ltKbBjBXJM81W0QtE3NWu2GCJheAv1YAADBAavB3wOv8eHn59 4JpliLXL1F7MUt7eWLxQ2eMI8JLs8yAloxfpfVUeXHKlHq2pGDzlTCl0LxWB.EN17gGSLQrWs8sO PsjlKAQkGZR9XhXrmo0._n7xbKJMrvoWIqibqWntNN4z_2hFj66dFfILr_3sPGK75qEI_A_hbC8E RbZLOQSzKddvXE_Wi5.cnYbvZd.IXCTKQdaRUQh_KE9WgB4SU.8kJVYE8BaqWe__eYcs9CdoGt1Z RsxZwlHfkaWo1tI56DZaMlqlRqBsT9ymQeCkcG_uqqI86HuHBFLNlE1jDs_0W59AI_s50IZlexPS bKAUB2VORbo1XFgL4lObNF1i8d5monZvvLntS91uB5ZI20oImQZrw5v5hfDc.HeMPS1RQPqylQ4w 1.Zxo4pDKfEtzm008vOrTGBOJkFm_4HO7xncxZLT8IMcuw3LxSg2XQmpFws4zv322TZy5DLID_Vn 00F1b4KgTJk3mqeDi_EPtRefmiNLSFLDqrg6HRFir3eWTCQ9XPwhmvxg8rXSShr5hh5uoqEm0UGX 5HILAttFf5ySCiAPp6jhKaAkcmmUHeZw8doUk217WzenVrEyx40nv21Sdcf2fglR5WV.WASno06W .uD6YZKQ5Cw_ZABxeE4jnS2s4Nx12nmuhgCDjGtSbAtiRCMycUOCyUp7Bnslc_y.erFnc0iVDGfH 19cfz0WcPdWrf6DtVg0jC3EiKQalNBvryWFzD5EGzvaL5VNX02_ku1AryLJ9QIzxH_TIPwPXmPGy ykEeRTIhpu65_bE40JCiiYCTmp58BrjpvkgnxLPwaQkgeUIgjjk0mzHXQPslasT8k8fIa22mP_fH OFc1EPW5KZ8K.OMLYaFT_TwgYgD348Yvfbo6nueOodlf2Wws4hrbODuH_WE3DqYMoOhLvQw5aKwJ KSLEVqsGHQcikW4vNnPcEQMxNqxAXwSVj4ui5EszQmVxbltsomdP7.ehxJLRtd5wPnfcLzgbtkhG LlYayRLIzuDNnH7BGnimaBy2KC7SgNHTa0NhZz.9lzWGdfYW8bWdw9LxhrWnPkCIzpRV3ikKNx6a tO4KwfIjn3JbjUeizaaQxy9Lbgc8OrdU6DwYrwnhqPWl8ZXjMgd740gwbpkGuPlBt.Sv7gMMhyF1 SmKjnUP.DGmisHlo- X-Sonic-MF: X-Sonic-ID: 4c39847d-36dc-4d71-96af-a5558c9f0cb7 Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.sg3.yahoo.com with HTTP; Fri, 26 Jul 2024 06:05:46 +0000 Received: by hermes--production-gq1-799bb7c8cf-dkgg4 (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID 2646ec637868c7c3e1cd35a9533e8e50; Fri, 26 Jul 2024 06:05:44 +0000 (UTC) X-YMail-OSG: D4qHxqsVM1lqkSAvQuhPoCACAUKTMrK0akTZakyWsSSMO8owzoRdg.y3pWwrNEA q9lzb3arj0CqugjodA0dSi6DEeQbuT_FVJj3UEpVjq_afHHudm.3MzOkOitbXRxptTmALanofKXc ZPxhXEVoyPZqzaOEQ7cm73MIrEoFYVk09Cukf3O.VJ36uTDrTeQBq0uF1sPiXylgtr4lkjsK4zxM uzU.m6R35vVA1xkLQATalkFmo8q4a02SmFlpxTONy8Wi1TaPe3JS8U74uLYl_ECCp6BvVlnC3Igv W1gct.WjlH_j.jKlSQh49Ug.VSUY8DE71jBq5HmEtg7K0izePr13FwhAD8tZPFfA_Vn5naVSXk6w 5PRxMpM2mxPm5SvMtIYJN3WJEPtSIF2HPBMIpNTUUyGTcDtMbreP5fyn4.t6CLUVqi06ZsoCd1qi xU_woNpX_dCF7md7lxBIt.J0RB0K2E86aBwuDsVCb_pP.VU0g6M4jlngMfbZckIZNQjDHUBm79GC JYVy4i2uPRTp1Rfabh0L7IiqWmEb.yVdRz8a57of7m8LsZxxY1i2CSMVjQupkgRBLiVg4WDmlYcY FoW5iU1qRizYOp8ozyxkp39vDHPLqDEssPWSz0YYu5hTWky9wYPRtw85HTcAsymARcnujI4Ate0S QO5JsC54Gfg4HV0W8xQnVgLEPgFXfLclbyIRkvApCkqw4W6NV2oPpY52e8vz7GvJnG3lBzwacgjS kHKGgG1rCLHNKvYGufeu2wzyk.28h0AFgZ2HaOjkTPX.uXxNLDwbs_fMR1YNAIA_PByvpOxf0gD5 q46PxvUB51jYzBwdRiCtfF3l6Se_Dd1_cWJQfqcyB8HM4mWVeX5ZPqaUcvZyZt9dafo66MrcbQ2R lHrh8kG4mcWcqZgFfa5eHLVRhFa1o161NyEeqMqtu2h94WVY6vkSlgQFKdg_BxmtK5Lu8raZ4bLE N074IGhGp2LFLuXcufQFJgoeNbotWmBVc9cQIJ7bFF23m1.Rt7mU5C2KPiqVGx7hIIxm3pFQVuut PFgo5zZT3s_15hw896cjoHO1O3K2d9bsk8yFU_9loBfSWkjONny8tkDF4vKxmJBrtsMdzjaAPEbu 7BdRtJhEkTwJq0lIzCKqXDSVfRTVmjCnuk5QxQIaM1iUG5lhC07XP0Y.eK.MM3yz.6O6ohqD0u.W 7QXw0Ei4mRcuUEVPh10PagtNqQ7134J5ej1ACxpkmaA9nlJo334FHv3qgl3BIiWOb_514xnNhYGA 2S.jKKjmyF3bmwAVZ9mUVz3tHRNPmUe58IRSrMAm46cdELSeQNCHcymMMxF1Cq9fKJy37peD6VGS AkDvBgeSYzkbDVMPBk2j1.SNMxXtgkx.FFfsTYFQoZ2JFwnoBD1KzDkfr.xyc_9Uu1PvouDM707y ol1pgkR_5_7.TnlhOsPaJwmWuM7OVKey9p.9hRGeBbhvsd.6Y4lFgSF8QMZFcfE6eij9Vn1fhBbv ktvyJIcV51FZKM3AsVAJqmMvlHMzro5MnjBHGKW_TinNjwjayZu1cq_eLG153ozfVlt6V.s6mek3 4cXE5FzBdY9y.ZkWjHK349eH1M8rL_.9X2BkfpA2NFR6wzHxsDvjG2xu63UQPXCw60potfAQH2_M RhHZwfx46KgILxFym52XYPYi49au6S0ilyT2zzY6KrumfPV4_PearJnayY3aRYZaiG6bc3qWwxv7 MfKwNsobLMKbJaSPo0yrsiMn.vOt5wI65zN9TaHeCRITnruixZxm6.2T.NSXSKYYxmXkfFzt3l2F W05qYcD9UfSo_XGIVr9y8EEpm8f7R1KZijfUucs5Nep3.Z_znDInZap17N4B4yY9gJEGNdprPpA2 ue4VOvR7Ld8ZldB6nV50MXfHsFjK5uutuMueg5kOIN4M1JERE9tH57ocgN5ct4YrHaVycv8MBhIU KCZECXUfc_QSuylnODrrjrbfW42rZaVTDa_00f.tEiQmDpuGl2rlMWEDrF59ztToOEUoYFuyWULZ ZjBpoBRhxsnzk9R3_zoa6yddtfQa8nbona2KnCJ7rCuNrQZBmRt4TDuq2Z2ZSlzBtlWIHpxegKjs mEs1DiiMQAn1NT78eqhfZ4Z15Sm6Mu.ZWzurISpc5FwEyFZY2np8yyj_3ktsVS6wZec0RKcfayUN itP.Ch2lTlvp8GrbH93fUCqEWDf70cqkJsWJ9.Uz98M2K1kE1_PcGX8DTKFxFBzKdhvw5Mx0xr9d 9fuMx7bDIrgkoJSjtjMw5mBBQ_mjO1x4E6Pb8fU6GjX98Gr4yK9iYYdx5.3t7AYiebKkC.m5tC6e tzKshkBcadxRcmu8o9fzXW.9.fmHlLQS5gyV48_dPjQL2oOQzr5i3HXREcWiFq3rsJz7mzIrEZaY - X-Sonic-MF: X-Sonic-ID: 6f81bd5e-a849-4c70-a847-38ee95c7c24c Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.gq1.yahoo.com with HTTP; Fri, 26 Jul 2024 06:05:42 +0000 Date: Fri, 26 Jul 2024 06:05:38 +0000 (UTC) From: "sivapostgres@yahoo.com" Reply-To: "sivapostgres@yahoo.com" To: Muhammad Ikram Cc: Postgresql General Group Message-ID: <40060236.111802.1721973938706@mail.yahoo.com> In-Reply-To: References: <431223779.107307.1721971823963.ref@mail.yahoo.com> <431223779.107307.1721971823963@mail.yahoo.com> Subject: Re: Slow performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_111801_1490837450.1721973938700" X-Mailer: WebService/1.1.22544 YMailNorrin Content-Length: 62150 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_111801_1490837450.1721973938700 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I've only one instance of PG in that server.=C2=A0 Means only one postgres= ql.conf for both databases. On Friday, 26 July, 2024 at 11:12:34 am IST, Muhammad Ikram wrote: =20 =20 Hi, Could you perform diff on postgresql.conf file to see whether values are sa= me for work_mem, shared_buffers, maintenance_work_mem and other related par= ameters? Regards,Ikram On Fri, Jul 26, 2024 at 10:31=E2=80=AFAM sivapostgres@yahoo.com wrote: Hello,Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two dat= abases with identical data. 1.=C2=A0 First DB:=C2=A0 client_db 2.=C2=A0 Second DB: client_test Took backup (pg_dump) of first database (client_db) and restored the databa= se as second database (client_test). Query: =C2=A0Select a.examname, a.registrationnumber, b.studentname, d.departmentn= ame, e.levelname,=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 a.subjectcode, c.subject= name, b.regular, a.semester, a.dummynumber, p.semester as curr_sem,=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 a.internalmark, a.externalmark, a.result, coale= sce((a.internalmark + a.externalmark),0) as total,=C2=A0=C2=A0 =C2=A0 =C2= =A0 =C2=A0 a.absent, a.malpractice, c.maxinternalmark, f.noofsemester, a.ex= amstudentstatus,=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.result =3D 'P= '=C2=A0 Then 'P' Else=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.result = =3D 'F' and a.malpractice =3D 'Y' and a.examstudentstatus is null Then 'M' = Else=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.result =3D 'F' and a.abse= nt =3D 'Y' and a.examstudentstatus =3D 'R' Then 'R.C' Else=C2=A0=C2=A0 =C2= =A0 =C2=A0 =C2=A0 Case When a.result =3D 'F' and a.absent =3D 'Y' and a.exa= mstudentstatus =3D 'S' Then 'S.L' Else=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Cas= e When a.result =3D 'F' and a.absent =3D 'Y' and a.examstudentstatus =3D 'F= ' Then 'N.P' Else=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.result =3D '= F' and a.absent =3D 'Y' and a.examstudentstatus =3D 'W' Then 'W.H' Else=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 'RA' End End End End End End as res,= =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Concat(RTrim(f.degreeawarded), ' ', RTrim= (d.departmentname))=C2=A0 as course,=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 a.rev= aluation, m.absent as int_abs, n.companyname, n.companydescription,=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 m.totalmark as int_mark, q.addressone, q.addres= stwo,=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.semester > f.noofsemeste= r Then 'PRIVATE'=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0When = a.semester <=3D f.noofsemester and a.semester in (1,2) Then 'I - Year'=C2= =A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0When a.semester <=3D f.n= oofsemester and a.semester in (3,4) Then 'II - Year'=C2=A0=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0When a.semester <=3D f.noofsemester and a.se= mester in (5,6) Then 'III - Year' End as studyr,=C2=A0=C2=A0 =C2=A0 =C2=A0 = =C2=A0c.subjectserialno, p.regulararrear=C2=A0=C2=A0From=C2=A0 cl_student_e= xam_subject=C2=A0 a=C2=A0=C2=A0Join=C2=A0 cl_student_name b=C2=A0=C2=A0On= =C2=A0 =C2=A0 b.companycode =3D a.companycode=C2=A0=C2=A0And=C2=A0 =C2=A0b.= registrationnumber =3D a.registrationnumber=C2=A0=C2=A0Join=C2=A0 cl_subjec= t c=C2=A0=C2=A0On=C2=A0 =C2=A0 c.companycode =3D a.companycode=C2=A0=C2=A0A= nd=C2=A0 =C2=A0c.subjectcode=C2=A0 =C2=A0 =3D a.subjectcode=C2=A0=C2=A0Join= =C2=A0 cl_department_header d=C2=A0=C2=A0On=C2=A0 =C2=A0d.departmentheaderp= k =3D b.departmentheaderfk=C2=A0=C2=A0Join=C2=A0 cl_level e=C2=A0=C2=A0On= =C2=A0 =C2=A0e.levelpk =3D b.Levelfk=C2=A0=C2=A0Join=C2=A0 cl_department_de= tail f=C2=A0=C2=A0On=C2=A0 =C2=A0f.departmentheaderfk =3D b.departmentheade= rfk=C2=A0=C2=A0And=C2=A0 =C2=A0f.levelfk =3D b.levelfk=C2=A0=C2=A0Left Oute= r Join cl_student_internal_mark m=C2=A0=C2=A0On=C2=A0 =C2=A0 m.companycode = =3D a.companycode=C2=A0=C2=A0And=C2=A0 =C2=A0m.registrationnumber =3D a.reg= istrationnumber=C2=A0=C2=A0And=C2=A0 =C2=A0m.subjectcode =3D a.subjectcode= =C2=A0=C2=A0And=C2=A0 =C2=A0m.departmentheaderfk =3D b.departmentheaderfk= =C2=A0=C2=A0And=C2=A0 =C2=A0m.levelfk =3D b.levelfk=C2=A0=C2=A0And=C2=A0 = =C2=A0m.Regular =3D b.Regular=C2=A0=C2=A0Join=C2=A0 co_company n=C2=A0=C2= =A0On=C2=A0 =C2=A0 n.companycode =3D a.companycode=C2=A0=C2=A0Join=C2=A0 cl= _student_semester_subject p=C2=A0=C2=A0On=C2=A0 =C2=A0 p.companycode =3D a.= companycode=C2=A0=C2=A0And=C2=A0 =C2=A0p.examheaderfk =3D a.examheaderfk=C2= =A0=C2=A0And=C2=A0 =C2=A0p.subjectcode=C2=A0 =3D a.subjectcode=C2=A0=C2=A0A= nd=C2=A0 =C2=A0p.registrationnumber =3D a.registrationnumber=C2=A0=C2=A0And= =C2=A0 =C2=A0p.semester =3D a.semester=C2=A0=C2=A0Join=C2=A0 co_company_bra= nch q=C2=A0=C2=A0On=C2=A0 =C2=A0 n.companycode =3D a.companycode=C2=A0=C2= =A0Where=C2=A0 a.companycode =3D '100'=C2=A0And=C2=A0 =C2=A0 a.examheaderfk= =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'=C2=A0And=C2=A0 =C2=A0(a.examstudent= status is null or a.examstudentstatus in ('R', 'S', 'W'))=C2=A0=C2=A0And=C2= =A0 b.departmentheaderfk in ('04DF8BD89D0844DD4D8AA151EFB28657')=C2=A0And= =C2=A0 =C2=A0 b.levelfk in ('37A9BEC2638844FFD5B1422D83E70EF3')=C2=A0And=C2= =A0 =C2=A0 b.status =3D 'A'=C2=A0=C2=A0Order By Concat(RTrim(f.degreeawarde= d), ' ', RTrim(d.departmentname)),=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = Case When a.semester > f.noofsemester Then 'PRIVATE'=C2=A0=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0When a.semester <=3D f.noofsemester a= nd a.semester in (1,2) Then 'I - Year'=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0When a.semester <=3D f.noofsemester and a.semester = in (3,4) Then 'II - Year'=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0When a.semester <=3D f.noofsemester and a.semester in (5,6) Th= en 'III - Year' End,=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a.registration= number, b.regular, p.semester desc, c.subjectserialno,=C2=A0=C2=A0 Case Whe= n c.subjectcategory =3D 'T' Then 1=C2=A0=C2=A0 =C2=A0 When c.subjectcategor= y =3D 'P' Then 2=C2=A0=C2=A0 =C2=A0 When c.subjectcategory =3D 'D' Then 3= =C2=A0=C2=A0 =C2=A0 When c.subjectcategory =3D 'V' Then 4=C2=A0=C2=A0 =C2= =A0 When c.subjectcategory =3D 'J' Then 5 End,=C2=A0=C2=A0 c.ancillary,=C2= =A0=C2=A0 Case When Substring(a.subjectcode, 6, 1) =3D 'C' Then 1=C2=A0=C2= =A0 =C2=A0 =C2=A0 =C2=A0When Substring(a.subjectcode, 6, 1) =3D 'S' Then 2= =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0When Substring(a.subjectcode, 6, 1) =3D 'A= ' Then 3=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0When Substring(a.subjectcode, 6, 1= ) =3D 'E' Then 4 Else 5 End,=C2=A0=C2=A0 =C2=A0 =C2=A0a.subjectcode=C2=A0 Explain Analyze of DB 1 (client_db) :"Sort=C2=A0 (cost=3D2862.35..2862.36 r= ows=3D1 width=3D1088) (actual time=3D451671.464..451671.495 rows=3D326 loop= s=3D1)""=C2=A0 Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ', rtrim= ((d.departmentname)::text))), (CASE WHEN (a.semester > f.noofsemester) THEN= 'PRIVATE'::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semester =3D= ANY ('{1,2}'::integer[]))) THEN 'I - Year'::text WHEN ((a.semester <=3D f.= noofsemester) AND (a.semester =3D ANY ('{3,4}'::integer[]))) THEN 'II - Yea= r'::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semester =3D ANY ('{= 5,6}'::integer[]))) THEN 'III - Year'::text ELSE NULL::text END), a.registr= ationnumber, b.regular, a.semester DESC, c.subjectserialno, (CASE WHEN (c.s= ubjectcategory =3D 'T'::bpchar) THEN 1 WHEN (c.subjectcategory =3D 'P'::bpc= har) THEN 2 WHEN (c.subjectcategory =3D 'D'::bpchar) THEN 3 WHEN (c.subject= category =3D 'V'::bpchar) THEN 4 WHEN (c.subjectcategory =3D 'J'::bpchar) T= HEN 5 ELSE NULL::integer END), c.ancillary, (CASE WHEN (""substring""((a.su= bjectcode)::text, 6, 1) =3D 'C'::text) THEN 1 WHEN (""substring""((a.subjec= tcode)::text, 6, 1) =3D 'S'::text) THEN 2 WHEN (""substring""((a.subjectcod= e)::text, 6, 1) =3D 'A'::text) THEN 3 WHEN (""substring""((a.subjectcode)::= text, 6, 1) =3D 'E'::text) THEN 4 ELSE 5 END), a.subjectcode""=C2=A0 Sort M= ethod: quicksort=C2=A0 Memory: 193kB""=C2=A0 ->=C2=A0 Nested Loop=C2=A0 (co= st=3D2.36..2862.34 rows=3D1 width=3D1088) (actual time=3D57829.857..451662.= 727 rows=3D326 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop= =C2=A0 (cost=3D2.36..2861.23 rows=3D1 width=3D686) (actual time=3D57829.829= ..451658.085 rows=3D326 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Join Filter: (((b.registrationnumber)::text =3D (p.registrationn= umber)::text) AND ((c.subjectcode)::text =3D (p.subjectcode)::text) AND (a.= semester =3D p.semester))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= Rows Removed by Join Filter: 13614738""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 ->=C2=A0 Index Scan using ""cl_student_semester_subject_IX3""= on cl_student_semester_subject p=C2=A0 (cost=3D0.55..8.57 rows=3D1 width= =3D60) (actual time=3D0.033..55.702 rows=3D41764 loops=3D1)""=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (((comp= anycode)::text =3D '100'::text) AND ((examheaderfk)::text =3D 'BA80952CFF8F= 4E1C3F9F44B62ED9BF37'::text))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 ->=C2=A0 Nested Loop Left Join=C2=A0 (cost=3D1.81..2848.39 rows=3D24= 4 width=3D735) (actual time=3D0.068..10.768 rows=3D326 loops=3D41764)""=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Join Fil= ter: (((m.departmentheaderfk)::text =3D (b.departmentheaderfk)::text) AND (= (m.levelfk)::text =3D (b.levelfk)::text) AND (m.regular =3D b.regular))""= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2= =A0 Nested Loop=C2=A0 (cost=3D1.39..2135.32 rows=3D244 width=3D795) (actual= time=3D0.053..6.723 rows=3D326 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Ne= sted Loop=C2=A0 (cost=3D1.11..2058.12 rows=3D244 width=3D746) (actual time= =3D0.045..4.299 rows=3D326 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D0.68..312.80 rows=3D172 width=3D6= 99) (actual time=3D0.029..0.338 rows=3D313 loops=3D41764)""=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on co_compa= ny n=C2=A0 (cost=3D0.00..1.01 rows=3D1 width=3D394) (actual time=3D0.001..0= .001 rows=3D1 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((companycode)::text =3D '100'::= text)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0= Nested Loop=C2=A0 (cost=3D0.68..310.07 rows=3D172 width=3D305) (actual tim= e=3D0.025..0.282 rows=3D313 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0= (cost=3D0.27..10.70 rows=3D1 width=3D438) (actual time=3D0.013..0.019 rows= =3D1 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0= (cost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.009..0.013 rows= =3D1 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2= =A0 Seq Scan on cl_department_header d=C2=A0 (cost=3D0.00..1.30 rows=3D1 wi= dth=3D200) (actual time=3D0.002..0.004 rows=3D1 loops=3D41764)""=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((depar= tmentheaderpk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text)""=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed= by Filter: 23""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Ind= ex Scan using cl_department_detail_ix1 on cl_department_detail f=C2=A0 (cos= t=3D0.27..8.29 rows=3D1 width=3D78) (actual time=3D0.007..0.007 rows=3D1 lo= ops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Index Cond: (((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D= 8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83= E70EF3'::text))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on cl_level= e=C2=A0 (cost=3D0.00..1.09 rows=3D1 width=3D160) (actual time=3D0.001..0.0= 03 rows=3D1 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Filter: ((levelpk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text)"= "=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Filter: 6"= "=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 ->=C2=A0 Index Scan using cl_student_name_ix4 on cl_student_name b= =C2=A0 (cost=3D0.41..297.65 rows=3D172 width=3D97) (actual time=3D0.011..0.= 165 rows=3D313 loops=3D41764)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (((= companycode)::text =3D '100'::text) AND ((departmentheaderfk)::text =3D '04= DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC263= 8844FFD5B1422D83E70EF3'::text) AND (status =3D 'A'::bpchar))""=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using ""cl_student_exam_subjec= t_IX1"" on cl_student_exam_subject a=C2=A0 (cost=3D0.42..10.13 rows=3D2 wid= th=3D89) (actual time=3D0.012..0.012 rows=3D1 loops=3D13072132)""=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (((companycode= )::text =3D '100'::text) AND ((examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9= F44B62ED9BF37'::text) AND ((registrationnumber)::text =3D (b.registrationnu= mber)::text))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 F= ilter: ((examstudentstatus IS NULL) OR (examstudentstatus =3D ANY ('{R,S,W}= '::bpchar[])))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using cl_subject_ix3 on= cl_subject c=C2=A0 (cost=3D0.28..0.32 rows=3D1 width=3D53) (actual time=3D= 0.007..0.007 rows=3D1 loops=3D13615064)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Index Cond: (((companycode)::text =3D '100'::text) AND ((subjectcode)::= text =3D (a.subjectcode)::text))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using ""cl_student_interna= l_mark_IX"" on cl_student_internal_mark m=C2=A0 (cost=3D0.42..2.90 rows=3D1= width=3D97) (actual time=3D0.011..0.011 rows=3D1 loops=3D13615064)""=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Index Cond: (((companycode)::text =3D (a.companycode)::text) AND= ((companycode)::text =3D '100'::text) AND ((subjectcode)::text =3D (a.subj= ectcode)::text) AND ((registrationnumber)::text =3D (a.registrationnumber):= :text))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Filter: (((departmentheaderfk)::text =3D '04DF8BD8= 9D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC2638844FF= D5B1422D83E70EF3'::text))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on= co_company_branch q=C2=A0 (cost=3D0.00..1.01 rows=3D1 width=3D276) (actual= time=3D0.001..0.001 rows=3D1 loops=3D326)""Planning Time: 15.936 ms""Execu= tion Time: 451672.059 ms" Explain Analyze of Second DB (client_test)"Sort=C2=A0 (cost=3D3454.91..3454= .92 rows=3D1 width=3D1088) (actual time=3D19.120..19.137 rows=3D326 loops= =3D1)""=C2=A0 Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ', rtrim(= (d.departmentname)::text))), (CASE WHEN (a.semester > f.noofsemester) THEN = 'PRIVATE'::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semester =3D = ANY ('{1,2}'::integer[]))) THEN 'I - Year'::text WHEN ((a.semester <=3D f.n= oofsemester) AND (a.semester =3D ANY ('{3,4}'::integer[]))) THEN 'II - Year= '::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semester =3D ANY ('{5= ,6}'::integer[]))) THEN 'III - Year'::text ELSE NULL::text END), a.registra= tionnumber, b.regular, a.semester DESC, c.subjectserialno, (CASE WHEN (c.su= bjectcategory =3D 'T'::bpchar) THEN 1 WHEN (c.subjectcategory =3D 'P'::bpch= ar) THEN 2 WHEN (c.subjectcategory =3D 'D'::bpchar) THEN 3 WHEN (c.subjectc= ategory =3D 'V'::bpchar) THEN 4 WHEN (c.subjectcategory =3D 'J'::bpchar) TH= EN 5 ELSE NULL::integer END), c.ancillary, (CASE WHEN (""substring""((a.sub= jectcode)::text, 6, 1) =3D 'C'::text) THEN 1 WHEN (""substring""((a.subject= code)::text, 6, 1) =3D 'S'::text) THEN 2 WHEN (""substring""((a.subjectcode= )::text, 6, 1) =3D 'A'::text) THEN 3 WHEN (""substring""((a.subjectcode)::t= ext, 6, 1) =3D 'E'::text) THEN 4 ELSE 5 END), a.subjectcode""=C2=A0 Sort Me= thod: quicksort=C2=A0 Memory: 193kB""=C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cos= t=3D2.23..3454.90 rows=3D1 width=3D1088) (actual time=3D0.319..14.984 rows= =3D326 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (= cost=3D2.23..3453.78 rows=3D1 width=3D686) (actual time=3D0.298..13.691 row= s=3D326 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Join F= ilter: (((b.registrationnumber)::text =3D (p.registrationnumber)::text) AND= ((c.subjectcode)::text =3D (p.subjectcode)::text))""=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop Left Join=C2=A0 (cost=3D1.= 81..2739.81 rows=3D230 width=3D735) (actual time=3D0.259..9.970 rows=3D326 = loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Join Filter: (((m.departmentheaderfk)::text =3D (b.departmentheaderf= k)::text) AND ((m.levelfk)::text =3D (b.levelfk)::text) AND (m.regular =3D = b.regular))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D1.39..2060.47 rows=3D230 width= =3D795) (actual time=3D0.233..6.232 rows=3D326 loops=3D1)""=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D1.11..1987.62 rows=3D230 width=3D746= ) (actual time=3D0.211..3.955 rows=3D326 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D0.68..299.78 rows=3D16= 6 width=3D699) (actual time=3D0.118..0.393 rows=3D313 loops=3D1)""=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on c= o_company n=C2=A0 (cost=3D0.00..1.01 rows=3D1 width=3D394) (actual time=3D0= .026..0.027 rows=3D1 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((companycode)::text =3D '10= 0'::text)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2= =A0 Nested Loop=C2=A0 (cost=3D0.68..297.11 rows=3D166 width=3D305) (actual = time=3D0.091..0.316 rows=3D313 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0= (cost=3D0.27..10.70 rows=3D1 width=3D438) (actual time=3D0.041..0.048 rows= =3D1 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (c= ost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.034..0.038 rows=3D1= loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Sc= an on cl_department_header d=C2=A0 (cost=3D0.00..1.30 rows=3D1 width=3D200)= (actual time=3D0.009..0.011 rows=3D1 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((departmentheaderpk)= ::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text)""=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Filter: 23= ""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using = cl_department_detail_ix1 on cl_department_detail f=C2=A0 (cost=3D0.27..8.29= rows=3D1 width=3D78) (actual time=3D0.024..0.024 rows=3D1 loops=3D1)""=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Co= nd: (((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::te= xt) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))""= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Seq Scan on cl_level e=C2=A0 (cost=3D0= .00..1.09 rows=3D1 width=3D160) (actual time=3D0.006..0.008 rows=3D1 loops= =3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((levelpk):= :text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text)""=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Filter: 6""=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index = Scan using cl_student_name_ix4 on cl_student_name b=C2=A0 (cost=3D0.41..284= .75 rows=3D166 width=3D97) (actual time=3D0.049..0.184 rows=3D313 loops=3D1= )""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (((companycode)::text =3D '100'= ::text) AND ((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB286= 57'::text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::tex= t) AND (status =3D 'A'::bpchar))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->= =C2=A0 Index Scan using ""cl_student_exam_subject_IX1"" on cl_student_exam_= subject a=C2=A0 (cost=3D0.42..10.15 rows=3D2 width=3D89) (actual time=3D0.0= 10..0.011 rows=3D1 loops=3D313)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 Index Cond: (((companycode)::text =3D '100'::text) AND ((= examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND ((reg= istrationnumber)::text =3D (b.registrationnumber)::text))""=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((examstudentstatus I= S NULL) OR (examstudentstatus =3D ANY ('{R,S,W}'::bpchar[])))""=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 ->=C2=A0 Index Scan using cl_subject_ix3 on cl_subject c=C2=A0 (cost= =3D0.28..0.32 rows=3D1 width=3D53) (actual time=3D0.006..0.006 rows=3D1 loo= ps=3D326)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (((companycode= )::text =3D '100'::text) AND ((subjectcode)::text =3D (a.subjectcode)::text= ))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -= >=C2=A0 Index Scan using ""cl_student_internal_mark_IX"" on cl_student_inte= rnal_mark m=C2=A0 (cost=3D0.42..2.94 rows=3D1 width=3D97) (actual time=3D0.= 010..0.010 rows=3D1 loops=3D326)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (((companycod= e)::text =3D (a.companycode)::text) AND ((companycode)::text =3D '100'::tex= t) AND ((subjectcode)::text =3D (a.subjectcode)::text) AND ((registrationnu= mber)::text =3D (a.registrationnumber)::text))""=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (((= departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND= ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))""=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using ""cl_st= udent_semester_subject_IX1"" on cl_student_semester_subject p=C2=A0 (cost= =3D0.42..3.09 rows=3D1 width=3D60) (actual time=3D0.010..0.010 rows=3D1 loo= ps=3D326)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Index Cond: (((companycode)::text =3D '100'::text) AND ((subjectcode= )::text =3D (a.subjectcode)::text) AND ((registrationnumber)::text =3D (a.r= egistrationnumber)::text) AND (semester =3D a.semester))""=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: ((examheaderfk= )::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text)""=C2=A0 =C2=A0 =C2=A0= =C2=A0 ->=C2=A0 Seq Scan on co_company_branch q=C2=A0 (cost=3D0.00..1.01 r= ows=3D1 width=3D276) (actual time=3D0.000..0.000 rows=3D1 loops=3D326)""Pla= nning Time: 15.581 ms""Execution Time: 19.546 ms" The query when run against DB1 takes around 7 min 32 seconds.The same query= when run against DB2 takes around 124 msec.=C2=A0=C2=A0 Same computer, same PG cluster, same query.Why it takes so much time when r= un against DB1 (client_db)? Already executed vacuum against client_db database.=C2=A0=C2=A0 Any help is really appreciated. Happiness Always BKR Sivaprakash --=20 Muhammad Ikram =20 ------=_Part_111801_1490837450.1721973938700 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I've only one instance of PG= in that server.  Means only one postgresql.conf for both databases.


=20
=20
On Friday, 26 July, 2024 at 11:12:34 am IST, Muhamm= ad Ikram <mmikram@gmail.com> wrote:


=20 =20
H= i,

Could you perform diff on postgr= esql.conf file to see whether values are same for work_mem, shared_buffers,= maintenance_work_mem and other related parameters?

Regards,
Ikram

On Fri, Jul 26, 2024 at 10:31=E2=80=AFAM s= ivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:
Hello,
Using= PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two databases with= identical data.

1.  First DB:  client_db
2.  Second = DB: client_test

Took backup (pg_dump) of first database (client_db) and restored the d= atabase as second database (client_test).

Query:
 S= elect a.examname, a.registrationnumber, b.studentname, d.departmentname, e.= levelname, 
        a.subjectcode, c.sub= jectname, b.regular, a.semester, a.dummynumber, p.semester as curr_sem,&nbs= p;
        a.internalmark, a.externalmark, a.= result, coalesce((a.internalmark + a.externalmark),0) as total, 
=
        a.absent, a.malpractice, c.maxinternalmark= , f.noofsemester, a.examstudentstatus, 
     =   Case When a.result =3D 'P'  Then 'P' Else 
&nbs= p;       Case When a.result =3D 'F' and a.malpractice =3D 'Y= ' and a.examstudentstatus is null Then 'M' Else 
  &nbs= p;     Case When a.result =3D 'F' and a.absent =3D 'Y' and a.exam= studentstatus =3D 'R' Then 'R.C' Else 
      =   Case When a.result =3D 'F' and a.absent =3D 'Y' and a.examstudentsta= tus =3D 'S' Then 'S.L' Else 
        Cas= e When a.result =3D 'F' and a.absent =3D 'Y' and a.examstudentstatus =3D 'F= ' Then 'N.P' Else 
        Case When a.r= esult =3D 'F' and a.absent =3D 'Y' and a.examstudentstatus =3D 'W' Then 'W.= H' Else 
              &n= bsp;                     =                 'RA' End End End En= d End End as res, 
        Concat(RTrim(= f.degreeawarded), ' ', RTrim(d.departmentname))  as course, 
        a.revaluation, m.absent as int_abs, n.com= panyname, n.companydescription, 
       = m.totalmark as int_mark, q.addressone, q.addresstwo, 
 = ;       Case When a.semester > f.noofsemester Then 'PRIVA= TE' 
             When a.= semester <=3D f.noofsemester and a.semester in (1,2) Then 'I - Year'&nbs= p;
             When a.semeste= r <=3D f.noofsemester and a.semester in (3,4) Then 'II - Year' 
             When a.semester <= =3D f.noofsemester and a.semester in (5,6) Then 'III - Year' End as studyr,=  
       c.subjectserialno, p.regulararr= ear 
 From  cl_student_exam_subject  a <= /div>
 Join  cl_student_name b 
 On =   b.companycode =3D a.companycode 
 And  &nb= sp;b.registrationnumber =3D a.registrationnumber 
 Join=   cl_subject c 
 On    c.companycode =3D= a.companycode 
 And   c.subjectcode  &n= bsp; =3D a.subjectcode 
 Join  cl_department_heade= r d 
 On   d.departmentheaderpk =3D b.departm= entheaderfk 
 Join  cl_level e 
&nb= sp;On   e.levelpk =3D b.Levelfk 
 Join  = cl_department_detail f 
 On   f.departmenthea= derfk =3D b.departmentheaderfk 
 And   f.leve= lfk =3D b.levelfk 
 Left Outer Join cl_student_internal= _mark m 
 On    m.companycode =3D a.companyco= de 
 And   m.registrationnumber =3D a.registr= ationnumber 
 And   m.subjectcode =3D a.subje= ctcode 
 And   m.departmentheaderfk =3D b.dep= artmentheaderfk 
 And   m.levelfk =3D b.level= fk 
 And   m.Regular =3D b.Regular 
 Join  co_company n 
 On    n= .companycode =3D a.companycode 
 Join  cl_student_= semester_subject p 
 On    p.companycode =3D = a.companycode 
 And   p.examheaderfk =3D a.ex= amheaderfk 
 And   p.subjectcode  =3D a.= subjectcode 
 And   p.registrationnumber =3D = a.registrationnumber 
 And   p.semester =3D a= .semester 
 Join  co_company_branch q 
<= div> On    n.companycode =3D a.companycode 
&= nbsp;Where  a.companycode =3D '100'
 And    a= .examheaderfk =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'
 And&nb= sp;  (a.examstudentstatus is null or a.examstudentstatus in ('R', 'S',= 'W')) 
 And  b.departmentheaderfk in ('04DF8BD89D= 0844DD4D8AA151EFB28657')
 And    b.levelfk in ('37= A9BEC2638844FFD5B1422D83E70EF3')
 And    b.status = =3D 'A' 
 Order By Concat(RTrim(f.degreeawarded), ' ', = RTrim(d.departmentname)), 
         = ; Case When a.semester > f.noofsemester Then 'PRIVATE' 
&= nbsp;              When a.semester <= =3D f.noofsemester and a.semester in (1,2) Then 'I - Year' 
=                When a.semester <= =3D f.noofsemester and a.semester in (3,4) Then 'II - Year' 
               When a.semester <= ;=3D f.noofsemester and a.semester in (5,6) Then 'III - Year' End, 
          a.registrationnumber, b.regular,= p.semester desc, c.subjectserialno, 
 =09=09=09<= /span>Case When c.subjectcategory =3D 'T' Then 1 
 =09=09=09=09  When c.subjectcategory =3D 'P' Then 2 
 =09=09=09=09  When c.subjectcategory =3D 'D'= Then 3 
 =09=09=09=09  When c.subjec= tcategory =3D 'V' Then 4 
 =09=09=09=09&nb= sp; When c.subjectcategory =3D 'J' Then 5 End, 
 = =09=09=09c.ancillary, 
 =09=09=09Ca= se When Substring(a.subjectcode, 6, 1) =3D 'C' Then 1 
 = ;=09=09=09     When Substring(a.subjectcode, 6,= 1) =3D 'S' Then 2 
 =09=09=09   = ;  When Substring(a.subjectcode, 6, 1) =3D 'A' Then 3 
=  =09=09=09     When Substring(a.subjectcod= e, 6, 1) =3D 'E' Then 4 Else 5 End, 
 =09=09   a.subjectcode 


Explain Analyze of DB 1 (client_= db) :
"Sort  (cost=3D2862.35..2862.36 = rows=3D1 width=3D1088) (actual time=3D451671.464..451671.495 rows=3D326 loo= ps=3D1)"
"  Sort Key: (concat(rtrim((f.degreeawarded)::text)= , ' ', rtrim((d.departmentname)::text))), (CASE WHEN (a.semester > f.noo= fsemester) THEN 'PRIVATE'::text WHEN ((a.semester <=3D f.noofsemester) A= ND (a.semester =3D ANY ('{1,2}'::integer[]))) THEN 'I - Year'::text WHEN ((= a.semester <=3D f.noofsemester) AND (a.semester =3D ANY ('{3,4}'::intege= r[]))) THEN 'II - Year'::text WHEN ((a.semester <=3D f.noofsemester) AND= (a.semester =3D ANY ('{5,6}'::integer[]))) THEN 'III - Year'::text ELSE NU= LL::text END), a.registrationnumber, b.regular, a.semester DESC, c.subjects= erialno, (CASE WHEN (c.subjectcategory =3D 'T'::bpchar) THEN 1 WHEN (c.subj= ectcategory =3D 'P'::bpchar) THEN 2 WHEN (c.subjectcategory =3D 'D'::bpchar= ) THEN 3 WHEN (c.subjectcategory =3D 'V'::bpchar) THEN 4 WHEN (c.subjectcat= egory =3D 'J'::bpchar) THEN 5 ELSE NULL::integer END), c.ancillary, (CASE W= HEN (""substring""((a.subjectcode)::text, 6, 1) =3D 'C'::text) THEN 1 WHEN = (""substring""((a.subjectcode)::text, 6, 1) =3D 'S'::text) THEN 2 WHEN (""s= ubstring""((a.subjectcode)::text, 6, 1) =3D 'A'::text) THEN 3 WHEN (""subst= ring""((a.subjectcode)::text, 6, 1) =3D 'E'::text) THEN 4 ELSE 5 END), a.su= bjectcode"
"  Sort Method: quicksort  Memory: 193kB"
"  ->  Nested Loop  (cost=3D2.36..2862.34 rows=3D= 1 width=3D1088) (actual time=3D57829.857..451662.727 rows=3D326 loops=3D1)"=
"        ->  Nested Loop  (cost= =3D2.36..2861.23 rows=3D1 width=3D686) (actual time=3D57829.829..451658.085= rows=3D326 loops=3D1)"
"           = ;   Join Filter: (((b.registrationnumber)::text =3D (p.registrationnum= ber)::text) AND ((c.subjectcode)::text =3D (p.subjectcode)::text) AND (a.se= mester =3D p.semester))"
"          &nbs= p;   Rows Removed by Join Filter: 13614738"
"    &= nbsp;         ->  Index Scan using ""cl_student= _semester_subject_IX3"" on cl_student_semester_subject p  (cost=3D0.55= ..8.57 rows=3D1 width=3D60) (actual time=3D0.033..55.702 rows=3D41764 loops= =3D1)"
"                &= nbsp;   Index Cond: (((companycode)::text =3D '100'::text) AND ((examh= eaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text))"
"&= nbsp;             ->  Nested Loop Lef= t Join  (cost=3D1.81..2848.39 rows=3D244 width=3D735) (actual time=3D0= .068..10.768 rows=3D326 loops=3D41764)"
"      &nb= sp;             Join Filter: (((m.departmenth= eaderfk)::text =3D (b.departmentheaderfk)::text) AND ((m.levelfk)::text =3D= (b.levelfk)::text) AND (m.regular =3D b.regular))"
"   = ;                 ->  Neste= d Loop  (cost=3D1.39..2135.32 rows=3D244 width=3D795) (actual time=3D0= .053..6.723 rows=3D326 loops=3D41764)"
"      &nbs= p;                   ->&nbs= p; Nested Loop  (cost=3D1.11..2058.12 rows=3D244 width=3D746) (actual = time=3D0.045..4.299 rows=3D326 loops=3D41764)"
"    &nb= sp;                     &= nbsp;     ->  Nested Loop  (cost=3D0.68..312.80 rows= =3D172 width=3D699) (actual time=3D0.029..0.338 rows=3D313 loops=3D41764)"<= /div>
"                  &= nbsp;                   ->&= nbsp; Seq Scan on co_company n  (cost=3D0.00..1.01 rows=3D1 width=3D39= 4) (actual time=3D0.001..0.001 rows=3D1 loops=3D41764)"
"  &= nbsp;                    =                     Filt= er: ((companycode)::text =3D '100'::text)"
"      =                      = ;           ->  Nested Loop  (cost=3D= 0.68..310.07 rows=3D172 width=3D305) (actual time=3D0.025..0.282 rows=3D313= loops=3D41764)"
"             = ;                     &nb= sp;         ->  Nested Loop  (cost=3D0.27.= .10.70 rows=3D1 width=3D438) (actual time=3D0.013..0.019 rows=3D1 loops=3D4= 1764)"
"                &= nbsp;                    =             ->  Nested Loop  (c= ost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.009..0.013 rows=3D1= loops=3D41764)"
"             = ;                     &nb= sp;                     -= >  Seq Scan on cl_department_header d  (cost=3D0.00..1.30 rows= =3D1 width=3D200) (actual time=3D0.002..0.004 rows=3D1 loops=3D41764)"
"                   = ;                     &nb= sp;                     F= ilter: ((departmentheaderpk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::= text)"
"                &= nbsp;                    =                     &nbs= p;   Rows Removed by Filter: 23"
"       = ;                     &nb= sp;                     &= nbsp;     ->  Index Scan using cl_department_detail_ix1 o= n cl_department_detail f  (cost=3D0.27..8.29 rows=3D1 width=3D78) (act= ual time=3D0.007..0.007 rows=3D1 loops=3D41764)"
"    &= nbsp;                    =                     &nbs= p;               Index Cond: (((departme= ntheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((level= fk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))"
" =                     &nbs= p;                     &n= bsp;     ->  Seq Scan on cl_level e  (cost=3D0.00..1= .09 rows=3D1 width=3D160) (actual time=3D0.001..0.003 rows=3D1 loops=3D4176= 4)"
"                &nbs= p;                     &n= bsp;                 Filter: ((leve= lpk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text)"
" =                     &nbs= p;                     &n= bsp;           Rows Removed by Filter: 6"
"                    &n= bsp;                     =   ->  Index Scan using cl_student_name_ix4 on cl_student_name = b  (cost=3D0.41..297.65 rows=3D172 width=3D97) (actual time=3D0.011..0= .165 rows=3D313 loops=3D41764)"
"        &nbs= p;                     &n= bsp;                   Index C= ond: (((companycode)::text =3D '100'::text) AND ((departmentheaderfk)::text= =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37= A9BEC2638844FFD5B1422D83E70EF3'::text) AND (status =3D 'A'::bpchar))"
=
"                   =             ->  Index Scan using ""c= l_student_exam_subject_IX1"" on cl_student_exam_subject a  (cost=3D0.4= 2..10.13 rows=3D2 width=3D89) (actual time=3D0.012..0.012 rows=3D1 loops=3D= 13072132)"
"              &nbs= p;                     &n= bsp; Index Cond: (((companycode)::text =3D '100'::text) AND ((examheaderfk)= ::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND ((registrationnumb= er)::text =3D (b.registrationnumber)::text))"
"    &nbs= p;                     &n= bsp;           Filter: ((examstudentstatus IS NULL= ) OR (examstudentstatus =3D ANY ('{R,S,W}'::bpchar[])))"
"  =                      = ;   ->  Index Scan using cl_subject_ix3 on cl_subject c  = (cost=3D0.28..0.32 rows=3D1 width=3D53) (actual time=3D0.007..0.007 rows=3D= 1 loops=3D13615064)"
"            &= nbsp;                   Index = Cond: (((companycode)::text =3D '100'::text) AND ((subjectcode)::text =3D (= a.subjectcode)::text))"
"           = ;         ->  Index Scan using ""cl_student_int= ernal_mark_IX"" on cl_student_internal_mark m  (cost=3D0.42..2.90 rows= =3D1 width=3D97) (actual time=3D0.011..0.011 rows=3D1 loops=3D13615064)"
"                  &nb= sp;       Index Cond: (((companycode)::text =3D (a.companyco= de)::text) AND ((companycode)::text =3D '100'::text) AND ((subjectcode)::te= xt =3D (a.subjectcode)::text) AND ((registrationnumber)::text =3D (a.regist= rationnumber)::text))"
"           =               Filter: (((departmenthead= erfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::t= ext =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))"
"   = ;     ->  Seq Scan on co_company_branch q  (cost=3D0= .00..1.01 rows=3D1 width=3D276) (actual time=3D0.001..0.001 rows=3D1 loops= =3D326)"
"Planning Time: 15.936 ms"
"Execution Time: 45= 1672.059 ms"



= Explain Analyze of Second DB (client_test)
= "Sort  (cost=3D3454.91..3454.92 rows=3D1 width=3D1088) (actual time=3D= 19.120..19.137 rows=3D326 loops=3D1)"
"  Sort Key: (concat(r= trim((f.degreeawarded)::text), ' ', rtrim((d.departmentname)::text))), (CAS= E WHEN (a.semester > f.noofsemester) THEN 'PRIVATE'::text WHEN ((a.semes= ter <=3D f.noofsemester) AND (a.semester =3D ANY ('{1,2}'::integer[]))) = THEN 'I - Year'::text WHEN ((a.semester <=3D f.noofsemester) AND (a.seme= ster =3D ANY ('{3,4}'::integer[]))) THEN 'II - Year'::text WHEN ((a.semeste= r <=3D f.noofsemester) AND (a.semester =3D ANY ('{5,6}'::integer[]))) TH= EN 'III - Year'::text ELSE NULL::text END), a.registrationnumber, b.regular= , a.semester DESC, c.subjectserialno, (CASE WHEN (c.subjectcategory =3D 'T'= ::bpchar) THEN 1 WHEN (c.subjectcategory =3D 'P'::bpchar) THEN 2 WHEN (c.su= bjectcategory =3D 'D'::bpchar) THEN 3 WHEN (c.subjectcategory =3D 'V'::bpch= ar) THEN 4 WHEN (c.subjectcategory =3D 'J'::bpchar) THEN 5 ELSE NULL::integ= er END), c.ancillary, (CASE WHEN (""substring""((a.subjectcode)::text, 6, 1= ) =3D 'C'::text) THEN 1 WHEN (""substring""((a.subjectcode)::text, 6, 1) = =3D 'S'::text) THEN 2 WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D = 'A'::text) THEN 3 WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D 'E':= :text) THEN 4 ELSE 5 END), a.subjectcode"
"  Sort Method: qu= icksort  Memory: 193kB"
"  ->  Nested Loop = ; (cost=3D2.23..3454.90 rows=3D1 width=3D1088) (actual time=3D0.319..14.984= rows=3D326 loops=3D1)"
"        ->  = Nested Loop  (cost=3D2.23..3453.78 rows=3D1 width=3D686) (actual time= =3D0.298..13.691 rows=3D326 loops=3D1)"
"      &nb= sp;       Join Filter: (((b.registrationnumber)::text =3D (p= .registrationnumber)::text) AND ((c.subjectcode)::text =3D (p.subjectcode):= :text))"
"              ->&= nbsp; Nested Loop Left Join  (cost=3D1.81..2739.81 rows=3D230 width=3D= 735) (actual time=3D0.259..9.970 rows=3D326 loops=3D1)"
"  &= nbsp;                 Join Filter: = (((m.departmentheaderfk)::text =3D (b.departmentheaderfk)::text) AND ((m.le= velfk)::text =3D (b.levelfk)::text) AND (m.regular =3D b.regular))"
"                    -= >  Nested Loop  (cost=3D1.39..2060.47 rows=3D230 width=3D795) = (actual time=3D0.233..6.232 rows=3D326 loops=3D1)"
"   =                     &nbs= p; ->  Nested Loop  (cost=3D1.11..1987.62 rows=3D230 width=3D7= 46) (actual time=3D0.211..3.955 rows=3D326 loops=3D1)"
"  &n= bsp;                     =         ->  Nested Loop  (cost=3D0.68..299= .78 rows=3D166 width=3D699) (actual time=3D0.118..0.393 rows=3D313 loops=3D= 1)"
"                &nbs= p;                     -&= gt;  Seq Scan on co_company n  (cost=3D0.00..1.01 rows=3D1 width= =3D394) (actual time=3D0.026..0.027 rows=3D1 loops=3D1)"
"  =                      = ;                     Fil= ter: ((companycode)::text =3D '100'::text)"
"     =                     &nbs= p;           ->  Nested Loop  (cost= =3D0.68..297.11 rows=3D166 width=3D305) (actual time=3D0.091..0.316 rows=3D= 313 loops=3D1)"
"             =                     &nbs= p;         ->  Nested Loop  (cost=3D0.27..= 10.70 rows=3D1 width=3D438) (actual time=3D0.041..0.048 rows=3D1 loops=3D1)= "
"                 =                     &nbs= p;           ->  Nested Loop  (cost= =3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.034..0.038 rows=3D1 lo= ops=3D1)"
"               = ;                     &nb= sp;                   ->&nb= sp; Seq Scan on cl_department_header d  (cost=3D0.00..1.30 rows=3D1 wi= dth=3D200) (actual time=3D0.009..0.011 rows=3D1 loops=3D1)"
"&nbs= p;                     &n= bsp;                     =                   Filter: ((de= partmentheaderpk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text)"
"                   = ;                     &nb= sp;                     R= ows Removed by Filter: 23"
"          &n= bsp;                     =                      = ;   ->  Index Scan using cl_department_detail_ix1 on cl_depart= ment_detail f  (cost=3D0.27..8.29 rows=3D1 width=3D78) (actual time=3D= 0.024..0.024 rows=3D1 loops=3D1)"
"        &n= bsp;                     =                      = ;           Index Cond: (((departmentheaderfk)::te= xt =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '= 37A9BEC2638844FFD5B1422D83E70EF3'::text))"
"      =                      = ;                     &nb= sp; ->  Seq Scan on cl_level e  (cost=3D0.00..1.09 rows=3D1 wi= dth=3D160) (actual time=3D0.006..0.008 rows=3D1 loops=3D1)"
"&nbs= p;                     &n= bsp;                     =             Filter: ((levelpk)::text =3D '37A= 9BEC2638844FFD5B1422D83E70EF3'::text)"
"      &nbs= p;                     &n= bsp;                     =       Rows Removed by Filter: 6"
"    &n= bsp;                     =                   ->  = Index Scan using cl_student_name_ix4 on cl_student_name b  (cost=3D0.4= 1..284.75 rows=3D166 width=3D97) (actual time=3D0.049..0.184 rows=3D313 loo= ps=3D1)"
"               =                     &nbs= p;             Index Cond: (((companycode)::t= ext =3D '100'::text) AND ((departmentheaderfk)::text =3D '04DF8BD89D0844DD4= D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D8= 3E70EF3'::text) AND (status =3D 'A'::bpchar))"
"    &nb= sp;                     &= nbsp;     ->  Index Scan using ""cl_student_exam_subject_= IX1"" on cl_student_exam_subject a  (cost=3D0.42..10.15 rows=3D2 width= =3D89) (actual time=3D0.010..0.011 rows=3D1 loops=3D313)"
" =                     &nbs= p;               Index Cond: (((companyc= ode)::text =3D '100'::text) AND ((examheaderfk)::text =3D 'BA80952CFF8F4E1C= 3F9F44B62ED9BF37'::text) AND ((registrationnumber)::text =3D (b.registratio= nnumber)::text))"
"            &nbs= p;                     &n= bsp;   Filter: ((examstudentstatus IS NULL) OR (examstudentstatus =3D = ANY ('{R,S,W}'::bpchar[])))"
"          =                 ->  Index S= can using cl_subject_ix3 on cl_subject c  (cost=3D0.28..0.32 rows=3D1 = width=3D53) (actual time=3D0.006..0.006 rows=3D1 loops=3D326)"
"&= nbsp;                    =           Index Cond: (((companycode)::text =3D '= 100'::text) AND ((subjectcode)::text =3D (a.subjectcode)::text))"
"                    -&g= t;  Index Scan using ""cl_student_internal_mark_IX"" on cl_student_int= ernal_mark m  (cost=3D0.42..2.94 rows=3D1 width=3D97) (actual time=3D0= .010..0.010 rows=3D1 loops=3D326)"
"        &= nbsp;                 Index Cond: (= ((companycode)::text =3D (a.companycode)::text) AND ((companycode)::text = =3D '100'::text) AND ((subjectcode)::text =3D (a.subjectcode)::text) AND ((= registrationnumber)::text =3D (a.registrationnumber)::text))"
"&n= bsp;                     =     Filter: (((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8= AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E= 70EF3'::text))"
"             = ->  Index Scan using ""cl_student_semester_subject_IX1"" on cl_stu= dent_semester_subject p  (cost=3D0.42..3.09 rows=3D1 width=3D60) (actu= al time=3D0.010..0.010 rows=3D1 loops=3D326)"
"    &nbs= p;               Index Cond: (((companyc= ode)::text =3D '100'::text) AND ((subjectcode)::text =3D (a.subjectcode)::t= ext) AND ((registrationnumber)::text =3D (a.registrationnumber)::text) AND = (semester =3D a.semester))"
"          &= nbsp;         Filter: ((examheaderfk)::text =3D 'BA8095= 2CFF8F4E1C3F9F44B62ED9BF37'::text)"
"        = ->  Seq Scan on co_company_branch q  (cost=3D0.00..1.01 rows= =3D1 width=3D276) (actual time=3D0.000..0.000 rows=3D1 loops=3D326)"
<= div>"Planning Time: 15.581 ms"
"Execution Time: 19.546 ms"
<= /div>

The query when run against DB1 takes around 7 min 32 seconds.=
The same query when run against DB2 takes around 124= msec.  

Same computer, same PG cluster, same query.
= Why it takes so much time when run against DB1 (client_db)?

Already executed vacuum = against client_db database.  

Any help is really appreciated.

Happiness Always
BKR Sivaprakash

<= /div>


--
Muhammad Ikram

------=_Part_111801_1490837450.1721973938700--