Re: Slow performance

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.internalmark + a.externalmark),0) as total,         a.absent, a.malpractice, c.maxinternalmark, f.noofsemester, a.examstudentstatus,         Case When a.result = 'P'  Then 'P' Else         Case When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is null Then 'M' Else         Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'R' Then 'R.C' Else         Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' Else         Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' Else         Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = '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 <= f.noofsemester and a.semester in (1,2) Then 'I - Year'              When a.semester <= f.noofsemester and a.semester in (3,4) Then 'II - Year'              When a.semester <= 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 = a.companycode  And   b.registrationnumber = a.registrationnumber  Join  cl_subject c  On    c.companycode = a.companycode  And   c.subjectcode    = a.subjectcode  Join  cl_department_header d  On   d.departmentheaderpk = b.departmentheaderfk  Join  cl_level e  On   e.levelpk = b.Levelfk  Join  cl_department_detail f  On   f.departmentheaderfk = b.departmentheaderfk  And   f.levelfk = b.levelfk  Left Outer Join cl_student_internal_mark m  On    m.companycode = a.companycode  And   m.registrationnumber = a.registrationnumber  And   m.subjectcode = a.subjectcode  And   m.departmentheaderfk = b.departmentheaderfk  And   m.levelfk = b.levelfk  And   m.Regular = b.Regular  Join  co_company n  On    n.companycode = a.companycode  Join  cl_student_semester_subject p  On    p.companycode = a.companycode  And   p.examheaderfk = a.examheaderfk  And   p.subjectcode  = a.subjectcode  And   p.registrationnumber = a.registrationnumber  And   p.semester = a.semester  Join  co_company_branch q  On    n.companycode = a.companycode  Where  a.companycode = '100' And    a.examheaderfk = '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 = 'A'  Order By Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)),           Case When a.semester > f.noofsemester Then 'PRIVATE'                When a.semester <= f.noofsemester and a.semester in (1,2) Then 'I - Year'                When a.semester <= f.noofsemester and a.semester in (3,4) Then 'II - Year'                When a.semester <= 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 = 'T' Then 1     When c.subjectcategory = 'P' Then 2     When c.subjectcategory = 'D' Then 3     When c.subjectcategory = 'V' Then 4     When c.subjectcategory = 'J' Then 5 End,   c.ancillary,   Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1        When Substring(a.subjectcode, 6, 1) = 'S' Then 2        When Substring(a.subjectcode, 6, 1) = 'A' Then 3        When Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End,      a.subjectcode 

Explain Analyze of DB 1 (client_db) :"Sort  (cost=2862.35..2862.36 rows=1 width=1088) (actual time=451671.464..451671.495 rows=326 loops=1)""  Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ', rtrim((d.departmentname)::text))), (CASE WHEN (a.semester > f.noofsemester) THEN 'PRIVATE'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY ('{1,2}'::integer[]))) THEN 'I - Year'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY ('{3,4}'::integer[]))) THEN 'II - Year'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY ('{5,6}'::integer[]))) THEN 'III - Year'::text ELSE NULL::text END), a.registrationnumber, b.regular, a.semester DESC, c.subjectserialno, (CASE WHEN (c.subjectcategory = 'T'::bpchar) THEN 1 WHEN (c.subjectcategory = 'P'::bpchar) THEN 2 WHEN (c.subjectcategory = 'D'::bpchar) THEN 3 WHEN (c.subjectcategory = 'V'::bpchar) THEN 4 WHEN (c.subjectcategory = 'J'::bpchar) THEN 5 ELSE NULL::integer END), c.ancillary, (CASE WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'C'::text) THEN 1 WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'S'::text) THEN 2 WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'A'::text) THEN 3 WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'E'::text) THEN 4 ELSE 5 END), a.subjectcode""  Sort Method: quicksort  Memory: 193kB""  ->  Nested Loop  (cost=2.36..2862.34 rows=1 width=1088) (actual time=57829.857..451662.727 rows=326 loops=1)""        ->  Nested Loop  (cost=2.36..2861.23 rows=1 width=686) (actual time=57829.829..451658.085 rows=326 loops=1)""              Join Filter: (((b.registrationnumber)::text = (p.registrationnumber)::text) AND ((c.subjectcode)::text = (p.subjectcode)::text) AND (a.semester = p.semester))""              Rows Removed by Join Filter: 13614738""              ->  Index Scan using ""cl_student_semester_subject_IX3"" on cl_student_semester_subject p  (cost=0.55..8.57 rows=1 width=60) (actual time=0.033..55.702 rows=41764 loops=1)""                    Index Cond: (((companycode)::text = '100'::text) AND ((examheaderfk)::text = 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text))""              ->  Nested Loop Left Join  (cost=1.81..2848.39 rows=244 width=735) (actual time=0.068..10.768 rows=326 loops=41764)""                    Join Filter: (((m.departmentheaderfk)::text = (b.departmentheaderfk)::text) AND ((m.levelfk)::text = (b.levelfk)::text) AND (m.regular = b.regular))""                    ->  Nested Loop  (cost=1.39..2135.32 rows=244 width=795) (actual time=0.053..6.723 rows=326 loops=41764)""                          ->  Nested Loop  (cost=1.11..2058.12 rows=244 width=746) (actual time=0.045..4.299 rows=326 loops=41764)""                                ->  Nested Loop  (cost=0.68..312.80 rows=172 width=699) (actual time=0.029..0.338 rows=313 loops=41764)""                                      ->  Seq Scan on co_company n  (cost=0.00..1.01 rows=1 width=394) (actual time=0.001..0.001 rows=1 loops=41764)""                                            Filter: ((companycode)::text = '100'::text)""                                      ->  Nested Loop  (cost=0.68..310.07 rows=172 width=305) (actual time=0.025..0.282 rows=313 loops=41764)""                                            ->  Nested Loop  (cost=0.27..10.70 rows=1 width=438) (actual time=0.013..0.019 rows=1 loops=41764)""                                                  ->  Nested Loop  (cost=0.27..9.60 rows=1 width=278) (actual time=0.009..0.013 rows=1 loops=41764)""                                                        ->  Seq Scan on cl_department_header d  (cost=0.00..1.30 rows=1 width=200) (actual time=0.002..0.004 rows=1 loops=41764)""                                                              Filter: ((departmentheaderpk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text)""                                                              Rows Removed by Filter: 23""                                                        ->  Index Scan using cl_department_detail_ix1 on cl_department_detail f  (cost=0.27..8.29 rows=1 width=78) (actual time=0.007..0.007 rows=1 loops=41764)""                                                              Index Cond: (((departmentheaderfk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text))""                                                  ->  Seq Scan on cl_level e  (cost=0.00..1.09 rows=1 width=160) (actual time=0.001..0.003 rows=1 loops=41764)""                                                        Filter: ((levelpk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text)""                                                        Rows Removed by Filter: 6""                                            ->  Index Scan using cl_student_name_ix4 on cl_student_name b  (cost=0.41..297.65 rows=172 width=97) (actual time=0.011..0.165 rows=313 loops=41764)""                                                  Index Cond: (((companycode)::text = '100'::text) AND ((departmentheaderfk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text) AND (status = 'A'::bpchar))""                                ->  Index Scan using ""cl_student_exam_subject_IX1"" on cl_student_exam_subject a  (cost=0.42..10.13 rows=2 width=89) (actual time=0.012..0.012 rows=1 loops=13072132)""                                      Index Cond: (((companycode)::text = '100'::text) AND ((examheaderfk)::text = 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND ((registrationnumber)::text = (b.registrationnumber)::text))""                                      Filter: ((examstudentstatus IS NULL) OR (examstudentstatus = ANY ('{R,S,W}'::bpchar[])))""                          ->  Index Scan using cl_subject_ix3 on cl_subject c  (cost=0.28..0.32 rows=1 width=53) (actual time=0.007..0.007 rows=1 loops=13615064)""                                Index Cond: (((companycode)::text = '100'::text) AND ((subjectcode)::text = (a.subjectcode)::text))""                    ->  Index Scan using ""cl_student_internal_mark_IX"" on cl_student_internal_mark m  (cost=0.42..2.90 rows=1 width=97) (actual time=0.011..0.011 rows=1 loops=13615064)""                          Index Cond: (((companycode)::text = (a.companycode)::text) AND ((companycode)::text = '100'::text) AND ((subjectcode)::text = (a.subjectcode)::text) AND ((registrationnumber)::text = (a.registrationnumber)::text))""                          Filter: (((departmentheaderfk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text))""        ->  Seq Scan on co_company_branch q  (cost=0.00..1.01 rows=1 width=276) (actual time=0.001..0.001 rows=1 loops=326)""Planning Time: 15.936 ms""Execution Time: 451672.059 ms"

Explain Analyze of Second DB (client_test)"Sort  (cost=3454.91..3454.92 rows=1 width=1088) (actual time=19.120..19.137 rows=326 loops=1)""  Sort Key: (concat(rtrim((f.degreeawarded)::text), ' ', rtrim((d.departmentname)::text))), (CASE WHEN (a.semester > f.noofsemester) THEN 'PRIVATE'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY ('{1,2}'::integer[]))) THEN 'I - Year'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY ('{3,4}'::integer[]))) THEN 'II - Year'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY ('{5,6}'::integer[]))) THEN 'III - Year'::text ELSE NULL::text END), a.registrationnumber, b.regular, a.semester DESC, c.subjectserialno, (CASE WHEN (c.subjectcategory = 'T'::bpchar) THEN 1 WHEN (c.subjectcategory = 'P'::bpchar) THEN 2 WHEN (c.subjectcategory = 'D'::bpchar) THEN 3 WHEN (c.subjectcategory = 'V'::bpchar) THEN 4 WHEN (c.subjectcategory = 'J'::bpchar) THEN 5 ELSE NULL::integer END), c.ancillary, (CASE WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'C'::text) THEN 1 WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'S'::text) THEN 2 WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'A'::text) THEN 3 WHEN (""substring""((a.subjectcode)::text, 6, 1) = 'E'::text) THEN 4 ELSE 5 END), a.subjectcode""  Sort Method: quicksort  Memory: 193kB""  ->  Nested Loop  (cost=2.23..3454.90 rows=1 width=1088) (actual time=0.319..14.984 rows=326 loops=1)""        ->  Nested Loop  (cost=2.23..3453.78 rows=1 width=686) (actual time=0.298..13.691 rows=326 loops=1)""              Join Filter: (((b.registrationnumber)::text = (p.registrationnumber)::text) AND ((c.subjectcode)::text = (p.subjectcode)::text))""              ->  Nested Loop Left Join  (cost=1.81..2739.81 rows=230 width=735) (actual time=0.259..9.970 rows=326 loops=1)""                    Join Filter: (((m.departmentheaderfk)::text = (b.departmentheaderfk)::text) AND ((m.levelfk)::text = (b.levelfk)::text) AND (m.regular = b.regular))""                    ->  Nested Loop  (cost=1.39..2060.47 rows=230 width=795) (actual time=0.233..6.232 rows=326 loops=1)""                          ->  Nested Loop  (cost=1.11..1987.62 rows=230 width=746) (actual time=0.211..3.955 rows=326 loops=1)""                                ->  Nested Loop  (cost=0.68..299.78 rows=166 width=699) (actual time=0.118..0.393 rows=313 loops=1)""                                      ->  Seq Scan on co_company n  (cost=0.00..1.01 rows=1 width=394) (actual time=0.026..0.027 rows=1 loops=1)""                                            Filter: ((companycode)::text = '100'::text)""                                      ->  Nested Loop  (cost=0.68..297.11 rows=166 width=305) (actual time=0.091..0.316 rows=313 loops=1)""                                            ->  Nested Loop  (cost=0.27..10.70 rows=1 width=438) (actual time=0.041..0.048 rows=1 loops=1)""                                                  ->  Nested Loop  (cost=0.27..9.60 rows=1 width=278) (actual time=0.034..0.038 rows=1 loops=1)""                                                        ->  Seq Scan on cl_department_header d  (cost=0.00..1.30 rows=1 width=200) (actual time=0.009..0.011 rows=1 loops=1)""                                                              Filter: ((departmentheaderpk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text)""                                                              Rows Removed by Filter: 23""                                                        ->  Index Scan using cl_department_detail_ix1 on cl_department_detail f  (cost=0.27..8.29 rows=1 width=78) (actual time=0.024..0.024 rows=1 loops=1)""                                                              Index Cond: (((departmentheaderfk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text))""                                                  ->  Seq Scan on cl_level e  (cost=0.00..1.09 rows=1 width=160) (actual time=0.006..0.008 rows=1 loops=1)""                                                        Filter: ((levelpk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text)""                                                        Rows Removed by Filter: 6""                                            ->  Index Scan using cl_student_name_ix4 on cl_student_name b  (cost=0.41..284.75 rows=166 width=97) (actual time=0.049..0.184 rows=313 loops=1)""                                                  Index Cond: (((companycode)::text = '100'::text) AND ((departmentheaderfk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text) AND (status = 'A'::bpchar))""                                ->  Index Scan using ""cl_student_exam_subject_IX1"" on cl_student_exam_subject a  (cost=0.42..10.15 rows=2 width=89) (actual time=0.010..0.011 rows=1 loops=313)""                                      Index Cond: (((companycode)::text = '100'::text) AND ((examheaderfk)::text = 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text) AND ((registrationnumber)::text = (b.registrationnumber)::text))""                                      Filter: ((examstudentstatus IS NULL) OR (examstudentstatus = ANY ('{R,S,W}'::bpchar[])))""                          ->  Index Scan using cl_subject_ix3 on cl_subject c  (cost=0.28..0.32 rows=1 width=53) (actual time=0.006..0.006 rows=1 loops=326)""                                Index Cond: (((companycode)::text = '100'::text) AND ((subjectcode)::text = (a.subjectcode)::text))""                    ->  Index Scan using ""cl_student_internal_mark_IX"" on cl_student_internal_mark m  (cost=0.42..2.94 rows=1 width=97) (actual time=0.010..0.010 rows=1 loops=326)""                          Index Cond: (((companycode)::text = (a.companycode)::text) AND ((companycode)::text = '100'::text) AND ((subjectcode)::text = (a.subjectcode)::text) AND ((registrationnumber)::text = (a.registrationnumber)::text))""                          Filter: (((departmentheaderfk)::text = '04DF8BD89D0844DD4D8AA151EFB28657'::text) AND ((levelfk)::text = '37A9BEC2638844FFD5B1422D83E70EF3'::text))""              ->  Index Scan using ""cl_student_semester_subject_IX1"" on cl_student_semester_subject p  (cost=0.42..3.09 rows=1 width=60) (actual time=0.010..0.010 rows=1 loops=326)""                    Index Cond: (((companycode)::text = '100'::text) AND ((subjectcode)::text = (a.subjectcode)::text) AND ((registrationnumber)::text = (a.registrationnumber)::text) AND (semester = a.semester))""                    Filter: ((examheaderfk)::text = 'BA80952CFF8F4E1C3F9F44B62ED9BF37'::text)""        ->  Seq Scan on co_company_branch q  (cost=0.00..1.01 rows=1 width=276) (actual time=0.000..0.000 rows=1 loops=326)""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