Re: Postgresql 14 performance

On Sun, Aug 21, 2022 at 3:36 PM Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com>
wrote:
>
> Hello Jeff,
>
> Thank you for your response.
>
> >Do you mean just no structural changes, or no changes of any kind?
> I mean, no database parameter changes were made.

So that probably just means that enough data has changed that the new stats
suggest the new plan will be faster. It is wrong about that, but given the
poor estimates in the plan that is not surprising that it would be wrong.
But without seeing the fast plan, it might be very hard to figure out what
changed. Maybe you could restore a backup to a test server to get the old
plan.

> > Did they start to "sometimes take minutes" just today, or was that a
pre existing issue?
> Not just today. Sometimes it takes 50 seconds, sometimes 20 seconds,
sometimes 10 milliseconds. (at completely random times.)

That sounds like a topic for a different email thread. You can use
auto_explain with the log_min_duration setting to capture plans for the
very run where they were slow. Since the slowness is only sporadic, then
you get a fast plan to compare it to just by manually repeating the query.

> > How up to date are the stats? Did you just finish ANALYZE right before
you captured the plan?
> Yes, I ran the VACUUM ANALYZE command for the all tables of the view and
I captured the plan after the vacuum analyze operation finished.
>

Your new plan shows the heap fetches were greatly reduced, so another
vacuum must have been more successful than the earlier one. Maybe a
long-running transaction was blocking rows from being cleaned up, and
finally went away. But the estimate is still very off.

Could you do `EXPLAIN (ANALYZE, BUFFERS) select * from "T_WF_STEP" wher
"StepDefTypeCd" = 'End';` to see how many rows it thought it would find
versus how many it actually finds? And if they are very different, ANALYZE
that table again just to make sure it didn't get overlooked before and then
repeat the query.

>
> > Do you happen to have one for the same query from before the problem
started? Also, it would be better to capture BUFFERS as part of the plan,
and preferably with track_io_timing turned on
> I added the exec plan with the buffers option, and I don't have query
plan from before the problem.

You can see there that little time was spent reading data, so that explains
why repeating the query didn't make it much faster due to caching. The
time isn't spent reading data, but doing CPU work on data already in memory.

Cheers,

Jeff