(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.
ExecutedVACUUM FULL VERBOSEfollowed byREINDEX 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 SivaprakashOn Thursday, 18 July, 2024 at 05:23:39 pm IST, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:On Thu, 18 Jul 2024 at 11:38, sivapostgres@xxxxxxxxx
<sivapostgres@xxxxxxxxx> 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.
