public inbox for [email protected]
help / color / mirror / Atom feedFrom: Francisco Olarte <[email protected]>
To: [email protected] <[email protected]>
Cc: Postgresql General Group <[email protected]>
Subject: Re: Slow performance
Date: Fri, 26 Jul 2024 09:54:43 +0200
Message-ID: <CA+bJJbyvo2BW1=cgX_g3QgcZQDs=haBOwC8PShoE6Gh+LUGEaQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
Hello:
On Fri, 26 Jul 2024 at 07:31, [email protected]
<[email protected]> 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.
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Slow performance
In-Reply-To: <CA+bJJbyvo2BW1=cgX_g3QgcZQDs=haBOwC8PShoE6Gh+LUGEaQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox