Thanks to Imre Samu's help, I found out that this is an unwarranted interference of the JIT compilation. When it is disabled, the short queries work stably. Before the problem started, I purposely increased the amount of surrogate data to evaluate performance. Perhaps the logic for enabling JIT compilation is different in different versions of Postgres. It didn't show up as clearly on long queries because they were rewritten without JOIN VIEW and provide filtering before aggregation and linking. I want to make rougher JIT compiler settings (I think disabling it is fundamentally wrong) and rewrite all queries similar to long queries. Thanks.
--
Regards, Dmitry!
сб, 11 дек. 2021 г. в 16:18, Peter J. Holzer <hjp-pgsql@hjp.at>:
Is this repeatable or did it just occur once?
32 µs to retrieve a single row via index probably means that it was already cached in RAM 842796 µs to retrieve a single row via index doesn't even look realistic for a completely cold database on a slow rotating hard disk. If this happened only once I suspect that something else interfered (maybe another I/O intensive process, if this is on a VM maybe even on another guest). If it is repeatable, something very weird is going on.
hp
-- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
