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 1sXDXa-002U4v-0E for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:30:55 +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 1sXDXX-005jvW-SE for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:30:51 +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 1sXDXW-005jvO-MW for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 05:30:51 +0000 Received: from sonic304-19.consmr.mail.sg3.yahoo.com ([106.10.242.209]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sXDXR-001UXp-Hr for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 05:30:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1721971841; bh=uPnoujWH4E/If9c2cQtveRikRKFBBZWjBkevPcWpfac=; h=Date:From:Reply-To:To:Subject:References:From:Subject:Reply-To; b=q494EEQUJUkG5n1pxLH9LdLQsibAJo2++WHd221YqBU70Wegf/UlmB/no5L76K+hrEOoyGzWQPFHyLDkwWR20ItY98NZYDau1XtonKgUvtwmKQEW7RzeM2KtCZdpzCb4ychSVzAprlP4tQPJfOrGHB9CAkrPKUHjF2Ax9W3dw6IYavIf7KyC8rGkgdLF3wd75D5acK6YzCiDJSKXkRtZE18xu9hYZVOjARUaaoUXos1OD9JBhbqdoccTA8xmQlHLb1msWdmCc3/ah9vD3v8zWgui5v31+lxaz3mOoy27cMWp7YZKMgeH2td7zvBUlbl/6VUw/E/vVzbGzAQrhsY4Eg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1721971841; bh=5LnAMgVnnLQgNP6gBlo9MYTlAvYuptVSjqnpCc0d6ix=; h=X-Sonic-MF:X-Sonic-MF:Date:From:To:Subject:From:Subject; b=Jhw2PjRH0EsxT0RDUFIFrUL2FDNKc+BrBVefseK4Dcq5KCc4c3vqNF6Tcfw4OwZbX7Vm2lSuUoJGu+ki/x3TUiw1aAhGmTM3snmvSSFFMQUUuCdd62sJJM0e30rtRAQ58+MQJSWc2lIbc+9e2m+992xG3GlGiML5HPTh30Y5kZuwNzwr3pDFiGcuCbr99MFkSHdJTCVelJ1zaBIlpOyPaYTAxNj6BTdRjTvS9eYwg55ursM6WCHYzN1Agl12ew8vw8od/oY7zSg3Rkv6uJABwhYkKrEsjGWl54gP5VYFLQ1YlyJH1k/Iq1U62efzd5DAwf/aVPzAg14yk02PJ+g54w== X-YMail-OSG: fkYVGIAVM1nsdLxpMzHBivAGOTMSfreEsP4Oodw967O2tLSNJRh9SOMGzspdDBy Cxm82n7nKxdDDtwdT_qIob4C0lwtIrFopvRdPsGtsQgUVJUvbSf3eye4N3pSUmcG1fQGll8onHDD amiu4RczJZH0Ae.pfPexREY5mZCswwLgA4_czPrlzHNH_w.m457DaXvMTDsiV5n7Iqx27_fx9e4R f6ofkAtzCkIEwiVFa85K2zhfYqoYDJT0YoSlIFwd8T3_cjPN2yL9sGAYvFS5QR13Xnqvr4_3CxD. y6vgh3.KhJ_HPDFZoC.0pHBp6UJ2oJGG7Wmy_dNWb3zI82q.WF5doNE.f5zvkL_RQJx8qdHVxgWO aXvZqVdJ_r9uD.ZzGZZoj6tYUdhgc8zzqRO0C2XZSpQMUADKkTy.9AWChuI.25b3ZT0LHk.Uhih7 eujJqbJG1WJO4TbZGMXA8QWkBQ3_qhjFjo0lDYB7CCNEND42hD2J4Xo.r2EBVpCSeFP8OYceCPKQ OERMqv6r6iqIznYAmkRjg0GXTYvYAFfJND7maU_JqPade42mdyMjYYNaGOD.3oIAMy.fEi2I6q94 FNjgXCDFUM3ySGOrMr1kZ_770qLRtmblrKEvvMFMcHhNP1XlyZMNF6yZM0YX5cBzTFHrPgLkOSG3 v1LqsK_DHCXLgAhPR_o3PTeVI7GDAnPKDqa8eY.JTL7HgqPAHAW8A6dU6O6CsmFN_6wNNgFcBa_z 7AsDZmYb0ERhQp9zzrQGUIOJ2p0jEXaTql0R1.hIJGxvfInad5mqu00HvBH2CRTeOvVxsgWNDgVb ehENAkJOZOHL3e15N12So8fIE2OTaohcH9qqtWJm0Tg2rNx60KsfB9fFL7viT9VsgXl1E6vMZj5S pAns5ataUvkbhcl64X3F6eCkRe4y6wr5ZtKQ2l2lttWSqqZyzBZyBNrUbKQKMFBnemWAzC5kcj5v 1LWHMbNDlajJaEBm6Un2yXrMIpgwzPviXCuFDRLCLTyy5psMdoKtHJp7gcO5zPFsme0eB7m7v922 QxLVzM_kPLhhGHGLuEZcH8IOxuHeDo4JvsaAc2pBo8r1nYzHqSOYaj_y_d4BazVlnDj0vOcvMtgl ZigA47_UqMWc7rZbqwFmlNrHKBchoxKfZ5mix8dw3MtHm8ZcM1oOldNDXu6tqY3az61VizrLLsx6 iMdi96bfEbz4CIw07UKAHg9QbiApp1r6YQqwGp70CC5sPr60U_PbJSy5.A8WoVOTbG6pJAgu3WcC I4pAyS9Qbk_MeQA1vF_3tgKUDxen50CiMWE6A17pONKKIU9ZElT5S9OahMUvNxsHJzNeqLZfq2Uc WeQ1WcFYsPCQYv00iDDa6_T5BMHld3Sysp1JsxudskqV4XsbCkMkwjSaxndpbEGEydP1N8Nv_W_r XzcAgQ5HAAum3pmCYLZb1mOdXLBmAcBrSIgw.wPNMNe.9oElOYVW9YA55lYzdj8iwU3OlxV4Gtmw cmTWtYzc46n..4Dc4YJDTEGPMT7ivfVtfl9EqBI0lrhBW1MdBHGkLjmbl9M77GutjJC7vkVZ9VQP CqXUR.6zM_KtKsjG_78lEoy11DjbkA9SYBp54WT3AjAzPeFQokpTnuGiVaQCNJaNj9.vN.BxwO7r QoOp5ZAxTTRRJzicj6WcW.3CCett87wdQMof1At_TZBRWC9apmb_ftWGgkol.pPgELK63DgUN0yu ifWuQljgrN1S3QfmjkxiEt4QwpFj8.P3slLtIgfrNB3i5IH7YydQRfCErsa.u6xmrbEuCCLoQRqb iX_Jr0Gu0VGA9K3h.DA1lWRHxp3QKPaiJhZMNy4HaRW_UA6QmrCtR5UsnrZ7JRXfFl8prt3TwizM YieUfic3TFg_NEvdgQzvFOyM4Fy4wQgHDTHHkWmT6ADzj5yJ9cyd3vCJ457ajU0FVdDJuqGziy3L xLrIjJIzd5HbKLElK.WbpWIQWVMDJydxpM7hWrlTnoQ4P.pXgGuz2t2P52pgno_grt5L7DGGj4qx 3f_KlsQ4roTQyKMTmKPLumspL9p_c_8lAs8EfxJFA4ZWPEc123Czs9.xIYARPAnzmC2A7WkItDov Fx49fFfAQONwEZOiZ8Ddfz2NW4n3jf8sUeSEZnBnWYUD4O7_1MVNx7xpHPNO.w_jOc4r4ABzix41 _E3qEq0GaHF_m4iAHZ47EHNQ5RkgkP6VEYgk8qPdZOLUH1zT6njikDmRv8B.BuFFfbfmzuxDsjPN aUiaQV1XNY9o5eSeGl_oHgHmuhn1ovqcY1O8TWYithynyZi2GGg4pXThoFFBHOl48UhYcO1ziP50 cOWdflJqxv1uByxpmvlJWjA-- X-Sonic-MF: X-Sonic-ID: 7f7502cc-f4ec-4c24-b628-e26fc4a843d2 Received: from sonic.gate.mail.ne1.yahoo.com by sonic304.consmr.mail.sg3.yahoo.com with HTTP; Fri, 26 Jul 2024 05:30:41 +0000 Received: by hermes--production-gq1-799bb7c8cf-jfh5x (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID cef60e2e6e567fa1ad8b06e809cfcddc; Fri, 26 Jul 2024 05:30:38 +0000 (UTC) X-YMail-OSG: y_vVFz8VM1nsOQavr41HLcZ8dv84BOFrKSaNVVycd3nwSv5ooVSi4kRc0Ywsa0E h0FPOGelVC6DB.94NC.52Q2_cNiuXp.GYwuLoun_mEWIHz_rcHIpcgOPC_.Udo1RPFtqTOEvHvqP flxOtXUxz_5moCO9yS5W5E7EuPFq25i.OVotvBC5JtFOINPAW3QMdvrOfd3M.Kf1wgxsbCI6fJa2 Z5wJr4t9JMdFNORALObIFuB751wc9zXsfnsZ9WzNnw3qO2MvzdPTnCtWYXYGk3nIptVl1R9QBQ4A Rd210H2358o2e9Grrrx__iJjC.KExGGd.ZvN.HO5jRvQ3pfgcxOhNKk2uGwGTf9_EW_kBecMImX6 mGCUBCmejXgigTY3CbHD2KROfjlHBBJHAP2AbPox9lJHQIZqUfPWetX8B4WMy5.Aipqn4ffr.48q SQLNo_2XQFnUzUDx9zmg.oBapBmZUP8z3HbOhaoQ.gAirih94ZnvI2asELA2wgfpun42MwAOYxvv Oyzw9EgZj6E0D3afdbSl4TVTaC.9ZYrBx4joHrK8zppK.gCYIF5SW.cbL9coVqDfWse59JIB7AeB FPc0ZAugn5azqqF44R4x5iOQuJaKmdNKa_.Ef8UIEkAp5tROmg5D4q_lNYX1xWCU_BzrOrqBgC8a N7iQeWObyyYTq3qYUXZQQzAxghzXoGHl6d0t12IV5RD1u1arnUI0DZ_4hPLBWtgUshX_EMl_EI86 V3jlzV_iaZo7fcjalradWq7kBERFnPJFUylBCQZdPW1COEHQIeOhnr_LKSQFwrQY9WrQ72pichRo wz2ZVDvEthfs_k.QF0.LSKY0JrSdU37mMdMK1J0HkYa8cwik6VSfLw.L3OVWkMS6kct_Nol.Zbze lmk9WgnRd65ee2bRhjdgK_77MRJCh2ftrD6fmWIQQR5O34RBYgL0sJmpW9mB0otuquMpA1aud5bk wd6Auqg1GQ__Lw1NUep_ocUM2U9zbihJzaCv8FimXTr89TYBN_47k.m3UYkG8BT2URuL7Q.0henE oc_HSz6Cr25QXrxZ7guKIdEr5Dn9PC1UdM5bNyef_3hAVx8FbSz9Oy3uXl0sBbREKc27l4MWxPxw 1.vMTZCOjT_T2dsupszmd4KD84nxDHrcFuhYsWvbqyHhlS2QdkCwhglvqWx40GSU.vCcDHfOUUuc 3ZYcHBfKzRZEtw4brxTHm.R4vZ4cGQP5t3pDDTHsACSXWqGLzSjwsq4RZIy85nXoeuzJQh7VSkhU 7mMvGekoDfcLdbz4X.pKb_numHFqLfYQhfzt9VOZmht4Mos1ftlKM25WMZpBCB4xxs6qRD5RshJi ZUV6m1Kfr01Lg67.PwVpdUTZ3d9Ui1VZ6._RdGq.MFAm0uFDng6DkEzCjDuShY5nRnCVeztVR09v 2SY0BSHPn_P.fkF9_pRVvVi6kfRopsG.hfZw.ziGuJrPxPrQ87iyMpV_GTvSXaUkS4Df5tpBLfEB 2i1LVZDsu8gLcuvY.zztgILNd97Ubu1AinWE3myFSxzoPxr3nqkIDCiW0SNlmf8OmPyxEr3ghrWr 5p9sPZbFdYB.WN2b.6uvMZPlHmZbzMxgOINEmRqOyo4TJW4RIDnqL9YHeg5UHUfeASGD3UKknFgK dCPSM0Dehr8mzyrg1mwr4OTLkuj6ebeu6qYf2_K.Z8NdkDoxOm3VpMyYyqL1nSyUc_nqVVTIOtRX g9Xfq28epzlyeRjbOuvgdNbzihil_LxroF9CM_MynsxqGhFQGjXfqr6Xnp7zLiGidJ7mRC3dc1Ti ulxeLfWp5O_K6knixXy9KUXMDoB50V8HYyBG4QY9dan1yAkotbfg6g17ikjtZ4B8iSwNzIqm8rGh oBBiTAsZ7FEnCFVtxPbi58eN08OlfCCuwn23S9A1qnTcVzp4DbqLrA6MQ1WVJZHJKaA_8TGhp4cS J13ozxEWmNeQDtzpcZsMGFPqmMKhBkYNz2OVCcvpdXFeptKhDu.YcFOHCrZ6Pq9pDBoqxd.BLlTh 1zb7ziMbiv0ZwhTNetcEecWff11nR7GocJfxOgSNLdwTsz8CTIxrX7uLQXhfiXiOBwn304gOWbap xnfL0mjJRktHtj8aRvqYfWwcU42so59E_4R_0a72hftPuZrV5Urk.kcG1agpXx3rXhnQTNMcXF8W dn7_hefzwaBStrcIuYGAcVZo7Akos5O0eE5tMk9.iFlHUsABgYLDmKiONeZrqgRMiA..ATwcAcgo 5dck3uORGvYiMH8mFNhnNJyjtecY8H179gOucNmPqZBsJUbCvzu_yudPo_8oVDOwkflVhh4oLGla iyMtoD02hrg0zvybjs7At5QPSJfpwXo5OaLxLsrI- X-Sonic-MF: X-Sonic-ID: d31e41fb-6890-41d2-98fb-9dc95da77886 Received: from sonic.gate.mail.ne1.yahoo.com by sonic304.consmr.mail.gq1.yahoo.com with HTTP; Fri, 26 Jul 2024 05:30:37 +0000 Date: Fri, 26 Jul 2024 05:30:23 +0000 (UTC) From: "sivapostgres@yahoo.com" Reply-To: "sivapostgres@yahoo.com" To: Postgresql General Group Message-ID: <431223779.107307.1721971823963@mail.yahoo.com> Subject: Re: Slow performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_107306_791073837.1721971823957" References: <431223779.107307.1721971823963.ref@mail.yahoo.com> X-Mailer: WebService/1.1.22544 YMailNorrin Content-Length: 59613 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_107306_791073837.1721971823957 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 ------=_Part_107306_791073837.1721971823957 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello,
Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, = Two databases with identical data.

1.  First DB:&nbs= p; client_db
2.  Second DB: client_test

Took back= up (pg_dump) of first database (client_db) and restored the database as sec= ond database (client_test).
Query:
 Sel= ect a.examname, a.registrationnumber, b.studentname, d.departmentname, e.le= velname, 
        a.subjectcode, c.subje= ctname, b.regular, a.semester, a.dummynumber, p.semester as curr_sem, =
        a.internalmark, a.externalmark, a.re= sult, coalesce((a.internalmark + a.externalmark),0) as total, 
        a.absent, a.malpractice, c.maxinternalmark, = f.noofsemester, a.examstudentstatus, 
      &= nbsp; Case When a.result =3D 'P'  Then 'P' Else 
 =       Case When a.result =3D 'F' and a.malpractice =3D 'Y' = and a.examstudentstatus is null Then 'M' Else 
   =     Case When a.result =3D 'F' and a.absent =3D 'Y' and a.examst= udentstatus =3D 'R' Then 'R.C' Else 
      &n= bsp; Case When a.result =3D 'F' and a.absent =3D 'Y' and a.examstudentstatu= s =3D 'S' Then 'S.L' Else 
        Case = When a.result =3D 'F' and a.absent =3D 'Y' and a.examstudentstatus =3D 'F' = Then 'N.P' Else 
        Case When a.res= ult =3D 'F' and a.absent =3D 'Y' and a.examstudentstatus =3D 'W' Then 'W.H'= Else 
              &nbs= p;                     &n= bsp;               'RA' End End End End = End End as res, 
        Concat(RTrim(f.= degreeawarded), ' ', RTrim(d.departmentname))  as course, 
<= div>        a.revaluation, m.absent as int_abs, n.compa= nyname, n.companydescription, 
        m= .totalmark as int_mark, q.addressone, q.addresstwo, 
  =       Case When a.semester > f.noofsemester Then 'PRIVATE= ' 
             When a.se= mester <=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 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=3D286= 2.35..2862.36 rows=3D1 width=3D1088) (actual time=3D451671.464..451671.495 = rows=3D326 loops=3D1)"
"  Sort Key: (concat(rtrim((f.degreea= warded)::text), ' ', rtrim((d.departmentname)::text))), (CASE WHEN (a.semes= ter > f.noofsemester) THEN 'PRIVATE'::text WHEN ((a.semester <=3D f.n= oofsemester) 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 - Year'::text WHEN ((a.semester <=3D f.noo= fsemester) AND (a.semester =3D ANY ('{5,6}'::integer[]))) THEN 'III - Year'= ::text ELSE NULL::text END), a.registrationnumber, b.regular, a.semester DE= SC, c.subjectserialno, (CASE WHEN (c.subjectcategory =3D 'T'::bpchar) THEN = 1 WHEN (c.subjectcategory =3D 'P'::bpchar) THEN 2 WHEN (c.subjectcategory = =3D 'D'::bpchar) THEN 3 WHEN (c.subjectcategory =3D 'V'::bpchar) THEN 4 WHE= N (c.subjectcategory =3D 'J'::bpchar) THEN 5 ELSE NULL::integer END), c.anc= illary, (CASE WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D 'C'::tex= t) THEN 1 WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D 'S'::text) T= HEN 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 EL= SE 5 END), a.subjectcode"
"  Sort Method: quicksort  Me= mory: 193kB"
"  ->  Nested Loop  (cost=3D2.36..= 2862.34 rows=3D1 width=3D1088) (actual time=3D57829.857..451662.727 rows=3D= 326 loops=3D1)"
"        ->  Nested L= oop  (cost=3D2.36..2861.23 rows=3D1 width=3D686) (actual time=3D57829.= 829..451658.085 rows=3D326 loops=3D1)"
"      &nbs= p;       Join Filter: (((b.registrationnumber)::text =3D (p.= registrationnumber)::text) AND ((c.subjectcode)::text =3D (p.subjectcode)::= text) AND (a.semester =3D p.semester))"
"      &nb= sp;       Rows Removed by Join Filter: 13614738"
"=               ->  Index Scan usi= ng ""cl_student_semester_subject_IX3"" on cl_student_semester_subject p&nbs= p; (cost=3D0.55..8.57 rows=3D1 width=3D60) (actual time=3D0.033..55.702 row= s=3D41764 loops=3D1)"
"            =         Index Cond: (((companycode)::text =3D '100'::te= xt) AND ((examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text)= )"
"              ->  = Nested Loop Left Join  (cost=3D1.81..2848.39 rows=3D244 width=3D735) (= actual time=3D0.068..10.768 rows=3D326 loops=3D41764)"
"  &n= bsp;                 Join Filter: (= ((m.departmentheaderfk)::text =3D (b.departmentheaderfk)::text) AND ((m.lev= elfk)::text =3D (b.levelfk)::text) AND (m.regular =3D b.regular))"
"                    -&= gt;  Nested Loop  (cost=3D1.39..2135.32 rows=3D244 width=3D795) (= actual time=3D0.053..6.723 rows=3D326 loops=3D41764)"
"  &nb= sp;                     &= nbsp; ->  Nested Loop  (cost=3D1.11..2058.12 rows=3D244 width= =3D746) (actual time=3D0.045..4.299 rows=3D326 loops=3D41764)"
"&= nbsp;                    =           ->  Nested Loop  (cost=3D0= .68..312.80 rows=3D172 width=3D699) (actual time=3D0.029..0.338 rows=3D313 = loops=3D41764)"
"             =                     &nbs= p;   ->  Seq Scan on co_company n  (cost=3D0.00..1.01 row= s=3D1 width=3D394) (actual time=3D0.001..0.001 rows=3D1 loops=3D41764)"
"                  &nbs= p;                     &n= bsp;   Filter: ((companycode)::text =3D '100'::text)"
" = ;                     &nb= sp;               ->  Nested Loo= p  (cost=3D0.68..310.07 rows=3D172 width=3D305) (actual time=3D0.025..= 0.282 rows=3D313 loops=3D41764)"
"        &nb= sp;                     &= nbsp;             ->  Nested Loop&nbs= p; (cost=3D0.27..10.70 rows=3D1 width=3D438) (actual time=3D0.013..0.019 ro= ws=3D1 loops=3D41764)"
"           =                     &nbs= p;                 ->  Nest= ed Loop  (cost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.009= ..0.013 rows=3D1 loops=3D41764)"
"        &nb= sp;                     &= nbsp;                    =     ->  Seq Scan on cl_department_header d  (cost= =3D0.00..1.30 rows=3D1 width=3D200) (actual time=3D0.002..0.004 rows=3D1 lo= ops=3D41764)"
"              &= nbsp;                    =                     &nbs= p;     Filter: ((departmentheaderpk)::text =3D '04DF8BD89D0844DD4= D8AA151EFB28657'::text)"
"          &nbs= p;                     &n= bsp;                     =         Rows Removed by Filter: 23"
"  &= nbsp;                    =                     &nbs= p;           ->  Index Scan using cl_depar= tment_detail_ix1 on cl_department_detail f  (cost=3D0.27..8.29 rows=3D= 1 width=3D78) (actual time=3D0.007..0.007 rows=3D1 loops=3D41764)"
"                    &n= bsp;                     =                     Index= Cond: (((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657':= :text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))"=
"                  =                      = ;           ->  Seq Scan on cl_level e&nbs= p; (cost=3D0.00..1.09 rows=3D1 width=3D160) (actual time=3D0.001..0.003 row= s=3D1 loops=3D41764)"
"            =                      = ;                     &nb= sp; Filter: ((levelpk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text)"=
"                  =                      = ;                 Rows Removed by F= ilter: 6"
"               = ;                     &nb= sp;       ->  Index Scan using cl_student_name_ix4 o= n cl_student_name b  (cost=3D0.41..297.65 rows=3D172 width=3D97) (actu= al time=3D0.011..0.165 rows=3D313 loops=3D41764)"
"    =                      = ;                     &nb= sp;   Index Cond: (((companycode)::text =3D '100'::text) AND ((departm= entheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((leve= lfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text) AND (status =3D 'A= '::bpchar))"
"              &n= bsp;                 ->  In= dex Scan using ""cl_student_exam_subject_IX1"" on cl_student_exam_subject a=   (cost=3D0.42..10.13 rows=3D2 width=3D89) (actual time=3D0.012..0.012= rows=3D1 loops=3D13072132)"
"          =                      = ;       Index Cond: (((companycode)::text =3D '100'::text) A= ND ((examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND = ((registrationnumber)::text =3D (b.registrationnumber)::text))"
"=                      = ;                 Filter: ((examstu= dentstatus IS NULL) OR (examstudentstatus =3D ANY ('{R,S,W}'::bpchar[])))"<= /div>
"                  &= nbsp;       ->  Index Scan using cl_subject_ix3 on c= l_subject c  (cost=3D0.28..0.32 rows=3D1 width=3D53) (actual time=3D0.= 007..0.007 rows=3D1 loops=3D13615064)"
"      &nbs= p;                     &n= bsp;   Index Cond: (((companycode)::text =3D '100'::text) AND ((subjec= tcode)::text =3D (a.subjectcode)::text))"
"      &= nbsp;             ->  Index Scan usin= g ""cl_student_internal_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 loo= ps=3D13615064)"
"             =             Index Cond: (((companycode)::tex= t =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 '04DF8BD89D0844DD4D8AA151EFB28657'::text)= AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))"
=
"        ->  Seq Scan on co_company_branch= q  (cost=3D0.00..1.01 rows=3D1 width=3D276) (actual time=3D0.001..0.0= 01 rows=3D1 loops=3D326)"
"Planning Time: 15.936 ms"
"E= xecution Time: 451672.059 ms"



<= div dir=3D"ltr" data-setdir=3D"false">Explain Analyze of Second DB (client_= test)
"Sort  (co= st=3D3454.91..3454.92 rows=3D1 width=3D1088) (actual time=3D19.120..19.137 = rows=3D326 loops=3D1)"
"  Sort Key: (concat(rtrim((f.degreea= warded)::text), ' ', rtrim((d.departmentname)::text))), (CASE WHEN (a.semes= ter > f.noofsemester) THEN 'PRIVATE'::text WHEN ((a.semester <=3D f.n= oofsemester) 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 - Year'::text WHEN ((a.semester <=3D f.noo= fsemester) AND (a.semester =3D ANY ('{5,6}'::integer[]))) THEN 'III - Year'= ::text ELSE NULL::text END), a.registrationnumber, b.regular, a.semester DE= SC, c.subjectserialno, (CASE WHEN (c.subjectcategory =3D 'T'::bpchar) THEN = 1 WHEN (c.subjectcategory =3D 'P'::bpchar) THEN 2 WHEN (c.subjectcategory = =3D 'D'::bpchar) THEN 3 WHEN (c.subjectcategory =3D 'V'::bpchar) THEN 4 WHE= N (c.subjectcategory =3D 'J'::bpchar) THEN 5 ELSE NULL::integer END), c.anc= illary, (CASE WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D 'C'::tex= t) THEN 1 WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D 'S'::text) T= HEN 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 EL= SE 5 END), a.subjectcode"
"  Sort Method: quicksort  Me= mory: 193kB"
"  ->  Nested Loop  (cost=3D2.23..= 3454.90 rows=3D1 width=3D1088) (actual time=3D0.319..14.984 rows=3D326 loop= s=3D1)"
"        ->  Nested Loop = ; (cost=3D2.23..3453.78 rows=3D1 width=3D686) (actual time=3D0.298..13.691 = rows=3D326 loops=3D1)"
"           =   Join Filter: (((b.registrationnumber)::text =3D (p.registrationnumb= er)::text) AND ((c.subjectcode)::text =3D (p.subjectcode)::text))"
"              ->  Nested Loop= Left Join  (cost=3D1.81..2739.81 rows=3D230 width=3D735) (actual time= =3D0.259..9.970 rows=3D326 loops=3D1)"
"      &nbs= p;             Join Filter: (((m.departmenthe= aderfk)::text =3D (b.departmentheaderfk)::text) AND ((m.levelfk)::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)"
"        &n= bsp;                 ->  Ne= sted Loop  (cost=3D1.11..1987.62 rows=3D230 width=3D746) (actual time= =3D0.211..3.955 rows=3D326 loops=3D1)"
"      &nbs= p;                     &n= bsp;   ->  Nested Loop  (cost=3D0.68..299.78 rows=3D166 w= idth=3D699) (actual time=3D0.118..0.393 rows=3D313 loops=3D1)"
"&= nbsp;                    =                 ->  Seq Sc= an on co_company n  (cost=3D0.00..1.01 rows=3D1 width=3D394) (actual t= ime=3D0.026..0.027 rows=3D1 loops=3D1)"
"      &nb= sp;                     &= nbsp;               Filter: ((companycod= e)::text =3D '100'::text)"
"          &n= bsp;                     =       ->  Nested Loop  (cost=3D0.68..297.11 row= s=3D166 width=3D305) (actual time=3D0.091..0.316 rows=3D313 loops=3D1)"
"                  &nbs= p;                     &n= bsp;   ->  Nested Loop  (cost=3D0.27..10.70 rows=3D1 widt= h=3D438) (actual time=3D0.041..0.048 rows=3D1 loops=3D1)"
" =                     &nbs= p;                     &n= bsp;     ->  Nested Loop  (cost=3D0.27..9.60 rows=3D= 1 width=3D278) (actual time=3D0.034..0.038 rows=3D1 loops=3D1)"
"=                      = ;                     &nb= sp;             ->  Seq Scan on cl_de= partment_header d  (cost=3D0.00..1.30 rows=3D1 width=3D200) (actual ti= me=3D0.009..0.011 rows=3D1 loops=3D1)"
"      &nbs= p;                     &n= bsp;                     =             Filter: ((departmentheaderpk)::te= xt =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text)"
"    =                      = ;                     &nb= sp;               Rows Removed by Filter= : 23"
"                &n= bsp;                     =                   ->  = Index Scan using cl_department_detail_ix1 on cl_department_detail f  (= cost=3D0.27..8.29 rows=3D1 width=3D78) (actual time=3D0.024..0.024 rows=3D1= loops=3D1)"
"              &n= bsp;                     =                      = ;     Index Cond: (((departmentheaderfk)::text =3D '04DF8BD89D084= 4DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B14= 22D83E70EF3'::text))"
"            =                      = ;                 ->  Seq S= can on cl_level e  (cost=3D0.00..1.09 rows=3D1 width=3D160) (actual ti= me=3D0.006..0.008 rows=3D1 loops=3D1)"
"      &nbs= p;                     &n= bsp;                     =       Filter: ((levelpk)::text =3D '37A9BEC2638844FFD5B1422D= 83E70EF3'::text)"
"            &nbs= p;                     &n= bsp;                     = Rows Removed by Filter: 6"
"          &n= bsp;                     =             ->  Index Scan using cl_s= tudent_name_ix4 on cl_student_name b  (cost=3D0.41..284.75 rows=3D166 = width=3D97) (actual time=3D0.049..0.184 rows=3D313 loops=3D1)"
"&= nbsp;                    =                     &nbs= p;       Index Cond: (((companycode)::text =3D '100'::text) = AND ((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::tex= t) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text) AND (= status =3D 'A'::bpchar))"
"          &nb= sp;                     -= >  Index Scan using ""cl_student_exam_subject_IX1"" on cl_student_e= xam_subject a  (cost=3D0.42..10.15 rows=3D2 width=3D89) (actual time= =3D0.010..0.011 rows=3D1 loops=3D313)"
"      &nbs= p;                     &n= bsp;         Index Cond: (((companycode)::text =3D '100= '::text) AND ((examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::= text) AND ((registrationnumber)::text =3D (b.registrationnumber)::text))"
"                  &n= bsp;                   Filter:= ((examstudentstatus IS NULL) OR (examstudentstatus =3D ANY ('{R,S,W}'::bpc= har[])))"
"               = ;           ->  Index Scan using cl_subjec= t_ix3 on cl_subject c  (cost=3D0.28..0.32 rows=3D1 width=3D53) (actual= time=3D0.006..0.006 rows=3D1 loops=3D326)"
"     =                     &nbs= p;     Index Cond: (((companycode)::text =3D '100'::text) AND ((s= ubjectcode)::text =3D (a.subjectcode)::text))"
"    &nb= sp;               ->  Index Scan= using ""cl_student_internal_mark_IX"" on cl_student_internal_mark m  = (cost=3D0.42..2.94 rows=3D1 width=3D97) (actual time=3D0.010..0.010 rows=3D= 1 loops=3D326)"
"             =             Index Cond: (((companycode)::tex= t =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 '04DF8BD89D0844DD4D8AA151EFB28657'::text)= AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))"
=
"              ->  Index Sc= an using ""cl_student_semester_subject_IX1"" on cl_student_semester_subject= p  (cost=3D0.42..3.09 rows=3D1 width=3D60) (actual time=3D0.010..0.01= 0 rows=3D1 loops=3D326)"
"          &nbs= p;         Index Cond: (((companycode)::text =3D '100':= :text) AND ((subjectcode)::text =3D (a.subjectcode)::text) AND ((registrati= onnumber)::text =3D (a.registrationnumber)::text) AND (semester =3D a.semes= ter))"
"                &= nbsp;   Filter: ((examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED= 9BF37'::text)"
"        ->  Seq Scan = on co_company_branch q  (cost=3D0.00..1.01 rows=3D1 width=3D276) (actu= al time=3D0.000..0.000 rows=3D1 loops=3D326)"
"Planning 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 tak= es around 124 msec.  

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

Already e= xecuted vacuum against client_db database.  

An= y help is really appreciated.
<= br>
Happiness Always
BKR Siv= aprakash

------=_Part_107306_791073837.1721971823957--