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
