MIME-Version: 1.0
References: <937562047.1752859.1721295502145.ref@mail.yahoo.com> <937562047.1752859.1721295502145@mail.yahoo.com>
In-Reply-To: <937562047.1752859.1721295502145@mail.yahoo.com>
From: Francisco Olarte <folarte@peoplecall.com>
Date: Thu, 18 Jul 2024 13:53:00 +0200
Message-ID: <CA+bJJbzBRMuoWCW6i36s7qD25pH8K175A=vbPALw0ABtPCQT0g@mail.gmail.com>
Subject: Re: Re. Select with where condition times out
To: "sivapostgres@yahoo.com" <sivapostgres@yahoo.com>
Cc: Postgresql General Group <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CA%2BbJJbzBRMuoWCW6i36s7qD25pH8K175A%3DvbPALw0ABtPCQT0g%40mail.gmail.com>
Precedence: bulk

On Thu, 18 Jul 2024 at 11:38, sivapostgres@yahoo.com
<sivapostgres@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.