Thread: 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
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
BKR Sivaprakash
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
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_testTook 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' ElseCase When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is null Then 'M' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'R' Then 'R.C' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' ElseCase 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.regulararrearFrom cl_student_exam_subject aJoin cl_student_name bOn b.companycode = a.companycodeAnd b.registrationnumber = a.registrationnumberJoin cl_subject cOn c.companycode = a.companycodeAnd c.subjectcode = a.subjectcodeJoin cl_department_header dOn d.departmentheaderpk = b.departmentheaderfkJoin cl_level eOn e.levelpk = b.LevelfkJoin cl_department_detail fOn f.departmentheaderfk = b.departmentheaderfkAnd f.levelfk = b.levelfkLeft Outer Join cl_student_internal_mark mOn m.companycode = a.companycodeAnd m.registrationnumber = a.registrationnumberAnd m.subjectcode = a.subjectcodeAnd m.departmentheaderfk = b.departmentheaderfkAnd m.levelfk = b.levelfkAnd m.Regular = b.RegularJoin co_company nOn n.companycode = a.companycodeJoin cl_student_semester_subject pOn p.companycode = a.companycodeAnd p.examheaderfk = a.examheaderfkAnd p.subjectcode = a.subjectcodeAnd p.registrationnumber = a.registrationnumberAnd p.semester = a.semesterJoin co_company_branch qOn n.companycode = a.companycodeWhere 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 1When c.subjectcategory = 'P' Then 2When c.subjectcategory = 'D' Then 3When c.subjectcategory = 'V' Then 4When c.subjectcategory = 'J' Then 5 End,c.ancillary,Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1When Substring(a.subjectcode, 6, 1) = 'S' Then 2When Substring(a.subjectcode, 6, 1) = 'A' Then 3When Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End,a.subjectcodeExplain 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
--
Muhammad Ikram
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
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_testTook 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' ElseCase When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is null Then 'M' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'R' Then 'R.C' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' ElseCase 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.regulararrearFrom cl_student_exam_subject aJoin cl_student_name bOn b.companycode = a.companycodeAnd b.registrationnumber = a.registrationnumberJoin cl_subject cOn c.companycode = a.companycodeAnd c.subjectcode = a.subjectcodeJoin cl_department_header dOn d.departmentheaderpk = b.departmentheaderfkJoin cl_level eOn e.levelpk = b.LevelfkJoin cl_department_detail fOn f.departmentheaderfk = b.departmentheaderfkAnd f.levelfk = b.levelfkLeft Outer Join cl_student_internal_mark mOn m.companycode = a.companycodeAnd m.registrationnumber = a.registrationnumberAnd m.subjectcode = a.subjectcodeAnd m.departmentheaderfk = b.departmentheaderfkAnd m.levelfk = b.levelfkAnd m.Regular = b.RegularJoin co_company nOn n.companycode = a.companycodeJoin cl_student_semester_subject pOn p.companycode = a.companycodeAnd p.examheaderfk = a.examheaderfkAnd p.subjectcode = a.subjectcodeAnd p.registrationnumber = a.registrationnumberAnd p.semester = a.semesterJoin co_company_branch qOn n.companycode = a.companycodeWhere 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 1When c.subjectcategory = 'P' Then 2When c.subjectcategory = 'D' Then 3When c.subjectcategory = 'V' Then 4When c.subjectcategory = 'J' Then 5 End,c.ancillary,Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1When Substring(a.subjectcode, 6, 1) = 'S' Then 2When Substring(a.subjectcode, 6, 1) = 'A' Then 3When Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End,a.subjectcodeExplain 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
--
Muhammad Ikram
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
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,IkramHello,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_testTook 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' ElseCase When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is null Then 'M' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'R' Then 'R.C' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' ElseCase When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' ElseCase 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.regulararrearFrom cl_student_exam_subject aJoin cl_student_name bOn b.companycode = a.companycodeAnd b.registrationnumber = a.registrationnumberJoin cl_subject cOn c.companycode = a.companycodeAnd c.subjectcode = a.subjectcodeJoin cl_department_header dOn d.departmentheaderpk = b.departmentheaderfkJoin cl_level eOn e.levelpk = b.LevelfkJoin cl_department_detail fOn f.departmentheaderfk = b.departmentheaderfkAnd f.levelfk = b.levelfkLeft Outer Join cl_student_internal_mark mOn m.companycode = a.companycodeAnd m.registrationnumber = a.registrationnumberAnd m.subjectcode = a.subjectcodeAnd m.departmentheaderfk = b.departmentheaderfkAnd m.levelfk = b.levelfkAnd m.Regular = b.RegularJoin co_company nOn n.companycode = a.companycodeJoin cl_student_semester_subject pOn p.companycode = a.companycodeAnd p.examheaderfk = a.examheaderfkAnd p.subjectcode = a.subjectcodeAnd p.registrationnumber = a.registrationnumberAnd p.semester = a.semesterJoin co_company_branch qOn n.companycode = a.companycodeWhere 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 1When c.subjectcategory = 'P' Then 2When c.subjectcategory = 'D' Then 3When c.subjectcategory = 'V' Then 4When c.subjectcategory = 'J' Then 5 End,c.ancillary,Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1When Substring(a.subjectcode, 6, 1) = 'S' Then 2When Substring(a.subjectcode, 6, 1) = 'A' Then 3When Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End,a.subjectcodeExplain 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
--Muhammad Ikram
--
Muhammad Ikram
Hello: On Fri, 26 Jul 2024 at 07:31, sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote: ... > Took backup (pg_dump) of first database (client_db) and restored the database as second database (client_test). ... > 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)? Can be bad luck, but the usual suspect would be different databases. I assume db1 is quiescent on the tests ( as it seems the production database, no heavy querying concurrent with your tests ). Bear in mind restoring leaves the database similar to what a vacuum full will do, so it can differ a lot from the original. > Already executed vacuum against client_db database. I think you already have pointed out this, but IIRC you have not told us if you have ANALYZED any of the databases. This is important. Bad stats in any of them could make the planner choose a bad plan ( or, if you are unlucky, make it choose a bad one ). Also, did you vacuum verbose? where your tables well packed? ( bad vacuuming can lead to huge tables with a lot of free space, but I doubt this is your case, but everything has to be checked, we only know what you write us ). And now, not being an expert in tracing explain I see this in plan-db1: " 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))" Not an explain expert, but if i read correctly an index scan expecting 1 row recovers 41674, which hints at bad statistics ( or skewed data distribution and bad luck ) The plans are similar, but in the fast query cl_student_semester_subject is accessed using other index: " -> 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))" Which seems much more selective and recovers just what it wants. I would start by analyzing ( and, if not too costly, reindexing ) that table. Francisco Olarte.
Hi Siva,
pg_dump taken from client_db seems not to have transferred full data to client_test.
When I examine the query plans, the rows scanned seem to be extremely different. For example, there is 1 row in cl_level table on client_test, while clined_db shows that around 300,000 records were scanned.
My suggestion would be to check the data counts in the tables you moved with count(*).
When I examine the query plans, the rows scanned seem to be extremely different. For example, there is 1 row in cl_level table on client_test, while clined_db shows that around 300,000 records were scanned.
My suggestion would be to check the data counts in the tables you moved with count(*).
Francisco Olarte <folarte@peoplecall.com>, 26 Tem 2024 Cum, 10:55 tarihinde şunu yazdı:
Hello:
On Fri, 26 Jul 2024 at 07:31, sivapostgres@yahoo.com
<sivapostgres@yahoo.com> wrote:
...
> Took backup (pg_dump) of first database (client_db) and restored the database as second database (client_test).
...
> 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)?
Can be bad luck, but the usual suspect would be different databases.
I assume db1 is quiescent on the tests ( as it seems the production
database, no heavy querying concurrent with your tests ).
Bear in mind restoring leaves the database similar to what a vacuum
full will do, so it can differ a lot from the original.
> Already executed vacuum against client_db database.
I think you already have pointed out this, but IIRC you have not told
us if you have ANALYZED any of the databases. This is important. Bad
stats in any of them could make the planner choose a bad plan ( or, if
you are unlucky, make it choose a bad one ).
Also, did you vacuum verbose? where your tables well packed? ( bad
vacuuming can lead to huge tables with a lot of free space, but I
doubt this is your case, but everything has to be checked, we only
know what you write us ).
And now, not being an expert in tracing explain I see this in plan-db1:
" 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))"
Not an explain expert, but if i read correctly an index scan expecting
1 row recovers 41674, which hints at bad statistics ( or skewed data
distribution and bad luck )
The plans are similar, but in the fast query
cl_student_semester_subject is accessed using other index:
" -> 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))"
Which seems much more selective and recovers just what it wants.
I would start by analyzing ( and, if not too costly, reindexing ) that table.
Francisco Olarte.
On Fri, 26 Jul 2024 at 19:55, Francisco Olarte <folarte@peoplecall.com> wrote: > " -> 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))" > > Not an explain expert, but if i read correctly an index scan expecting > 1 row recovers 41674, which hints at bad statistics ( or skewed data > distribution and bad luck ) You have correctly identified the reason the poor plan was chosen. If that row estimate was anything higher than 1, that plan wouldn't be picked. If ANALYZE cl_student_semester_subject; does not fix the issue, then increasing the statistics targets with something like: alter table cl_student_semester_subject alter column companycode set (default_statistics_target = 1000); alter table cl_student_semester_subject alter column examheaderfk set (default_statistics_target = 1000); analyze cl_student_semester_subject; (Warning, additional statistics targets can slow down planning a little) or if that does not help and there's some correlation between those columns and/or the values in question, then maybe the following might help get a more accurate estimate: create statistics on companycode, examheaderfk from cl_student_semester_subject; analyze cl_student_semester_subject; David