Re: Slow performance - Mailing list pgsql-general

Hello:

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