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 1sXDie-002V5c-BD for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:42:20 +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 1sXDic-005r7S-I2 for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 05:42:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sXDic-005r7J-2R for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 05:42:18 +0000 Received: from mail-lf1-x131.google.com ([2a00:1450:4864:20::131]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sXDiY-001WAx-V9 for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 05:42:17 +0000 Received: by mail-lf1-x131.google.com with SMTP id 2adb3069b0e04-52f04b4abdcso1315980e87.2 for ; Thu, 25 Jul 2024 22:42:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721972534; x=1722577334; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=rCw/tn7Ape2a3TPADFg9xWwyfW6+qGeVVryxTeTN8dM=; b=fLf3Hs0NhJPtHtpWqA3FkOkbVMj55VW1LbnRUn9BAgr8Ncqv2EnPYZOeEVKsDTq+ID y9KaAyCVdfO3vYgVR9zlntunh83QzRWximM3JguaAGgsp0Oeu4NB7qz9oBZg0A9vuIG8 UbA96nvH32qS+1Sz87XPJUlwL10MoxZFrHzx5gpvaMhFnk9ZzYIHGiWBDNxRPpBV1PJ+ rhx2k+1bkVRN94WlcNwECbiOapeSL8enLBWWOtZoQgciWJ5J60Vrn1WdzagEj+joEAl1 JVcjGA4NIo4g2qX6Wr+UROON0CymaD1/2zzAPvDrv9HaLeyC4Sp8uW80wM/NGIHCXYeF Tt5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721972534; x=1722577334; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=rCw/tn7Ape2a3TPADFg9xWwyfW6+qGeVVryxTeTN8dM=; b=ceXYcWwQFvkxPkW2uHE4O+L38hh8ZcGATyBjyxcc58zlJ0azcO40RF9pPYqqyvCHtJ E0khPNi8pa9GVM9iosmmPB5+BLp0V/Xj4+AAaLCJDcoMVQXVFcQylTGpdaVVZhOalccz etnQdvgIFENQGTZ/WepoaIOG3R0k3WC+O4XB6s/qNf8bYayU7QQOxxoh3qIU43SLwDXX v12LS+62LHdxKx9YyL9OA2tAjt7/WMMSjtCEm3xDkZUw4MyaicvQEmzVZmKR4W81IuHx hemcxF2P2whtsFNPFQuyap0ZPCKH8hz7Pk6IyL8ALouz2feBxhM8pi49+5v0+DX4TkNt e6ow== X-Gm-Message-State: AOJu0YwBR+dW1IjoRIvxOC6ncKi1jC6VuK9CGahJsZWOX0s65uHl9Gct adlrsAS4XkCHKretqWL2NCDzU/RkM3huX2996MkhXeBSSltADvorg13LAKEsoIf5l47eJ1FyzvO HQXm72mxYlUc5f0ERr0YjdQhkX7yR8ZGM2pI= X-Google-Smtp-Source: AGHT+IE4D+llvZZz8FEFFKPBj1j6EFiAeigtRkXHI3IEBjSXKzAnEPegGtfzFLaIoResBZNo42JWfvJ7q7zjuB2+clI= X-Received: by 2002:ac2:4c43:0:b0:52b:8ef7:bf1f with SMTP id 2adb3069b0e04-52fd3f04265mr3045013e87.17.1721972533302; Thu, 25 Jul 2024 22:42:13 -0700 (PDT) MIME-Version: 1.0 References: <431223779.107307.1721971823963.ref@mail.yahoo.com> <431223779.107307.1721971823963@mail.yahoo.com> In-Reply-To: <431223779.107307.1721971823963@mail.yahoo.com> From: Muhammad Ikram Date: Fri, 26 Jul 2024 10:41:56 +0500 Message-ID: Subject: Re: Slow performance To: "sivapostgres@yahoo.com" Cc: Postgresql General Group Content-Type: multipart/alternative; boundary="000000000000291c6a061e1ff919" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000291c6a061e1ff919 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, Could you perform diff on postgresql.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 sivapostgres@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 > database as second database (client_test). > > Query: > Select a.examname, a.registrationnumber, b.studentname, d.departmentname= , > e.levelname, > a.subjectcode, c.subjectname, b.regular, a.semester, > a.dummynumber, p.semester as curr_sem, > a.internalmark, a.externalmark, a.result, coalesce((a.internalmar= k > + a.externalmark),0) as total, > a.absent, a.malpractice, c.maxinternalmark, f.noofsemester, > a.examstudentstatus, > 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.examstudentstatus =3D 'R' Then 'R.C' Else > Case When a.result =3D 'F' and a.absent =3D 'Y' and > a.examstudentstatus =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.result =3D 'F' and a.absent =3D 'Y' and > a.examstudentstatus =3D 'W' Then 'W.H' Else > 'RA' End End End End > End End as res, > Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)) as > course, > a.revaluation, m.absent as int_abs, n.companyname, > n.companydescription, > m.totalmark as int_mark, q.addressone, q.addresstwo, > Case When a.semester > f.noofsemester Then 'PRIVATE' > 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 as studyr, > c.subjectserialno, p.regulararrear > From cl_student_exam_subject a > Join cl_student_name b > On b.companycode =3D a.companycode > And b.registrationnumber =3D a.registrationnumber > Join cl_subject c > On c.companycode =3D a.companycode > And c.subjectcode =3D a.subjectcode > Join cl_department_header d > On d.departmentheaderpk =3D b.departmentheaderfk > Join cl_level e > On e.levelpk =3D b.Levelfk > Join cl_department_detail f > On f.departmentheaderfk =3D b.departmentheaderfk > And f.levelfk =3D b.levelfk > Left Outer Join cl_student_internal_mark m > On m.companycode =3D a.companycode > And m.registrationnumber =3D a.registrationnumber > And m.subjectcode =3D a.subjectcode > And m.departmentheaderfk =3D b.departmentheaderfk > And m.levelfk =3D b.levelfk > 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.examheaderfk > And p.subjectcode =3D a.subjectcode > And p.registrationnumber =3D a.registrationnumber > And p.semester =3D a.semester > Join co_company_branch q > On n.companycode =3D a.companycode > Where a.companycode =3D '100' > And a.examheaderfk =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37' > And (a.examstudentstatus is null or a.examstudentstatus in ('R', 'S', > 'W')) > And b.departmentheaderfk in ('04DF8BD89D0844DD4D8AA151EFB28657') > And b.levelfk in ('37A9BEC2638844FFD5B1422D83E70EF3') > And b.status =3D 'A' > Order By Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)), > Case When a.semester > f.noofsemester Then 'PRIVATE' > 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, > Case When c.subjectcategory =3D 'T' Then 1 > When c.subjectcategory =3D 'P' Then 2 > When c.subjectcategory =3D 'D' Then 3 > When c.subjectcategory =3D 'V' Then 4 > When c.subjectcategory =3D 'J' Then 5 End, > c.ancillary, > Case When Substring(a.subjectcode, 6, 1) =3D 'C' Then 1 > When Substring(a.subjectcode, 6, 1) =3D 'S' Then 2 > When Substring(a.subjectcode, 6, 1) =3D 'A' Then 3 > When Substring(a.subjectcode, 6, 1) =3D 'E' Then 4 Else 5 End, > 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 loops=3D1)" > " Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ', > rtrim((d.departmentname)::text))), (CASE WHEN (a.semester > f.noofsemeste= r) > THEN 'PRIVATE'::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semest= er =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.noofsemester) AND (a.semester =3D AN= Y > ('{5,6}'::integer[]))) THEN 'III - Year'::text ELSE NULL::text END), > a.registrationnumber, b.regular, a.semester DESC, c.subjectserialno, (CAS= E > 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 WHEN (c.subjectcategory =3D > 'J'::bpchar) THEN 5 ELSE NULL::integer 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: quicksort Memory: 193kB" > " -> Nested Loop (cost=3D2.36..2862.34 rows=3D1 width=3D1088) (actual > time=3D57829.857..451662.727 rows=3D326 loops=3D1)" > " -> Nested Loop (cost=3D2.36..2861.23 rows=3D1 width=3D686) (ac= tual > time=3D57829.829..451658.085 rows=3D326 loops=3D1)" > " Join Filter: (((b.registrationnumber)::text =3D > (p.registrationnumber)::text) AND ((c.subjectcode)::text =3D > (p.subjectcode)::text) AND (a.semester =3D p.semester))" > " Rows Removed by Join Filter: 13614738" > " -> Index Scan using ""cl_student_semester_subject_IX3"" o= n > cl_student_semester_subject p (cost=3D0.55..8.57 rows=3D1 width=3D60) (a= ctual > time=3D0.033..55.702 rows=3D41764 loops=3D1)" > " Index Cond: (((companycode)::text =3D '100'::text) A= ND > ((examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text))" > " -> Nested Loop Left Join (cost=3D1.81..2848.39 rows=3D24= 4 > width=3D735) (actual time=3D0.068..10.768 rows=3D326 loops=3D41764)" > " Join Filter: (((m.departmentheaderfk)::text =3D > (b.departmentheaderfk)::text) AND ((m.levelfk)::text =3D (b.levelfk)::tex= t) > AND (m.regular =3D b.regular))" > " -> Nested Loop (cost=3D1.39..2135.32 rows=3D244 > width=3D795) (actual time=3D0.053..6.723 rows=3D326 loops=3D41764)" > " -> Nested Loop (cost=3D1.11..2058.12 rows=3D= 244 > width=3D746) (actual time=3D0.045..4.299 rows=3D326 loops=3D41764)" > " -> Nested Loop (cost=3D0.68..312.80 > rows=3D172 width=3D699) (actual time=3D0.029..0.338 rows=3D313 loops=3D41= 764)" > " -> Seq Scan on co_company n > (cost=3D0.00..1.01 rows=3D1 width=3D394) (actual time=3D0.001..0.001 rows= =3D1 > loops=3D41764)" > " Filter: ((companycode)::text > =3D '100'::text)" > " -> Nested Loop (cost=3D0.68..310= .07 > rows=3D172 width=3D305) (actual time=3D0.025..0.282 rows=3D313 loops=3D41= 764)" > " -> Nested Loop > (cost=3D0.27..10.70 rows=3D1 width=3D438) (actual time=3D0.013..0.019 row= s=3D1 > loops=3D41764)" > " -> Nested Loop > (cost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.009..0.013 rows= =3D1 > loops=3D41764)" > " -> 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)" > " Filter: > ((departmentheaderpk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text)= " > " Rows > Removed by Filter: 23" > " -> Index Scan > using cl_department_detail_ix1 on cl_department_detail f (cost=3D0.27..8= .29 > rows=3D1 width=3D78) (actual time=3D0.007..0.007 rows=3D1 loops=3D41764)" > " Index Cond= : > (((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text= ) > AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))" > " -> Seq Scan on > cl_level e (cost=3D0.00..1.09 rows=3D1 width=3D160) (actual time=3D0.001= ..0.003 > rows=3D1 loops=3D41764)" > " Filter: > ((levelpk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text)" > " Rows Removed by > Filter: 6" > " -> 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)" > " Index Cond: > (((companycode)::text =3D '100'::text) AND ((departmentheaderfk)::text = =3D > '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D > '37A9BEC2638844FFD5B1422D83E70EF3'::text) AND (status =3D 'A'::bpchar))" > " -> Index 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) AND ((examheaderfk)::text =3D > 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND ((registrationnumber)::text= =3D > (b.registrationnumber)::text))" > " 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.00= 7..0.007 > rows=3D1 loops=3D13615064)" > " Index Cond: (((companycode)::text =3D > '100'::text) AND ((subjectcode)::text =3D (a.subjectcode)::text))" > " -> Index Scan using ""cl_student_internal_mark_IX"" > on cl_student_internal_mark m (cost=3D0.42..2.90 rows=3D1 width=3D97) (a= ctual > time=3D0.011..0.011 rows=3D1 loops=3D13615064)" > " 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))" > " 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.001 rows=3D1 loops=3D326)" > "Planning Time: 15.936 ms" > "Execution Time: 451672.059 ms" > > > > Explain Analyze of Second DB (client_test) > "Sort (cost=3D3454.91..3454.92 rows=3D1 width=3D1088) (actual > time=3D19.120..19.137 rows=3D326 loops=3D1)" > " Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ', > rtrim((d.departmentname)::text))), (CASE WHEN (a.semester > f.noofsemeste= r) > THEN 'PRIVATE'::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semest= er =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.noofsemester) AND (a.semester =3D AN= Y > ('{5,6}'::integer[]))) THEN 'III - Year'::text ELSE NULL::text END), > a.registrationnumber, b.regular, a.semester DESC, c.subjectserialno, (CAS= E > 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 WHEN (c.subjectcategory =3D > 'J'::bpchar) THEN 5 ELSE NULL::integer 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: quicksort 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) (ac= tual > time=3D0.298..13.691 rows=3D326 loops=3D1)" > " Join Filter: (((b.registrationnumber)::text =3D > (p.registrationnumber)::text) AND ((c.subjectcode)::text =3D > (p.subjectcode)::text))" > " -> Nested Loop Left Join (cost=3D1.81..2739.81 rows=3D23= 0 > width=3D735) (actual time=3D0.259..9.970 rows=3D326 loops=3D1)" > " Join Filter: (((m.departmentheaderfk)::text =3D > (b.departmentheaderfk)::text) AND ((m.levelfk)::text =3D (b.levelfk)::tex= t) > 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)" > " -> Nested Loop (cost=3D1.11..1987.62 rows=3D= 230 > width=3D746) (actual time=3D0.211..3.955 rows=3D326 loops=3D1)" > " -> Nested Loop (cost=3D0.68..299.78 > rows=3D166 width=3D699) (actual time=3D0.118..0.393 rows=3D313 loops=3D1)= " > " -> 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)" > " Filter: ((companycode)::text > =3D '100'::text)" > " -> Nested Loop (cost=3D0.68..297= .11 > rows=3D166 width=3D305) (actual time=3D0.091..0.316 rows=3D313 loops=3D1)= " > " -> Nested Loop > (cost=3D0.27..10.70 rows=3D1 width=3D438) (actual time=3D0.041..0.048 row= s=3D1 > loops=3D1)" > " -> Nested Loop > (cost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.034..0.038 rows= =3D1 > loops=3D1)" > " -> Seq Scan on > cl_department_header d (cost=3D0.00..1.30 rows=3D1 width=3D200) (actual > time=3D0.009..0.011 rows=3D1 loops=3D1)" > " Filter: > ((departmentheaderpk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text)= " > " Rows > Removed by Filter: 23" > " -> 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)" > " Index Cond= : > (((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text= ) > AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text))" > " -> Seq Scan on > cl_level e (cost=3D0.00..1.09 rows=3D1 width=3D160) (actual time=3D0.006= ..0.008 > rows=3D1 loops=3D1)" > " Filter: > ((levelpk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text)" > " Rows Removed by > Filter: 6" > " -> Index Scan using > cl_student_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)" > " Index Cond: > (((companycode)::text =3D '100'::text) AND ((departmentheaderfk)::text = =3D > '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D > '37A9BEC2638844FFD5B1422D83E70EF3'::text) AND (status =3D 'A'::bpchar))" > " -> 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)" > " Index Cond: (((companycode)::text = =3D > '100'::text) AND ((examheaderfk)::text =3D > 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND ((registrationnumber)::text= =3D > (b.registrationnumber)::text))" > " 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.00= 6..0.006 > rows=3D1 loops=3D326)" > " Index Cond: (((companycode)::text =3D > '100'::text) AND ((subjectcode)::text =3D (a.subjectcode)::text))" > " -> Index Scan using ""cl_student_internal_mark_IX"" > on cl_student_internal_mark m (cost=3D0.42..2.94 rows=3D1 width=3D97) (a= ctual > time=3D0.010..0.010 rows=3D1 loops=3D326)" > " 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))" > " Filter: (((departmentheaderfk)::text =3D > '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D > '37A9BEC2638844FFD5B1422D83E70EF3'::text))" > " -> Index Scan using ""cl_student_semester_subject_IX1"" o= n > cl_student_semester_subject p (cost=3D0.42..3.09 rows=3D1 width=3D60) (a= ctual > time=3D0.010..0.010 rows=3D1 loops=3D326)" > " Index Cond: (((companycode)::text =3D '100'::text) A= ND > ((subjectcode)::text =3D (a.subjectcode)::text) AND > ((registrationnumber)::text =3D (a.registrationnumber)::text) AND (semest= er =3D > a.semester))" > " Filter: ((examheaderfk)::text =3D > 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::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)" > "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 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 > > --=20 Muhammad Ikram --000000000000291c6a061e1ff919 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

Could you perform diff o= n postgresql.conf file to see whether values are same for work_mem, shared_= buffers, maintenance_work_mem and other related parameters?

<= /div>
Regards,
Ikram


On Fri, Jul 26, 2024 at 10:31= =E2=80=AFAM sivapostgres@yahoo.co= m <sivapostgres@yahoo.com<= /a>> wrote:
<= div>
Hello,
Using PG 11.11, One P= G Cluster, Windows 2019 Server Standard, Two databases 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 database as second database (client_test).

Query:
=C2=A0Select a.examname, = a.registrationnumber, b.studentname, d.departmentname, e.levelname,=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 a.subjectcode, c.subjectname, b.regula= r, 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, coalesce= ((a.internalmark + a.externalmark),0) as total,=C2=A0
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 a.absent, a.malpractice, c.maxinternalmark, f.noofsemeste= r, a.examstudentstatus,=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case Wh= en 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.malpracti= ce =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.absent =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.examstudentstatu= s =3D 'S' Then 'S.L' 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 'F' Then 'N.P' Else=C2=A0
=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.result =3D 'F' and a.a= bsent =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' En= d End End End End End as res,=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 C= oncat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname))=C2=A0 a= s course,=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 a.revaluation, m.abse= nt 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.addresstwo,= =C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.semester > f.no= ofsemester 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.semest= er 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.noofsemester and a.sem= ester 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= .semester in (5,6) Then 'III - Year' End as studyr,=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0c.subjectserialno, p.regulararrear=C2=A0
<= div>=C2=A0From=C2=A0 cl_student_exam_subject=C2=A0 a=C2=A0
=C2=A0= Join=C2=A0 cl_student_name b=C2=A0
=C2=A0On=C2=A0 =C2=A0 b.compan= ycode =3D a.companycode=C2=A0
=C2=A0And=C2=A0 =C2=A0b.registratio= nnumber =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=A0And=C2=A0 =C2=A0c.subjectcode=C2=A0 =C2=A0 =3D a.sub= jectcode=C2=A0
=C2=A0Join=C2=A0 cl_department_header d=C2=A0
=C2=A0On=C2=A0 =C2=A0d.departmentheaderpk =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_departmen= t_detail f=C2=A0
=C2=A0On=C2=A0 =C2=A0f.departmentheaderfk =3D b.= departmentheaderfk=C2=A0
=C2=A0And=C2=A0 =C2=A0f.levelfk =3D b.le= velfk=C2=A0
=C2=A0Left Outer 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.registrationnumbe= r=C2=A0
=C2=A0And=C2=A0 =C2=A0m.subjectcode =3D a.subjectcode=C2= =A0
=C2=A0And=C2=A0 =C2=A0m.departmentheaderfk =3D b.departmenthe= aderfk=C2=A0
=C2=A0And=C2=A0 =C2=A0m.levelfk =3D b.levelfk=C2=A0<= /div>
=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.companyc= ode =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.company= code=C2=A0
=C2=A0And=C2=A0 =C2=A0p.examheaderfk =3D a.examheaderf= k=C2=A0
=C2=A0And=C2=A0 =C2=A0p.subjectcode=C2=A0 =3D a.subjectco= de=C2=A0
=C2=A0And=C2=A0 =C2=A0p.registrationnumber =3D a.registr= ationnumber=C2=A0
=C2=A0And=C2=A0 =C2=A0p.semester =3D a.semester= =C2=A0
=C2=A0Join=C2=A0 co_company_branch q=C2=A0
=C2= =A0On=C2=A0 =C2=A0 n.companycode =3D a.companycode=C2=A0
=C2=A0Wh= ere=C2=A0 a.companycode =3D '100'
=C2=A0And=C2=A0 =C2=A0 = a.examheaderfk =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'
=C2= =A0And=C2=A0 =C2=A0(a.examstudentstatus is null or a.examstudentstatus in (= 'R', 'S', 'W'))=C2=A0
=C2=A0And=C2=A0 b.d= epartmentheaderfk 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.degreeawarded), ' ', RTrim(d.de= partmentname)),=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Case Whe= n 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 and 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) Then 'III - Ye= ar' End,=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a.registrat= ionnumber, b.regular, p.semester desc, c.subjectserialno,=C2=A0
= =C2=A0 Case When c.subjectcategory =3D 'T' Then 1=C2= =A0
=C2=A0 =C2=A0 When c.subjectcategory =3D '= ;P' Then 2=C2=A0
=C2=A0 =C2=A0 When c.subject= category =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 <= /span>=C2=A0 =C2=A0 =C2=A0When Substring(a.subjectcode, 6, 1) =3D 'A= 9; Then 3=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0When Su= bstring(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) :<= /div>
"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.degreeawarde= d)::text), ' ', rtrim((d.departmentname)::text))), (CASE WHEN (a.se= mester > 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 - Year= '::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semester =3D A= NY ('{5,6}'::integer[]))) THEN 'III - Year'::text ELSE NULL= ::text END), a.registrationnumber, b.regular, a.semester DESC, c.subjectser= ialno, (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'::bp= char) THEN 4 WHEN (c.subjectcategory =3D 'J'::bpchar) THEN 5 ELSE N= ULL::integer END), c.ancillary, (CASE WHEN (""substring"&quo= t;((a.subjectcode)::text, 6, 1) =3D 'C'::text) THEN 1 WHEN ("&= quot;substring""((a.subjectcode)::text, 6, 1) =3D 'S'::te= xt) 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.s= ubjectcode"
"=C2=A0 Sort Method: quicksort=C2=A0 Memory= : 193kB"
"=C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D= 2.36..2862.34 rows=3D1 width=3D1088) (actual time=3D57829.857..451662.727 r= ows=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) (actu= al 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.registr= ationnumber)::text =3D (p.registrationnumber)::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 b= y Join Filter: 13614738"
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using ""cl_student_se= mester_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=3D4176= 4 loops=3D1)"
"=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 'BA80952CFF8F4E1C3F9F44B6= 2ED9BF37'::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..284= 8.39 rows=3D244 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 Filter: (((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 Nested Loop=C2=A0 (cost=3D1.11..2058.12 rows=3D244 width=3D746) (act= ual 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=3D699) (actual time=3D0.029..0.338 rows=3D313 l= oops=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_company n=C2=A0 (cost=3D0.0= 0..1.01 rows=3D1 width=3D394) (actual time=3D0.001..0.001 rows=3D1 loops=3D= 41764)"
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =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..310.07 row= s=3D172 width=3D305) (actual time=3D0.025..0.282 rows=3D313 loops=3D41764)&= quot;
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =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 Loo= p=C2=A0 (cost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.009..0.01= 3 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 width=3D200) (actual time=3D0.002..0.004 rows=3D= 1 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: ((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 Rem= oved 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_d= epartment_detail f=C2=A0 (cost=3D0.27..8.29 rows=3D1 width=3D78) (actual ti= me=3D0.007..0.007 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 Index Cond: (((= departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::t= ext) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::t= ext))"
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =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 Sca= n on cl_level e=C2=A0 (cost=3D0.00..1.09 rows=3D1 width=3D160) (actual time= =3D0.001..0.003 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 '37A9BE= C2638844FFD5B1422D83E70EF3'::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_studen= t_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) AN= D ((departmentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'= ;::text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'= ;::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_subject_IX1"" on cl_student_exam_subject a=C2=A0 (c= ost=3D0.42..10.13 rows=3D2 width=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 'BA80952CFF8F4E1C3F9F44B62ED9BF3= 7'::text) AND ((registrationnumber)::text =3D (b.registrationnumber)::t= ext))"
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =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 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=3D0.007..0.007 rows=3D1 loo= ps=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 I= ndex 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_in= ternal_mark m=C2=A0 (cost=3D0.42..2.90 rows=3D1 width=3D97) (actual time=3D= 0.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 ((comp= anycode)::text =3D '100'::text) AND ((subjectcode)::text =3D (a.sub= jectcode)::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: (((departmentheade= rfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((leve= lfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::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.0= 01..0.001 rows=3D1 loops=3D326)"
"Planning Time: 15.936= ms"
"Execution Time: 451672.059 ms"


Explain Analyze of Second DB (client_test)
<= div>"Sort=C2=A0 (cost=3D3454.91..3454.92 rows=3D1 width=3D1088) (actua= l time=3D19.120..19.137 rows=3D326 loops=3D1)"
"=C2=A0 = Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ', rtrim((d.dep= artmentname)::text))), (CASE WHEN (a.semester > f.noofsemester) THEN = 9;PRIVATE'::text WHEN ((a.semester <=3D f.noofsemester) AND (a.semes= ter =3D ANY ('{1,2}'::integer[]))) THEN 'I - Year'::text WH= EN ((a.semester <=3D f.noofsemester) 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.registrationnumber,= b.regular, a.semester DESC, c.subjectserialno, (CASE WHEN (c.subjectcatego= ry =3D 'T'::bpchar) THEN 1 WHEN (c.subjectcategory =3D 'P':= :bpchar) THEN 2 WHEN (c.subjectcategory =3D 'D'::bpchar) THEN 3 WHE= N (c.subjectcategory =3D 'V'::bpchar) THEN 4 WHEN (c.subjectcategor= y =3D 'J'::bpchar) THEN 5 ELSE NULL::integer END), c.ancillary, (CA= SE WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D= 'C'::text) THEN 1 WHEN (""substring""((a.subje= ctcode)::text, 6, 1) =3D 'S'::text) THEN 2 WHEN (""substr= ing""((a.subjectcode)::text, 6, 1) =3D 'A'::text) THEN 3 = WHEN (""substring""((a.subjectcode)::text, 6, 1) =3D &#= 39;E'::text) THEN 4 ELSE 5 END), a.subjectcode"
"= =C2=A0 Sort Method: quicksort=C2=A0 Memory: 193kB"
"=C2= =A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D2.23..3454.90 rows=3D1 width=3D10= 88) (actual time=3D0.319..14.984 rows=3D326 loops=3D1)"
&quo= t;=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D2.23..3= 453.78 rows=3D1 width=3D686) (actual time=3D0.298..13.691 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.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 Neste= d Loop Left Join=C2=A0 (cost=3D1.81..2739.81 rows=3D230 width=3D735) (actua= l 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.departmentheaderfk)::text) AND ((m.l= evelfk)::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 w= idth=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.6= 2 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 Nes= ted Loop=C2=A0 (cost=3D0.68..299.78 rows=3D166 width=3D699) (actual time=3D= 0.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 co_compan= y 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 '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..297.11 rows=3D166 width=3D305) (actual time=3D0.091..0.316 rows=3D= 313 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 (co= st=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 Nes= ted Loop=C2=A0 (cost=3D0.27..9.60 rows=3D1 width=3D278) (actual time=3D0.03= 4..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 Scan on cl_department_header d=C2= =A0 (cost=3D0.00..1.30 rows=3D1 width=3D200) (actual time=3D0.009..0.011 ro= ws=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 Rem= oved 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_d= epartment_detail f=C2=A0 (cost=3D0.27..8.29 rows=3D1 width=3D78) (actual ti= me=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 Cond: (((depa= rtmentheaderfk)::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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =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 o= n cl_level e=C2=A0 (cost=3D0.00..1.09 rows=3D1 width=3D160) (actual time=3D= 0.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 '37A9BEC263884= 4FFD5B1422D83E70EF3'::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 ((depart= mentheaderfk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::text) A= ND ((levelfk)::text =3D '37A9BEC2638844FFD5B1422D83E70EF3'::text) A= ND (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_studen= t_exam_subject_IX1"" on cl_student_exam_subject a=C2=A0 (cost=3D0= .42..10.15 rows=3D2 width=3D89) (actual time=3D0.010..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) AN= D ((examheaderfk)::text =3D 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::tex= t) AND ((registrationnumber)::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 IS NULL) OR (examstudentstatus =3D ANY (= 9;{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.3= 2 rows=3D1 width=3D53) (actual time=3D0.006..0.006 rows=3D1 loops=3D326)&qu= ot;
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =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: (((com= panycode)::text =3D '100'::text) AND ((subjectcode)::text =3D (a.su= bjectcode)::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 "&quo= t;cl_student_internal_mark_IX"" on cl_student_internal_mark m=C2= =A0 (cost=3D0.42..2.94 rows=3D1 width=3D97) (actual time=3D0.010..0.010 row= s=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: (((com= panycode)::text =3D (a.companycode)::text) AND ((companycode)::text =3D = 9;100'::text) AND ((subjectcode)::text =3D (a.subjectcode)::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 '04DF= 8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9= BEC2638844FFD5B1422D83E70EF3'::text))"
"=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using "&= quot;cl_student_semester_subject_IX1"" on cl_student_semester_sub= ject p=C2=A0 (cost=3D0.42..3.09 rows=3D1 width=3D60) (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 Index Cond: (((companycode)::= text =3D '100'::text) AND ((subjectcode)::text =3D (a.subjectcode):= :text) AND ((registrationnumber)::text =3D (a.registrationnumber)::text) AN= D (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)"
&quo= t;=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.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 aga= inst DB2 takes around 124 msec.=C2=A0=C2=A0

Same computer, same PG cluster, same query.
Why it takes so much time when run against DB1 (client_db)?
<= div dir=3D"ltr">
Already executed vacuum against = client_db database.=C2=A0=C2=A0

Any help is really appreciated.

Happiness Always
BKR Sivaprakash

<= /div>


--
Muhammad Ikram

--000000000000291c6a061e1ff919--