Thread: Re. Select with where condition times out

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.

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
fieldalone 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
Server2019 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
performanceof 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.

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

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
Desktopwith 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.
Theactual 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
originaldatabase 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.