Re: Large Table structure recommendations

[Peter Brawley]

First, do understand the query correctly... it pivots on enum values to average several param values joined to max hourly values for one param, is that right?

On first inspection it looks like a variant of the "within-group aggregates" query pattern (https://www.artfulsoftware.com/queries.php). Some solutions to that problem are a lot faster than others, and different solutions imply somewhat different table designs. I'd want to benchmark them all against a range of table designs and data volumes so I can pick the best Explain Analyze result and the best performance curve.

Pivot queries are slow and don't scale well. I'd probably start by replacing the param enum column with a tinyint column referencing a lookup table for the param strings.

And since 8.0 offers tgols that are likely to help, (windowing functions, CTEs, other optimisations), I'd upgrade to 8.0 before doing anything else.