Horton
Menu

Thread

  1. Re. Select with where condition times out

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2024-07-18T09:38:22Z

    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

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

    Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2024-07-18T09:42:41Z

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

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

    Francisco Olarte <folarte@peoplecall.com> — 2024-07-18T11:53:00Z

    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.

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

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2024-07-20T11:44:22Z

     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 <folarte@peoplecall.com> wrote:  
 
 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.

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

    Ron Johnson <ronljohnsonjr@gmail.com> — 2024-07-20T11:58:43Z

    (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 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 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 <
> folarte@peoplecall.com> wrote:
>
>
> 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.
>
>

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

    Francisco Olarte <folarte@peoplecall.com> — 2024-07-20T17:24:52Z

    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.

On Sat, 20 Jul 2024 at 13:44, sivapostgres@yahoo.com
<sivapostgres@yahoo.com> wrote:
> Executed
> VACUUM FULL VERBOSE
> followed by
> 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 ].
> 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.

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 conditions, the result is same.

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.

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

    Michael Nolan <htfoot@gmail.com> — 2024-07-20T19:21:56Z

    On Thu, Jul 18, 2024 at 4:38 AM 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
>
> 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
>

If you do a full backup, does it complete in a normal manner and the usual time?

Have you tried doing a shutdown and restart of the database, or
possibly rebooting the server?

You may need to alter the database server settings to increase the
maximum query time.

Mike Nolan
htfoot@gmail.com

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

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2024-07-22T04:46:13Z

     

    On Sunday, 21 July, 2024 at 12:52:22 am IST, Michael Nolan <htfoot@gmail.com> wrote:  
 
 On Thu, Jul 18, 2024 at 4:38 AM 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
>
> 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
>

If you do a full backup, does it complete in a normal manner and the usual time?

Have you tried doing a shutdown and restart of the database, or
possibly rebooting the server?

You may need to alter the database server settings to increase the
maximum query time.

Mike Nolan
htfoot@gmail.com

1.  Full backup taken without any issue.  Checked it by restoring as another database in the same server.  No Issues.
2.  PG Service stopped and re-started.    Re-booted the server also.  Same issue.
3.  PG is working with default settings only, that's set during installation time.  
When the query was run in the restored database, in the same server machine, the query executed in a second.  The same query, in original database, takes more than 15 min.  
BKR Sivaprakash

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

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2024-07-22T05:08:01Z

     
    On Saturday, 20 July, 2024 at 10:55:30 pm IST, Francisco Olarte <folarte@peoplecall.com> wrote:  
 
 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.

OK

On Sat, 20 Jul 2024 at 13:44, sivapostgres@yahoo.com
<sivapostgres@yahoo.com> wrote:
> Executed
> VACUUM FULL VERBOSE
> followed by
> 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 no dead rows.  



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

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

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 conditions, the result is same.

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 )

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?

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

What else ?

Regards.
Francisco Olarte.

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

    Francisco Olarte <folarte@peoplecall.com> — 2024-07-22T07:09:19Z

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

On Mon, 22 Jul 2024 at 07:08, sivapostgres@yahoo.com
<sivapostgres@yahoo.com> 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.

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

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.

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

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