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

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