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 <fola...@peoplecall.com>, 26 Tem 2024 Cum, 10:55 tarihinde şunu yazdı: > Hello: > > On Fri, 26 Jul 2024 at 07:31, sivapostg...@yahoo.com > <sivapostg...@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. > > >
