Re: Re. Select with where condition times out

(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 sivapostgres(at)yahoo(dot)com <
sivapostgres(at)yahoo(dot)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 <
> folarte(at)peoplecall(dot)com> wrote:
>
>
> On Thu, 18 Jul 2024 at 11:38, sivapostgres(at)yahoo(dot)com
> <sivapostgres(at)yahoo(dot)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.
>
>