Re: Re. Select with where condition times out - Mailing list pgsql-general
From | Francisco Olarte |
---|---|
Subject | Re: Re. Select with where condition times out |
Date | |
Msg-id | CA+bJJbzHZny5Q1zfTmrj4HTwj2V6+yA7OJhLfw3K6WBeha4DLA@mail.gmail.com Whole thread Raw |
In response to | Re: Re. Select with where condition times out ("sivapostgres@yahoo.com" <sivapostgres@yahoo.com>) |
List | pgsql-general |
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.
pgsql-general by date: