Subject: Re: Re. Select with where condition times out

From: "[email-masked]" <[email-masked]>
Date: Sat, 20 Jul 2024 11:44:22 +0000 (UTC)

Executed VACUUM FULL VERBOSEfollowed byREINDEX 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 <[email-masked]> wrote:  
 
 On Thu, 18 Jul 2024 at 11:38, [email-masked]
<[email-masked]> 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.

---

From: Ron Johnson <[email-masked]>
Date: Sat, 20 Jul 2024 07:58:43 -0400

(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 [email-masked] <
[email-masked]> 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 ].
> 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.
>

---

From: Francisco Olarte <[email-masked]>
Date: Sat, 20 Jul 2024 19:24:52 +0200

Hi:

Please, avoid top posting, specially when replying to long mail with
various points,m it makes it nearly impossible to track what you are
replying to.

> REINDEX DATABASE dbname;

As it has been already said, vacuum full implies reindex ( it
basically copies old table to a new one, including indexes, swaps
them, deletes old one ).
> It didn't increase the performance, still time out happened.  VACUUM didn't find any dead rows in that particular table.

The no dead rows is the interesting part.

> 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.

Then they are worthless and harmful. Query time problems is normally
data and statistics dependent and always query dependent.

The query you posted has only two ways to be done, and few ways to be
improved. Suggestions for it will probably be harmful for other
queries.

> 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 ].

What you did not:
- Show your tables and indexes.
- Show your real queries.
- Tell us what "the application is" ( i.e., "psql", "a java app using
JDBC", ... )

> 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 conditio…

After what has been happening, I have to ask. Do you mean ONE server
with two databases, or TWO servers with one database each? Also, what
are the especs of the server and the desktops, and the postgres
configuration on each? A misconfigured server can easily send query
time through the roof ( i.e., DB servers want real RAM, if you
configure postgres with too much mem and it swaps you can make a query
really slow )

> What else I need to do to correct this issue?

No clue.

> I can easily replace the old database with the backup.  Is that only option?

Ah, one clue. From the info I have in this and previous mails, that is
the only option for me. Having more info someone may have ideas, but
so far the only thing I have concluded is three databases, fast in
server, slow in server and desktop, test only. So my only options are
fast server and slow server. So my solution would be   "use fast
server". As I said, maybe having more data we could suggest "analyze
that table with these parameters", or "make this index" or "rewrite
this condition in this way", but this is impossible to do with the
data you provided.

Regards.
Francisco Olarte.

---

From: "[email-masked]" <[email-masked]>
Date: Mon, 22 Jul 2024 05:08:01 +0000 (UTC)

On Saturday, 20 July, 2024 at 10:55:30 pm IST, Francisco Olarte <[email-masked]> wrote:  
 
 Hi:

OK

Yes no dead rows.  

Actual Query: select source_node_id, create_time from sym_data where table_name = 'tx_combined_sales_header' and ((event_type = 'I' and row_data like '"F92DD7AA237A45D99CA5741DF73EA3D1"%') or (event_type in ('U', 'D') and pk_data like '"F92DD7AA237A45D99CA5741DF73EA3D1"')) and create_time >= '2024-07-18 01:43:32.981' order by create_time desc

What you did not:

I thought I'm clear. My bad.
2 computers were involved in total.  One Xeon Server with Windows 2019 Standard and other one is Intel i5 based Desktop with Windows 10.
I took backup (pg_dump) from windows server machine.
And restored in the same server as another database.  Now we have 2 databases with identical data in Windows Server. The actual query (given above) is taking more than 15 min in the original database and takes a second in the restored database.
Also I restored the database in Desktop machine also, which takes ms only.All PG settings are set at installation, and nothing changed by us. 

> What else I need to do to correct this issue?

No clue.
I have done Vacuum, Re-Index in the original database. No improvement. Anything else that I can do to make the original database to perform just like the restored database?

What else ?

Regards.
Francisco Olarte.

---

From: Francisco Olarte <[email-masked]>
Date: Mon, 22 Jul 2024 09:09:19 +0200

Trying to trim to still relevant parts, as mail is becoming extremely
hard to read.

On Mon, 22 Jul 2024 at 07:08, [email-masked]
<[email-masked]> wrote:
> Actual Query:
>  select source_node_id, create_time from sym_data where table_name = 'tx_combined_sales_header' and ((event_type = 'I' and row_data like '"F92DD7AA237A45D99CA5741DF73EA3D1"%') or (event_type in ('U', 'D') and pk_data like '"F92DD7AA237A45D99CA5741DF73EA3D1"')) and create_time >= '2024-07-18 01:43:32.981' order by create_time desc

That is a complex query, you should probably insure tables are
properly analized, show your indexes and show explain, explain analyze
if possible, on both servers.

...
> And restored in the same server as another database.  Now we have 2 databases with identical data in Windows Server. The actual query (given above) is taking more than 15 min in the original databa…

Assuming same cluster ( same postgres instance ) explain may shed some light.

> I have done Vacuum, Re-Index in the original database. No improvement. Anything else that I can do to make the original database to perform just like the restored database?

Insure analyze is the same in both. Test using explain. If you peruse
the archives you will notice explain output is what tells people what
the server is doing. IIRC explain (analyze,buffers) on both will show
how the query was done and where the time was spent.

> the only option for me. Having more info someone may have ideas, but
...
> What else ?

Use explain, send appropriate info. You may have noticed there are few
responses to your mail. This is probably due to people not being able
to provide  too meaningful help with the data you provide and not
willing to try taking blind shots.

Postgres is a very complex piece of software, and even includes some
randomization when optimizing very complex queries, which is not your
case. With a query like yours having different times in similar
databases the first thing to do will be analyze both, explain analyze
on both, compare the results, to ensure both databases are doing the
same operation. If they are, then it is time to see why the old one
does it slower ( I assume you are not testing a busy production server
against an idling backup). If they do not, then the path to follow is
to compare plans and try to know why they differ.

> Francisco Olarte.
As an aside, I would personally appreciate it if you delete my
signature from the end of your message when replying to one sent by
me.

Francisco Olarte.