public inbox for [email protected]
help / color / mirror / Atom feedRe: Slow performance
3+ messages / 2 participants
[nested] [flat]
* Re: Slow performance
@ 2024-07-26 05:41 Muhammad Ikram <[email protected]>
2024-07-26 06:05 ` Re: Slow performance [email protected] <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Muhammad Ikram @ 2024-07-26 05:41 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Postgresql General Group <[email protected]>
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 [email protected] <
[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow performance
2024-07-26 05:41 Re: Slow performance Muhammad Ikram <[email protected]>
@ 2024-07-26 06:05 ` [email protected] <[email protected]>
2024-07-26 06:15 ` Re: Slow performance Muhammad Ikram <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: [email protected] @ 2024-07-26 06:05 UTC (permalink / raw)
To: Muhammad Ikram <[email protected]>; +Cc: Postgresql General Group <[email protected]>
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 <[email protected]> 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
On Fri, Jul 26, 2024 at 10:31 AM [email protected] <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow performance
2024-07-26 05:41 Re: Slow performance Muhammad Ikram <[email protected]>
2024-07-26 06:05 ` Re: Slow performance [email protected] <[email protected]>
@ 2024-07-26 06:15 ` Muhammad Ikram <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Muhammad Ikram @ 2024-07-26 06:15 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Postgresql General Group <[email protected]>
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
On Fri, Jul 26, 2024 at 11:05 AM [email protected] <
[email protected]> wrote:
> 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 <
> [email protected]> 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
>
>
> On Fri, Jul 26, 2024 at 10:31 AM [email protected] <
> [email protected]> 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
>
>
--
Muhammad Ikram
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-07-26 06:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-26 05:41 Re: Slow performance Muhammad Ikram <[email protected]>
2024-07-26 06:05 ` [email protected] <[email protected]>
2024-07-26 06:15 ` Muhammad Ikram <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox