Hi: Please, avoid top posting, specially when replying to long mail with various points,m it makes it nearly impossible to track what you are replying to. On Sat, 20 Jul 2024 at 13:44, sivapostgres@xxxxxxxxx <sivapostgres@xxxxxxxxx> wrote: > Executed > VACUUM FULL VERBOSE > followed by > REINDEX DATABASE dbname; As it has been already said, vacuum full implies reindex ( it basically copies old table to a new one, including indexes, swaps them, deletes old one ). > It didn't increase the performance, still time out happened. VACUUM didn't find any dead rows in that particular table. The no dead rows is the interesting part. > 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. Then they are worthless and harmful. Query time problems is normally data and statistics dependent and always query dependent. The query you posted has only two ways to be done, and few ways to be improved. Suggestions for it will probably be harmful for other queries. > 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. What you did not: - Show your tables and indexes. - Show your real queries. - Tell us what "the application is" ( i.e., "psql", "a java app using JDBC", ... ) > 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. After what has been happening, I have to ask. Do you mean ONE server with two databases, or TWO servers with one database each? Also, what are the especs of the server and the desktops, and the postgres configuration on each? A misconfigured server can easily send query time through the roof ( i.e., DB servers want real RAM, if you configure postgres with too much mem and it swaps you can make a query really slow ) > What else I need to do to correct this issue? No clue. > I can easily replace the old database with the backup. Is that only option? Ah, one clue. From the info I have in this and previous mails, that is the only option for me. Having more info someone may have ideas, but so far the only thing I have concluded is three databases, fast in server, slow in server and desktop, test only. So my only options are fast server and slow server. So my solution would be "use fast server". As I said, maybe having more data we could suggest "analyze that table with these parameters", or "make this index" or "rewrite this condition in this way", but this is impossible to do with the data you provided. Regards. Francisco Olarte.
