Re: cutting down the TODO list thread - Mailing list pgsql-hackers

On Mon, May 15, 2023 at 2:05 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> > Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER
> > -> There are external tools that help with this kind of analysis
>
> Althrough there are external tools which help with the analysis, the
> silent complaint of this item seems to be "PostgreSQL doesn't provide
> the user with actionable maintenance tasks/DDL info", and I
> wholeheartedly agree with that.

Well, if SET PERFORMANCE_TIPS is adding a new GUC, that's inherently
something that could not happen in a contrib module, only in core. If
it's dedicated syntax of some kind, same thing. I am not at all
convinced that adding something spelled SET PERFORMANCE_TIPS in any
form is a good idea, but at the very least someone would have to
propose what the possible values of that option would be and what they
would do in a pretty detailed way for us to decide whether we liked it
or not. It seems to me that it would be quite difficult to get any
kind of consensus. If you came up with several different kinds of
performance tips and made the GUC a comma-separated list of tip types,
I suspect that you'd get a good bit of opposition: maybe Tom would
dislike one of the tip types for some reason, me a second, and Andres
a third. If you remove all three of those you've gutted your
implementation. Whee.

But even if we leave the syntax aside, it is very difficult, IMHO, to
come up with something in this area that makes sense to put in core.
There are so many things you could warn about, and so many possible
algorithms that you could use to emit warnings. We do have a few
things somewhat like this in core, like the warning that checkpoints
are happening too close together, or the hint that when you said DROP
TABLE olders maybe you really meant DROP TABLE orders. But in those
cases, the situation is fairly unambiguous: if your checkpoints are
happening too close together, you should probably raise max_wal_size,
as long as it's not going to run you out of disk space. If you
specified a non-existent object name, you should probably correct the
object name to something that does exist.

But things like CREATE INDEX or CLUSTER are a lot trickier. I struggle
to think of what individual PostgreSQL command would have enough
context to know that those things are a good idea. For example,
clustering a table on an index makes sense if (1) there are queries
that would run faster with the clustering and (2) they are run
frequently enough and are expensive enough that the savings would be
material and (3) the clustering wouldn't degrade so quickly as to be
pointless. But I don't see how it would be possible to discover this
situation without instrumenting the whole workload, or at least having
some trace of the workload. Even if you have the data, you probably
need to do a bunch of number-crunching to come up with good
recommendations, and that's expensive, and you probably have to be OK
with a significantly higher risk of wrong answers, too, because the
past may be different from the future, and the planner's estimates of
what the clustering would save might be wrong.

I wouldn't go so far as to say that doing anything of this sort is
absolutely and categorically hopeless, but suggesting to an aspiring
hacker (or even an established one) that they go try to implement SET
PERFORMANCE_TIPS isn't helpful at all. At least in my opinion, it's
not clear what that means, or that we want it, or what we might want
instead, or even that we want anything at all. We should aim to have a
TODO list filled with things that are actionable and likely to be
worth the effort someone might choose to invest in them.

--
Robert Haas
EDB: http://www.enterprisedb.com