On Fri, Nov 19, 2021 at 6:03 AM Robert Creager <robertc@xxxxxxxxxxxxxxxx> wrote: > Which would be better? Discard plans or forcing custom plans? Seems like wrapping a copy might be better than the Postgres.conf change as that would affect all statements. What kind of performance hit would we be taking with that do you estimate? Microseconds per statement? Yeah, hard to say, depends on hardware and such. Would there be any benefit overall to 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?), ...).
