Subject: Re. Select with where condition times out

From: "[email-masked]" <[email-masked]>
Date: Thu, 18 Jul 2024 09:38:22 +0000 (UTC)

Hello,PG V11
Select count(*) from table1Returns 10456432
Select field1, field2 from table1 where field3> '2024-07-18 12:00:00' Times out
The above query was working fine for the past 2 years.  
Backup was taken a day back.  Need to recover complete data as far as possible.
Any possible way(s) to do this?
BKR Sivaprakash 

Yahoo Mail: Search, organise, conquer

---

From: Kashif Zeeshan <[email-masked]>
Date: Thu, 18 Jul 2024 14:42:41 +0500

Hi

It can be caused due to Table Bloat, table bloat can slow down queries. Use
the pg_repack extension or VACUUM FULL to try ti fix this table bloat issue.
You can refer to following link
https://supabase.com/blog/postgres-bloat

Regards
Kashif Zeeshan

On Thu, Jul 18, 2024 at 2:38 PM [email-masked] <
[email-masked]> wrote:

> Hello,
> PG V11
> <https://mail.onelink.me/107872968?pid=NativePlacement&c=Global_Acquisition_YMktg_315_EmailSignatureGrowth_YahooMail:Search,Organize,Conquer&af_sub1=Acquisition&af_sub2=Global_YMktg&af_sub3=&af_sub4=100000945&af_sub5=OrganizeConquer__Static_>
>

---

From: Francisco Olarte <[email-masked]>
Date: Thu, 18 Jul 2024 13:53:00 +0200

> Select field1, field2 from table1 where field3> '2024-07-18 12:00:00'

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.