On Sun, Aug 21, 2022 at 3:36 PM Kenny Bachman <kenny.bachman17@xxxxxxxxx> 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.
>
> 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
