pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mark Steben <mark.steben@drivedominion.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Long running query in new production, not so long in old
Date: Thu, 28 Mar 2019 13:56:16 +0100
Message-ID: <55e1328ed76390092c1ea5f1af4b59ce593e732f.camel@cybertec.at> (raw)
In-Reply-To: <CADyzmyy9afP526per2wRO6PBn=uqZe6jtpsRLyiFE5CG4PoQiA@mail.gmail.com>
References: <CADyzmyy9afP526per2wRO6PBn=uqZe6jtpsRLyiFE5CG4PoQiA@mail.gmail.com>

On Wed, 2019-03-27 at 19:27 -0400, Mark Steben wrote:
> 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.

Slow plan:

 ->  Index Scan using emailrcpts_4columns on emailrcpts  (cost=0.56..119078.48 rows=5 width=29) (actual time=4873080.765..4873080.765 rows=0 loops=1)
       Index Cond: ((cid = 1784539) AND (removed = false) AND (active = true) AND (bounce < 3))
       Filter: ((NOT removed) AND active AND (email IS NOT NULL) AND (((fname IS NOT NULL) AND (length((fname)::text) <> 0)) OR ((lname IS NOT NULL) AND (length((lname)::text) <> 0))) AND
(number_of_sends(id, 30, 1) < 3) AND (lower(get_make(cid, (vin)::text, (make_purchased)::text)) = 'ohgoshnonotthebees!!!'::text) AND (NOT (SubPlan 1)))
       Rows Removed by Filter: 19952
       Buffers: shared hit=113768530 read=6244
       SubPlan 1
         ->  Limit  (cost=0.28..15.58 rows=1 width=0) (never executed)

Fast plan:

 ->  Index Scan using emailrcpts_4columns on emailrcpts  (cost=0.56..113162.26 rows=5 width=29) (actual time=21086.555..21086.555 rows=0 loops=1)
       Index Cond: ((cid = 1784539) AND (removed = false) AND (active = true) AND (bounce < 3))
       Filter: ((NOT removed) AND active AND (email IS NOT NULL) AND (((fname IS NOT NULL) AND (length((fname)::text) <> 0)) OR ((lname IS NOT NULL) AND (length((lname)::text) <> 0))) AND
(number_of_sends(id, 30, 1) < 3) AND (lower(get_make(cid, (vin)::text, (make_purchased)::text)) = 'ohgoshnonotthebees!!!'::text) AND (NOT (SubPlan 1)))
       Rows Removed by Filter: 19952
       Buffers: shared hit=445188 read=61756
       SubPlan 1
         ->  Limit  (cost=0.28..15.58 rows=1 width=0) (never executed)

Hmm. These are the ideas I can come up with:

1. There are many index tuples belonging to dead heap tuples.
   Then re-running the query should produce way fewer buffer hits.
   VACUUM would fix that issue.

2. The index is terribly fragmented.
   REINDEX INDEX emailrcpts_4columns
   would improve that one.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






view thread (6+ messages)  latest in thread

Message-ID: <55e1328ed76390092c1ea5f1af4b59ce593e732f.camel@cybertec.at>
Permalink:  ../55e1328ed76390092c1ea5f1af4b59ce593e732f.camel@cybertec.at/
Also on:    postgresql.org/message-id/55e1328ed76390092c1ea5f1af4b59ce593e732f.camel@cybertec.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: laurenz.albe@cybertec.at, mark.steben@drivedominion.com
  Subject: Re: Long running query in new production, not so long in old
  In-Reply-To: <55e1328ed76390092c1ea5f1af4b59ce593e732f.camel@cybertec.at>
  References: <CADyzmyy9afP526per2wRO6PBn=uqZe6jtpsRLyiFE5CG4PoQiA@mail.gmail.com> <55e1328ed76390092c1ea5f1af4b59ce593e732f.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox