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 1sXEFK-002Yvr-56 for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 06:16:06 +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 1sXEFI-006JGm-KY for pgsql-general@arkaria.postgresql.org; Fri, 26 Jul 2024 06:16:04 +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 1sXEFI-006JGe-38 for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 06:16:04 +0000 Received: from mail-lf1-x12b.google.com ([2a00:1450:4864:20::12b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sXEFE-001WWm-SC for pgsql-general@lists.postgresql.org; Fri, 26 Jul 2024 06:16:03 +0000 Received: by mail-lf1-x12b.google.com with SMTP id 2adb3069b0e04-52ef95ec938so1154493e87.3 for ; Thu, 25 Jul 2024 23:16:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721974560; x=1722579360; 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=0nORvErnLyJBYe95uRZT5ql71+O5auEF9RPZYuhYMnk=; b=ncSsp4+lhu0MDnFP5UmclS9qpxrDmvqXxNvkREFxLcDYXmTKo8pRG8uL2PB82tstQU j/iqUJTDCsiwSJabh4GZXm/x27AsDYg1a9dobKBJDzvq2nOqalDjUs6Ba3NjobaJRWQQ FdZWH8kVzjNUWajqsUEYEhBCzE+o06i5xMPZihw1owkfEBUMjoE6PCtaP5+ipm1OZT0i iehpbPIUjwEtMrSomf1wqBWImYncGh0mGBhiWHLta7UaBESWppc1FgsMPYi8/x5bCISO ri8HEtcM58NIfaegMFPVysllZN+rMBsOhWAi0D0pjQJd9ETpgcpszp8YOqqPQepDaSJ3 4FCg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721974560; x=1722579360; 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=0nORvErnLyJBYe95uRZT5ql71+O5auEF9RPZYuhYMnk=; b=p18ZQi2idc07auTj2MuJeAyKfqWVdyTFd3jvWyKU+M89cLmELVV5dv2V96X9gnwQjG FmpO+cF1LrHFUHkLxPM3tYu2SqB+yffCtms9UEm3JtPk454FppPV65ZMHMKudhCEGvUc rovifd9X8Uu2hA1RPgcNlPvKYC00PR5BJ7ZxTXNdiv1hQbHY6IIHlsz6ea+4XFFNJwiP lW1HWnrE5xJOq0mS5TE0iIvXZarTnyqWcUSXPlJEgCyEFFKVoUCFqG3Z76meqAO380/l fMj/YLYLs6QUOWzDSBNSA/l/qb6UHu70gr2Y6KrUvbOC7Awo5iYBU8yYidLURXwCHprd 8/9Q== X-Gm-Message-State: AOJu0YygnsYkymLA1MJsKCVOmFmLfPRrmyRMNDadjD63iDvCp4qsXCF2 kPcHY5RisOOsMg1m+pSR9m/5sYBIZYDSLdVcEHZ9b++Lo2GTdhrdPrj+KmdAUqImJr/x7V+w4Xz BSZgxYoMzIXfiook/T+NheukI9rxf4k+7 X-Google-Smtp-Source: AGHT+IHVbDfPUpp5qWIc1sXuQ8OlcP4D9S8vfdfww5ac8CsVau0uSnVcYDFSeNuV6BZeLY3qHJzdB1pgvRKbKXr5tNo= X-Received: by 2002:a05:6512:39c9:b0:52c:9906:fa33 with SMTP id 2adb3069b0e04-52fd3f8ec4emr3337491e87.43.1721974559573; Thu, 25 Jul 2024 23:15:59 -0700 (PDT) MIME-Version: 1.0 References: <431223779.107307.1721971823963.ref@mail.yahoo.com> <431223779.107307.1721971823963@mail.yahoo.com> <40060236.111802.1721973938706@mail.yahoo.com> In-Reply-To: <40060236.111802.1721973938706@mail.yahoo.com> From: Muhammad Ikram Date: Fri, 26 Jul 2024 11:15:42 +0500 Message-ID: Subject: Re: Slow performance To: "sivapostgres@yahoo.com" Cc: Postgresql General Group Content-Type: multipart/alternative; boundary="000000000000ef8f40061e207147" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ef8f40061e207147 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Again, I have not gone through your explain plans for both, will it be possible for you to take diff of the both plans. It will give some insight about how things are going and where the problem is. Alternatively I will suggest Reindex and execute ANALYZE command to regenerate stats . Regards, Ikram On Fri, Jul 26, 2024 at 11:05=E2=80=AFAM sivapostgres@yahoo.com < sivapostgres@yahoo.com> wrote: > I've only one instance of PG in that server. Means only one > postgresql.conf for both databases. > > > On Friday, 26 July, 2024 at 11:12:34 am IST, Muhammad Ikram < > mmikram@gmail.com> wrote: > > > 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 > > > > -- > Muhammad Ikram > > --=20 Muhammad Ikram --000000000000ef8f40061e207147 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Again,

I have not gone th= rough your explain plans for both, will it be possible for you to take diff= of the both plans. It will give some insight about how things are going an= d where the problem is.

Alternatively

I will suggest Reindex and=C2=A0 execute ANALYZE command t= o regenerate stats .

Regards,
Ikram


On Fri, Jul 26, 2024 at 11:05=E2=80=AFAM sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:
I've only one instance of PG in that server.= =C2=A0 Means only one postgresql.conf for both databases.


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


=20 =20
<= div dir=3D"ltr">
Hi,

Could you = perform diff on postgresql.conf file to see whether values are same for wor= k_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 &l= t;sivapostgres@yahoo.com> wrote= :
Hello,
Using PG 11.11, One PG Cluster, Windows 2= 019 Server Standard, Two databases with identical data.

1.=C2=A0 First DB:=C2=A0 clien= t_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_t= est).

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.regular, a.semester, a.du= mmynumber, 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.ab= sent, a.malpractice, c.maxinternalmark, f.noofsemester, a.examstudentstatus= ,=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' an= d 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 &#= 39;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&= #39; and a.absent =3D 'Y' and a.examstudentstatus =3D 'S' T= hen 'S.L' Else=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Case Whe= n a.result =3D 'F' and a.absent =3D 'Y' and a.examstudentst= atus =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= 9; 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.degr= eeawarded), ' ', RTrim(d.departmentname))=C2=A0 as course,=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 a.revaluation, m.absent as int_abs, n.c= ompanyname, 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.noofsemester Then &= #39;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 &= #39;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.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.semester in (5,6) Th= en '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_exam_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.companyco= de=C2=A0
=C2=A0And=C2=A0 =C2=A0b.registrationnumber =3D a.registr= ationnumber=C2=A0
=C2=A0Join=C2=A0 cl_subject 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.subjectcode=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=A0= Join=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_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.levelfk=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.registrationnumber=C2=A0
=C2= =A0And=C2=A0 =C2=A0m.subjectcode =3D a.subjectcode=C2=A0
=C2=A0An= d=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_compa= ny 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=A0And=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_branch q=C2=A0
=C2=A0On=C2=A0 =C2=A0 = n.companycode =3D a.companycode=C2=A0
=C2=A0Where=C2=A0 a.company= code =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.departmentheaderfk i= n ('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.departmentname)),= =C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Case When a.semester &g= t; 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.noofsemeste= r 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.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=A0 =C2=A0When a.semester &l= t;=3D f.noofsemester and a.semester in (5,6) Then 'III - Year' End,= =C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 a.registrationnumber, 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.subjectcategory =3D = 'D' Then 3=C2=A0
=C2=A0 =C2=A0 When c.sub= jectcategory =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 Ca= se 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.su= bjectcode, 6, 1) =3D 'E' Then 4 Else 5 End,=C2=A0
=C2=A0<= span> =C2=A0 =C2=A0a.subjectcode=C2=A0


Explain Analyze of D= B 1 (client_db) :
"Sort=C2=A0 (cost=3D= 2862.35..2862.36 rows=3D1 width=3D1088) (actual time=3D451671.464..451671.4= 95 rows=3D326 loops=3D1)"
"=C2=A0 Sort Key: (concat(rtr= im((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 - Year'::text WHEN ((a.semester <=3D f.noofsemester) AN= D (a.semester =3D ANY ('{5,6}'::integer[]))) THEN 'III - Year&#= 39;::text ELSE NULL::text END), a.registrationnumber, b.regular, a.semester= DESC, c.subjectserialno, (CASE WHEN (c.subjectcategory =3D 'T'::bp= char) 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'::bp= char) THEN 5 ELSE NULL::integer END), c.ancillary, (CASE WHEN (""= substring""((a.subjectcode)::text, 6, 1) =3D 'C'::text) T= HEN 1 WHEN (""substring""((a.subjectcode)::text, 6, 1) = =3D 'S'::text) THEN 2 WHEN (""substring""((a.su= bjectcode)::text, 6, 1) =3D 'A'::text) THEN 3 WHEN (""sub= string""((a.subjectcode)::text, 6, 1) =3D 'E'::text) THEN= 4 ELSE 5 END), a.subjectcode"
"=C2=A0 Sort Method: qui= cksort=C2=A0 Memory: 193kB"
"=C2=A0 ->=C2=A0 Nested = Loop=C2=A0 (cost=3D2.36..2862.34 rows=3D1 width=3D1088) (actual time=3D5782= 9.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)&qu= ot;
"=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) AND (a.semester =3D p.se= mester))"
"=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 &quo= t;"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.03= 3..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: (((company= code)::text =3D '100'::text) AND ((examheaderfk)::text =3D 'BA8= 0952CFF8F4E1C3F9F44B62ED9BF37'::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=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.departme= ntheaderfk)::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) (ac= tual 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) (actual time=3D0.045..4.299 rows=3D326 loops=3D41764)&q= uot;
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =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..312.80 rows=3D172 width=3D699) (actual time=3D= 0.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_co= mpany 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: ((compa= nycode)::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 time=3D0.025..0.282 r= ows=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 Loo= p=C2=A0 (cost=3D0.27..10.70 rows=3D1 width=3D438) (actual time=3D0.013..0.0= 19 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 width=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: ((de= partmentheaderpk)::text =3D '04DF8BD89D0844DD4D8AA151EFB28657'::tex= t)"
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =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.007..0.007 rows=3D1 loops=3D41764)&q= uot;
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =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 '04DF8BD= 89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC= 2638844FFD5B1422D83E70EF3'::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 r= ows=3D1 width=3D160) (actual time=3D0.001..0.003 rows=3D1 loops=3D41764)&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 =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 Rem= oved 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=3D= 172 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 '04DF= 8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text =3D '37A9= BEC2638844FFD5B1422D83E70EF3'::text) AND (status =3D 'A'::bpcha= r))"
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =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.13 rows=3D2 width=3D89) (actu= al 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: (((comp= anycode)::text =3D '100'::text) AND ((examheaderfk)::text =3D '= BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND ((registrationnumber)::tex= t =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 t= ime=3D0.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))"<= /div>
"=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_mar= k_IX"" on cl_student_internal_mark m=C2=A0 (cost=3D0.42..2.90 row= s=3D1 width=3D97) (actual time=3D0.011..0.011 rows=3D1 loops=3D13615064)&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 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))"
"=C2=A0 =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 '04DF8BD89D0844DD4D8AA1= 51EFB28657'::text) AND ((levelfk)::text =3D '37A9BEC2638844FFD5B142= 2D83E70EF3'::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=3D= 1 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)
<= div>
"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) T= HEN '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 (= 9;{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.registrationnu= mber, b.regular, a.semester DESC, c.subjectserialno, (CASE WHEN (c.subjectc= ategory =3D 'T'::bpchar) THEN 1 WHEN (c.subjectcategory =3D 'P&= #39;::bpchar) THEN 2 WHEN (c.subjectcategory =3D 'D'::bpchar) THEN = 3 WHEN (c.subjectcategory =3D 'V'::bpchar) THEN 4 WHEN (c.subjectca= tegory =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 (""s= ubstring""((a.subjectcode)::text, 6, 1) =3D 'A'::text) TH= EN 3 WHEN (""substring""((a.subjectcode)::text, 6, 1) = =3D 'E'::text) THEN 4 ELSE 5 END), a.subjectcode"
&q= uot;=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= =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 rows=3D326 l= oops=3D1)"
"=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Join Filter: (((b.registrationnumber)::text =3D (p.registrationnumbe= r)::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 Ne= sted Loop Left Join=C2=A0 (cost=3D1.81..2739.81 rows=3D230 width=3D735) (ac= tual 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.= levelfk)::text =3D (b.levelfk)::text) AND (m.regular =3D b.regular))"<= /div>
"=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 qu= ery 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 compute= r, same PG cluster, same query.
Why it takes so much = time when run against DB1 (client_db)?

Already executed vacuum against client_db datab= ase.=C2=A0=C2=A0

Any help is really appreciated.

Happiness Always
BKR Sivapraka= sh

=


--
Muhammad Ikram



--
Muhammad Ikram

--000000000000ef8f40061e207147--