Date: Sat, 20 Jul 2024 11:44:22 +0000 (UTC)
From: "sivapostgres@yahoo.com" <sivapostgres@yahoo.com>
Reply-To: "sivapostgres@yahoo.com" <sivapostgres@yahoo.com>
To: Francisco Olarte <folarte@peoplecall.com>
Cc: Postgresql General Group <pgsql-general@lists.postgresql.org>
Message-ID: <680243525.2443164.1721475862129@mail.yahoo.com>
In-Reply-To: <CA+bJJbzBRMuoWCW6i36s7qD25pH8K175A=vbPALw0ABtPCQT0g@mail.gmail.com>
References: <937562047.1752859.1721295502145.ref@mail.yahoo.com> <937562047.1752859.1721295502145@mail.yahoo.com> <CA+bJJbzBRMuoWCW6i36s7qD25pH8K175A=vbPALw0ABtPCQT0g@mail.gmail.com>
Subject: Re: Re. Select with where condition times out
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_2443163_692248890.1721475862128"
Content-Length: 8313
Archived-At: <https://www.postgresql.org/message-id/680243525.2443164.1721475862129%40mail.yahoo.com>
Precedence: bulk

------=_Part_2443163_692248890.1721475862128
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

 Executed=C2=A0VACUUM FULL VERBOSEfollowed byREINDEX DATABASE dbname;
It didn't increase the performance, still time out happened.=C2=A0 VACUUM d=
idn't find any dead rows in that particular table.=C2=A0=C2=A0

Yes, the actual query and conditions were not given in my first comment.=C2=
=A0 Actually where condition is not on the date field alone and the query w=
ith current date is only a sample.=C2=A0 =C2=A0
What I did,1.=C2=A0 Took backup (pg_dump) of the database from the server i=
t's running.=C2=A0 =C2=A0[ Server config. Xeon Silver 4208, Windows Server =
2019 Standard ].
2.=C2=A0 Restored in another desktop system, installing PG 11 afresh.
3.=C2=A0 Performance was excellent.=C2=A0 Within milliseconds I got the res=
ult.=C2=A0 Application was run from the desktop.
4.=C2=A0 Restored the database in the same server, as another database.=C2=
=A0 Improved performance but doesn't match the performance of the desktop.=
=C2=A0 Application run from the server itself.=C2=A0=C2=A0

Now server got two databases with exactly the same data.=C2=A0 =C2=A0Old on=
e takes more than 15 minutes; newer one takes few seconds.=C2=A0 Applicatio=
n run from the server and also from clients.=C2=A0 In both conditions, the =
result is same.=C2=A0=C2=A0
What else I need to do to correct this issue?
I can easily replace the old database with the backup.=C2=A0 Is that only o=
ption?
Happiness Always
BKR Sivaprakash
    On Thursday, 18 July, 2024 at 05:23:39 pm IST, Francisco Olarte <folart=
e@peoplecall.com> wrote: =20
=20
 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.
 =20
------=_Part_2443163_692248890.1721475862128
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><div class=3D"ydp791b15a5yahoo-style-wrap" style=
=3D"font-family:times new roman, new york, times, serif;font-size:16px;"><d=
iv></div>
        <div dir=3D"ltr" data-setdir=3D"false">Executed&nbsp;</div><div dir=
=3D"ltr" data-setdir=3D"false">VACUUM FULL VERBOSE</div><div dir=3D"ltr" da=
ta-setdir=3D"false">followed by</div><div dir=3D"ltr" data-setdir=3D"false"=
>REINDEX DATABASE dbname;</div><div><br></div><div dir=3D"ltr" data-setdir=
=3D"false">It didn't increase the performance, still time out happened.&nbs=
p; VACUUM didn't find any dead rows in that particular table.&nbsp;&nbsp;<b=
r></div><div dir=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" d=
ata-setdir=3D"false">Yes, the actual query and conditions were not given in=
 my first comment.&nbsp; Actually where condition is not on the date field =
alone and the query with current date is only a sample.&nbsp; &nbsp;</div><=
div dir=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdi=
r=3D"false">What I did,</div><div dir=3D"ltr" data-setdir=3D"false">1.&nbsp=
; Took backup (pg_dump) of the database from the server it's running.&nbsp;=
 &nbsp;[ Server config. Xeon Silver 4208, Windows Server 2019 Standard ].<b=
r>2.&nbsp; Restored in another desktop system, installing PG 11 afresh.<br>=
3.&nbsp; Performance was excellent.&nbsp; Within milliseconds I got the res=
ult.&nbsp; Application was run from the desktop.<br>4.&nbsp; Restored the d=
atabase in the same server, as another database.&nbsp; Improved performance=
 but doesn't match the performance of the desktop.&nbsp; Application run fr=
om the server itself.&nbsp;&nbsp;<br><br>Now server got two databases with =
exactly the same data.&nbsp; &nbsp;Old one takes more than 15 minutes; newe=
r one takes few seconds.&nbsp; Application run from the server and also fro=
m clients.&nbsp; In both conditions, the result is same.&nbsp;&nbsp;</div><=
div dir=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdi=
r=3D"false">What else I need to do to correct this issue?</div><div dir=3D"=
ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"false"=
>I can easily replace the old database with the backup.&nbsp; Is that only =
option?</div><div dir=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"l=
tr" data-setdir=3D"false">Happiness Always<br>BKR Sivaprakash</div><div dir=
=3D"ltr" data-setdir=3D"false"><br></div>
       =20
        </div><div id=3D"yahoo_quoted_1667118553" class=3D"yahoo_quoted">
            <div style=3D"font-family:'Helvetica Neue', Helvetica, Arial, s=
ans-serif;font-size:13px;color:#26282a;">
               =20
                <div>
                        On Thursday, 18 July, 2024 at 05:23:39 pm IST, Fran=
cisco Olarte &lt;folarte@peoplecall.com&gt; wrote:
                    </div>
                    <div><br></div>
                    <div><br></div>
               =20
               =20
                <div><div dir=3D"ltr">On Thu, 18 Jul 2024 at 11:38, <a shap=
e=3D"rect" ymailto=3D"mailto:sivapostgres@yahoo.com" href=3D"mailto:sivapos=
tgres@yahoo.com">sivapostgres@yahoo.com</a><br clear=3D"none">&lt;<a shape=
=3D"rect" ymailto=3D"mailto:sivapostgres@yahoo.com" href=3D"mailto:sivapost=
gres@yahoo.com">sivapostgres@yahoo.com</a>&gt; wrote:<br clear=3D"none">&gt=
; Hello,<br clear=3D"none">&gt; PG V11<br clear=3D"none">&gt;<br clear=3D"n=
one">&gt; Select count(*) from table1<br clear=3D"none">&gt; Returns 104564=
32<br clear=3D"none">&gt;<br clear=3D"none">&gt; Select field1, field2 from=
 table1 where field3&gt; '2024-07-18 12:00:00'<br clear=3D"none">&gt; Times=
 out<br clear=3D"none"><br clear=3D"none">How do you send the query / how d=
oes it time out? Is that the real<br clear=3D"none">query? Is table a table=
 or a view? What does explain say?<div class=3D"yqt5815655315" id=3D"yqtfd1=
2137"><br clear=3D"none"><br clear=3D"none">&gt; Any possible way(s) to do =
this?</div><br clear=3D"none"><br clear=3D"none">If your client is timing o=
ut, increase timeout, if imposible you can<br clear=3D"none">try fetching i=
n batches, but more detail would be needed.<br clear=3D"none"><br clear=3D"=
none">Suggestions to improve total time had already being given, try to<br =
clear=3D"none">decrease bloat if you have it, but AFAIK timeouts are config=
urable, so<br clear=3D"none">it may just be you have a too low timeout.<br =
clear=3D"none"><br clear=3D"none">If it had been working, is field3 indexed=
? How is the table modified?<br clear=3D"none"><br clear=3D"none">Because w=
ith a configured timeout, whit an unindexed table ( forcing a<br clear=3D"n=
one">table scan ) the query may be working for years before you hit the bad=
<br clear=3D"none">spot. Also, the query includes todays date, so I doubt i=
t has been<br clear=3D"none">used for years, probably "a similar one has be=
en used for years", and<br clear=3D"none">probably that is not your real ta=
ble ( or you have a naming problem ).<br clear=3D"none">Without giving real=
 info, people cannot give you real solutions.<br clear=3D"none"><br clear=
=3D"none">Francisco Olarte.<div class=3D"yqt5815655315" id=3D"yqtfd63042"><=
br clear=3D"none"></div></div></div>
            </div>
        </div></body></html>
------=_Part_2443163_692248890.1721475862128--