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@xxxxxx>:
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@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
