Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sV8jx-0088Il-FK for pgsql-general@arkaria.postgresql.org; Sat, 20 Jul 2024 11:59:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sV8jv-00FRLz-L4 for pgsql-general@arkaria.postgresql.org; Sat, 20 Jul 2024 11:59:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sV8jv-00FRHk-5v for pgsql-general@lists.postgresql.org; Sat, 20 Jul 2024 11:59:03 +0000 Received: from mail-oi1-x235.google.com ([2607:f8b0:4864:20::235]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sV8jn-000X0s-7t for pgsql-general@postgresql.org; Sat, 20 Jul 2024 11:59:00 +0000 Received: by mail-oi1-x235.google.com with SMTP id 5614622812f47-3d9de9f8c8dso1499047b6e.1 for ; Sat, 20 Jul 2024 04:58:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721476734; x=1722081534; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=1nnonUsMO4uhCSAQ2GxXAm4fGdI6EfkLvMfrriH/r/w=; b=Y9m05ltXNVFX7eNczg5GYFJnPHhMM+7N21xaNLyeoxXfU2/bmLiMbbTwsDuQ3nZ7y4 v2HL9SA5f6MK6s4ZU56g6FSGsR3PiVrjfkEnUPpckUj/jW+wyvwra81gH8Z5Ra6BToBl IxfQJ/0yP5fpRqCFRr5NqnaTAFiDuqLbrOmq2ahwJif37od00oZwW4u5/bSVEc/uyr5L EqB9LBxal7TcMf0ybATX6MgK91vda/IyJHgEcngwl7ds6GDtzKe3osQOFP+WlLE6wn02 aNFjjF9xxM0LMPwJZKqleLE68FuQs1vJ0pfjpO/lu7zhRJVrs0aiyGIIQ9XmrY+aUgEw gNRw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721476734; x=1722081534; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=1nnonUsMO4uhCSAQ2GxXAm4fGdI6EfkLvMfrriH/r/w=; b=ujc6J6iK4d32MfwMZYP/wo5y4xPtN22FLdPJCyj6vuDakc2JtkvDSbo8A+9/ldkgcp WiROk79wbwWzvABKisEgkzSJqALTRpY2+zKJrAXrrnQU38KsvkKrMYmFTUdmy0hqqIm4 C5F+XwUDBv60lpVx2yKkRxFVwYlIdTClIgnRSO6KYdSsIGFWdLv2rzVzyfHCYUW0Mq7A pwOxPwYDw4xnnzSNDdxJiyCo3fdVMYBAsW8yga+fk2lQAruFa2xt+QtDOIV4OdGXPMmu Dfa2boH4BwMkN1PXEQ86/hJfA61hUMxDLi/YYBeAg1CZaDyoDsiHSG28Rq3ZU4KPnPEd EVvA== X-Gm-Message-State: AOJu0YxQSZXPRVPUcOuURNKOg3928OfjX10Ep0MSOeyRTCZKNRPM/Ipv Utul/aIg8wE7z45lpP8RruMuMhcpJuWMywHpzuzb54SxUXLZdLhbPW0Bz1Unvy81XGXuBuEIcSg 7uP9RQqY3vxky7l9KeGazlVZk859s5g== X-Google-Smtp-Source: AGHT+IH1O93nPRHBPPQ/aGx/XaxXiO73oYaHr4t6ja6e1ZJvXYjfNcT65lZN9zMcR1fLn0rlgn/fHGu9GK9jiQkRlM8= X-Received: by 2002:a05:6870:6488:b0:25e:11f4:f694 with SMTP id 586e51a60fabf-261213d631amr2312208fac.21.1721476734232; Sat, 20 Jul 2024 04:58:54 -0700 (PDT) MIME-Version: 1.0 References: <937562047.1752859.1721295502145.ref@mail.yahoo.com> <937562047.1752859.1721295502145@mail.yahoo.com> <680243525.2443164.1721475862129@mail.yahoo.com> In-Reply-To: <680243525.2443164.1721475862129@mail.yahoo.com> From: Ron Johnson Date: Sat, 20 Jul 2024 07:58:43 -0400 Message-ID: Subject: Re: Re. Select with where condition times out To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000003bb34a061dac892a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003bb34a061dac892a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable (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=E2=80=AFAM sivapostgres@yahoo.com < sivapostgres@yahoo.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 wit= h > 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@peoplecall.com> wrote: > > > On Thu, 18 Jul 2024 at 11:38, 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. > > --0000000000003bb34a061dac892a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
(Because VACUUM FULL rewrites the table, an implicit = REINDEX occurs.)

I don't see mention of analyz= ing the database.

Also, VACUUM FULL probably doesn= 't do what you think it does.

On Sat, Jul 20, 2024 at 7:44=E2=80=AFAM sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:=
Executed=C2=A0
VACUUM FULL V= ERBOSE
followed by
REINDEX DATA= BASE dbname;

It didn't increase th= e performance, still time out happened.=C2=A0 VACUUM didn't find any de= ad 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 fie= ld alone and the query with 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 it'= s running.=C2=A0 =C2=A0[ Server config. Xeon Silver 4208, Windows Server 20= 19 Standard ].
2.=C2=A0 Restored in another desktop system, installing P= G 11 afresh.
3.=C2=A0 Performance was excellent.=C2=A0 Within millisecon= ds I got the result.=C2=A0 Application was run from the desktop.
4.=C2= =A0 Restored the database in the same server, as another database.=C2=A0 Im= proved performance but doesn't match the performance of the desktop.=C2= =A0 Application run from the server itself.=C2=A0=C2=A0

Now server g= ot two databases with exactly the same data.=C2=A0 =C2=A0Old one takes more= than 15 minutes; newer one takes few seconds.=C2=A0 Application run from t= he server and also from clients.=C2=A0 In both conditions, the result is sa= me.=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 t= hat only option?

Happiness= Always
BKR Sivaprakash

=20
=20
On Thursday, 18 July, 2024 at 05:23:39 pm IST, Fran= cisco Olarte <folarte@peoplecall.com> wrote:


=20 =20
On Thu, 18 Jul 2024 at 11:38, sivapos= tgres@yahoo.com
<sivapostgres@yahoo.com> wr= ote:
> Hello,
> PG V11
>
> Select count(*) from table1
> Returns 10456432
>
&g= t; 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 nee= ded.

Suggestions to improve total time= had already being given, try to
decrease bloat if you ha= ve it, but AFAIK timeouts are configurable, so
it may jus= t 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 uni= ndexed table ( forcing a
table scan ) the query may be wo= rking for years before you hit the bad
spot. Also, the qu= ery 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 y= ou real solutions.

Francisco Olarte.
--0000000000003bb34a061dac892a--