On Mon, 9 Jun 2025 at 06:53, Robert Treat <r...@xzilla.net> wrote: > > On Sat, Jun 7, 2025 at 9:17 PM David Rowley <dgrowle...@gmail.com> wrote: > > What are your thoughts on cached plans? In this scenario, do you > > assume that waiting a few days means that connections get reset and > > prepared statements will have been replanned? Or do you think cached > > plans don't matter in this scenario? > > > > Heh; I did say that the GUC model wasn't perfect, so good on you for > getting right to one of the more wonky parts. In practice, I actually > don't think it matters as much as one might think; IME there is a sort > of inverse relationship were the more sensitive you are to production > changes and/or running at high scale, the more likely you are going to > want to slow deploy / ramp up these changes, and doing things like > adding the GUC at the session level will likely require a connection > recycle anyway. Also keeping invisible indexes in place for days or > weeks is likely to be a common scenario, and again we don't normally > expect connections, or cached plans, to stay alive for weeks at a > time. Of course you can't dismiss this; you'd definitely have to > document that if they are worried about queries with cached plans the > best solution would be to recycle any connections that might have > existed before setting the guc in place. That may not sound ideal, but > I think in practice it is no worse than the practical effects of > thinking that ANALYZE will help keep your queries fast; sure it keeps > your statistics up to date, but if you are running cached plans for > indefinite periods of time, you wouldn't actually pick those up those > statistics changes*, which means cached plans are already susceptible > to degrading over time, and we are expecting people to recycle > connections regularly even if we don't say it very loud.
I agree that it doesn't seem ideal. I feel like if we're adding a feature that we have to list a bunch of caveats in the documentation, then we're doing something wrong. BTW, the ALTER INDEX will correctly invalidate cached plans and does not suffer from the same issue. My thoughts on this are that extensions are a better place to keep solutions that work most of the time. Once you start committing quirky things to Postgres, you sentence yourself to answering the same question for possibly a few decades in the -bugs or -general mailing list. I do my best to avoid that and feel we have enough of that already, so I'm -1 on the GUC solution for this. I know there are a few other people that are for it, so feel free to listen to them instead. Personally, I'd rather see us getting query hints in core and having some method to specify a global hint to hint "not using index X". I'm not holding my breath for that one. David
