Re: Slow performance

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(*).

Francisco Olarte <folarte(at)peoplecall(dot)com>, 26 Tem 2024 Cum, 10:55 tarihinde
şunu yazdı:

> Hello:
>
> On Fri, 26 Jul 2024 at 07:31, sivapostgres(at)yahoo(dot)com
> <sivapostgres(at)yahoo(dot)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.
>
>
>