22.12.25 (inclusive)

monetary

MVP

ICT tasks

IT

Smart Field Information Systems

Mobile app

In terms of optimization theory, the problem looks like finding an algorithm A that, for an arbitrary cost function f (describing the query profile, data distribution, memory constraints, etc.), provides a solution no worse than any other algorithm for all possible f. A number of results, known as the "no free lunch" theorems, show that there is no such universal algorithm: an algorithm that works well on some classes of functions inevitably loses on others. In practice, this means that it is impossible to write a single "eternal" optimizer that is guaranteed to be close to optimal for all imaginable workloads. As part of the experiment, we created a typical tracking event scheme: CREATE TABLE events ( event_id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, event_name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL, props JSONB ); CREATE INDEX ON events (user_id, created_at); CREATE INDEX ON events (event_name, created_at); CREATE INDEX ON events USING GIN (props); Next, we generated two opposite workloads.: • w₁ — long range queries by user_id with aggregation; • w₂ — point samples by event_name with filtering by JSON field. The optimal index configuration for W₁ and w₂ differs: indexes that speed up one set of queries worsen cache behavior and plans for the other. If we add future unknown workloads here, it becomes clear that a universal configuration that guarantees proximity to the optimum for all scenarios is unattainable.

The request for a single universal optimizer, which is "always almost optimal" for any possible load, goes beyond what is achievable even theoretically. A realistic statement should allow for: limiting workload classes, retraining the optimizer for new patterns, and accepting the fact that any algorithm will have scenarios in which it loses to alternatives.

Sergeev I.A.