(Because VACUUM FULL rewrites the table, an implicit REINDEX occurs.) I don't see mention of analyzing the database.
Also, VACUUM FULL probably doesn't do what you think it does. On Sat, Jul 20, 2024 at 7:44 AM sivapostg...@yahoo.com < sivapostg...@yahoo.com> wrote: > Executed > VACUUM FULL VERBOSE > followed by > REINDEX DATABASE dbname; > > It didn't increase the performance, still time out happened. VACUUM > didn't find any dead rows in that particular table. > > Yes, the actual query and conditions were not given in my first comment. > Actually where condition is not on the date field alone and the query with > current date is only a sample. > > What I did, > 1. Took backup (pg_dump) of the database from the server it's running. > [ Server config. Xeon Silver 4208, Windows Server 2019 Standard ]. > 2. Restored in another desktop system, installing PG 11 afresh. > 3. Performance was excellent. Within milliseconds I got the result. > Application was run from the desktop. > 4. Restored the database in the same server, as another database. > Improved performance but doesn't match the performance of the desktop. > Application run from the server itself. > > Now server got two databases with exactly the same data. Old one takes > more than 15 minutes; newer one takes few seconds. Application run from > the server and also from clients. In both conditions, the result is same. > > What else I need to do to correct this issue? > > I can easily replace the old database with the backup. Is that only > option? > > Happiness Always > BKR Sivaprakash > > On Thursday, 18 July, 2024 at 05:23:39 pm IST, Francisco Olarte < > fola...@peoplecall.com> wrote: > > > On Thu, 18 Jul 2024 at 11:38, sivapostg...@yahoo.com > <sivapostg...@yahoo.com> wrote: > > Hello, > > PG V11 > > > > Select count(*) from table1 > > Returns 10456432 > > > > Select field1, field2 from table1 where field3> '2024-07-18 12:00:00' > > Times out > > How do you send the query / how does it time out? Is that the real > query? Is table a table or a view? What does explain say? > > > > Any possible way(s) to do this? > > > If your client is timing out, increase timeout, if imposible you can > try fetching in batches, but more detail would be needed. > > Suggestions to improve total time had already being given, try to > decrease bloat if you have it, but AFAIK timeouts are configurable, so > it may just be you have a too low timeout. > > If it had been working, is field3 indexed? How is the table modified? > > Because with a configured timeout, whit an unindexed table ( forcing a > table scan ) the query may be working for years before you hit the bad > spot. Also, the query includes todays date, so I doubt it has been > used for years, probably "a similar one has been used for years", and > probably that is not your real table ( or you have a naming problem ). > Without giving real info, people cannot give you real solutions. > > Francisco Olarte. > >
