Long running query in new production, not so long in old

From: Mark Steben <mark(dot)steben(at)drivedominion(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Long running query in new production, not so long in old
Date: 2019-03-27 23:27:06
Message-ID: CADyzmyy9afP526per2wRO6PBn=uqZe6jtpsRLyiFE5CG4PoQiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Good evening

We are moving to a new VM environment (expedient) and have one query that
typically runs in 22 - 25 seconds in our old environment, but is running in
about 1 hour, 20 minutes in our new. I'd like some insight as to why the
explain is showing shared buffer hits numbering over 113 milliion
in the new environment and only 445 thousand in the old. I have sent the
explains along with the table descriptions, row counts, the one function
that I know causes the bottleneck, the query, some relevant configuration
settings in postgresql conf (identical in both environments)
and a listing from top in both environments, showing memory, shared memory,
and cpu.

Everything seems to be identical or close, except for the shared buffer
count in the explain.
Any insight would be appreciated.

Thank you,

--
*Mark Steben*
Database Administrator
@utoRevenue <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com <http://www.autorevenue.com/>

<http://autobasedigital.net/marketing/DD12_sig.jpg>

Attachment Content-Type Size
numsends explain from new production.rtf application/octet-stream 4.3 KB
numsends top info both environments.rtf application/octet-stream 926 bytes
numsends explain from old production.rtf application/octet-stream 4.4 KB
numsends query.rtf application/octet-stream 1.3 KB
numsends important configs from postgresql.conf.rtf application/octet-stream 2.6 KB
numsends function.rtf application/octet-stream 998 bytes

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alexander Shaburov 2019-03-28 07:45:07 Re: Long running query in new production, not so long in old
Previous Message Keith Fiske 2019-03-27 14:25:34 Re: Unique Index Disk Consumption