PostgreSQL consider rewrite subquery to nested query with order by - 1000x+ performance improve

[wenerme]

original

SELECT (
           SELECT MAX(rkv.id) AS id
           FROM kine AS rkv),
       (
           SELECT MAX(crkv.prev_revision) AS prev_revision
           FROM kine AS crkv
           WHERE crkv.name = 'compact_rev_key'),
       kv.id AS theid,
       kv.name,
       kv.created,
       kv.deleted,
       kv.create_revision,
       kv.prev_revision,
       kv.lease,
       kv.value,
       kv.old_value
FROM kine AS kv
         JOIN (
    SELECT MAX(mkv.id) AS id
    FROM kine AS mkv
    WHERE mkv.name LIKE '/registry/events/%'
    GROUP BY mkv.name) maxkv
              ON maxkv.id = kv.id
WHERE (kv.deleted = 0 OR 'f')
ORDER BY kv.id ASC
LIMIT 2;

execute in 53s

by using nested - same result

select *
from (SELECT (
                 SELECT MAX(rkv.id) AS id
                 FROM kine AS rkv),
             (
                 SELECT MAX(crkv.prev_revision) AS prev_revision
                 FROM kine AS crkv
                 WHERE crkv.name = 'compact_rev_key'),
             kv.id AS theid,
             kv.name,
             kv.created,
             kv.deleted,
             kv.create_revision,
             kv.prev_revision,
             kv.lease,
             kv.value,
             kv.old_value
      FROM kine AS kv
               JOIN (
          SELECT MAX(mkv.id) AS id
          FROM kine AS mkv
          WHERE mkv.name LIKE '/registry/events/%'
          GROUP BY mkv.name) maxkv
                    ON maxkv.id = kv.id
      WHERE (kv.deleted = 0 OR 'f')) t
ORDER BY id ASC
LIMIT 2;

execute in 4ms

full analyse https://explain.depesz.com/s/HUEf

[wenerme]

seems this query cause the problem

kine/pkg/drivers/generic/generic.go

Lines 43 to 57 in 31fbd94

	listSQL = fmt.Sprintf(`
	SELECT (%s), (%s), %s
	FROM kine AS kv
	JOIN (
	SELECT MAX(mkv.id) AS id
	FROM kine AS mkv
	WHERE
	mkv.name LIKE ?
	%%s
	GROUP BY mkv.name) maxkv
	ON maxkv.id = kv.id
	WHERE
	(kv.deleted = 0 OR ?)
	ORDER BY kv.id ASC
	`, revSQL, compactRevSQL, columns)

subquery first, then agg

[brandond]

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?

[wenerme]

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.

[wenerme]

@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.

[wenerme]

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

[brandond]

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.

[jgreat]

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?