PostgreSQL consider rewrite subquery to nested query with order by - 1000x+ performance improve about kine HOT 7 CLOSED

I like the PR but I'm going to wait on adding some automated tests against a couple different DB engines and versions before I merge it.

from kine.

seems this query cause the problem

subquery first, then agg

from kine.

What version of postgres did you test this on? I've noticed that newer versions perform much better; it's possible that the query optimizer in these versions handles the current query better.

Also, I see that your PR changed the generic query - have you tested it on sqlite and MySQL as well?

from kine.

PostgreSQL 13.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.3.0) 9.3.0, 64-bit

I use aliyun rds pg before, found the performance problem, then migrate to self host pg 13.

from kine.

@brandond not tested on sqlite and MySQL, but I expected to see same performance if the engine already handle the optimization on this situation. If the subquery not materialized, then the performance should better than before.I think this syntax is generic enough, should not cause any problem.

from kine.

This is another cluster using aliyun rds pg, a lot slow query

from kine.

I'm seeing k3s server throwing a lot of "TRACE" messages for long transaction commit times when using Postgres as a backend. Could this issue be related?

Any update on PR #78 to fix it?

from kine.