Hi,
Could you perform diff on postgresql.conf file to see whether values are
same for work_mem, shared_buffers, maintenance_work_mem and other related
parameters?
Regards,
Ikram
On Fri, Jul 26, 2024 at 10:31 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> Hello,
> Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two
> databases with identical data.
>
> 1. First DB: client_db
> 2. Second DB: client_test
>
> Took backup (pg_dump) of first database (client_db) and restored the
> database as second database (client_test).
>
> Query:
> Select a.examname, a.registrationnumber, b.studentname, d.departmentname,
> e.levelname,
> a.subjectcode, c.subjectname, b.regular, a.semester,
> a.dummynumber, p.semester as curr_sem,
> a.internalmark, a.externalmark, a.result, coalesce((a.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
>
>
--
Muhammad Ikram
