Trying to trim to still relevant parts, as mail is becoming extremely
hard to read.
On Mon, 22 Jul 2024 at 07:08, sivapostgres@xxxxxxxxx
<sivapostgres@xxxxxxxxx> 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.
