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
