Re: Need help identifying a periodic performance issue.

On Fri, Nov 19, 2021 at 6:03 AM Robert Creager <robertc@spectralogic.com> wrote:
> Which would be better?  Discard plans or forcing custom plans?  Seems like wrapping a copy might be better than the
Postgres.confchange as that would affect all statements.  What kind of performance hit would we be taking with that do
youestimate?  Microseconds per statement?  Yeah, hard to say, depends on hardware and such.  Would there be any benefit
overallto doing that?  Forcing the replan? 

Just to understand what's going on, it'd be interesting to know if the
problem goes away if you *just* inject the DISCARD PLANS statement
before running your COPYs, but if that doesn't help it'd also be
interesting to know what happens if you ANALYZE each table after each
COPY.  Are you running any explicit ANALYZE commands?  How long do
your sessions/connections live for?

I'm wondering if the thing that changed between 9.6 and 13 might be
the heuristics for when auto vacuum's background ANALYZE is triggered,
creating the unlucky timing required to get your system to this state
occasionally.

For a while now I have been wondering how we could teach the
planner/stats system about "volatile" tables (as DB2 calls them), that
is, ones that are frequently empty, which often come up in job queue
workloads.  I've seen problems like this with user queries (I used to
work on big job queue systems across different relational database
vendors, which is why I finished up writing the SKIP LOCKED patch for
9.5), but this is the first time I've contemplated FK check queries
being negatively affected by this kind of stats problem.  I don't have
a good concrete idea, though (various dumb ideas: don't let auto
analyze run on an empty table if it's marked VOLATILE, or ignore
apparently empty stats on tables marked VOLATILE (and use what?),
...).