Re: Re. Select with where condition times out

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(at)yahoo(dot)com
<sivapostgres(at)yahoo(dot)com> 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.