PostgreSQL嵌套/关联查询优化：获取匹配的最大oid_src值

表结构 #

CREATE TABLE mytable (
    tm BIGINT NOT NULL,
    oid_src BIGINT NOT NULL,
    rel SMALLINT NOT NULL,
    oid_dst BIGINT NOT NULL,
    CONSTRAINT PK_Src_Rel_Dst PRIMARY KEY(oid_src, rel, oid_dst)
);
CREATE INDEX IX_TM ON mytable (tm DESC);

查询需求 #

筛选满足以下条件的记录，并获取匹配记录中oid_src的最大值：

• oid_src = 1且rel为116或99
• 存在至少一条同oid_dst、同rel的记录，其oid_src属于集合：{100488, 100489, 100490, 100491, 100492, 100493, 100494, 100495, 100496, 100497, 100498, 100499}

各阶段查询及性能分析 #

1. 初始COUNT子查询（耗时2.7ms） #

SELECT te.tm, te.oid_src, te.rel, te.oid_dst
FROM mytable te
WHERE te.oid_src = 1 AND te.rel IN (116, 99)
AND (SELECT COUNT(*) FROM mytable te2
     WHERE te2.oid_src IN (100488, 100489, 100490, 100491, 100492, 100493, 100494,
                100495, 100496, 100497, 100498, 100499) 
       AND te2.oid_dst=te.oid_dst AND te2.rel=te.rel
   ) > 0
ORDER BY te.tm DESC
LIMIT 20;

2. EXISTS子查询（耗时2.2ms，无法获取最大oid_src） #

性能更优，但无法返回需求中的最大oid_src值：

SELECT te.tm, te.oid_src, te.rel, te.oid_dst
FROM "mytable" te
WHERE te.oid_src = 1 AND te.rel IN (116, 99)
AND Exists (SELECT 1 FROM  "mytable" te2 WHERE te2.oid_src IN (100488, 100489, 100490, 100491, 100492, 100493, 100494, 100495, 100496, 100497, 100498, 100499)
AND te2.oid_dst=te.oid_dst AND te2.rel=te.rel)
ORDER BY te.tm DESC
LIMIT 20;

对应的EXPLAIN ANALYZE结果：

Limit  (cost=0.84..56.60 rows=20 width=26) (actual time=0.120..2.175 rows=20 loops=1)
  ->  Nested Loop Semi Join  (cost=0.84..12921.50 rows=4634 width=26) (actual time=0.120..2.174 rows=20 loops=1)
        ->  Index Scan using ix_tm on "mytable" te  (cost=0.42..10555.40 rows=4634 width=26) (actual time=0.027..0.931 rows=109 loops=1)
              Filter: ((rel = ANY ('{116,99}'::integer[])) AND (oid_src = 1))
              Rows Removed by Filter: 4471
        ->  Index Only Scan using pk_src_rel_dst on "mytable" te2  (cost=0.42..6.29 rows=1 width=10) (actual time=0.011..0.011 rows=0 loops=109)
              Index Cond: ((oid_src = ANY ('{100488,100489,100490,100491,100492,100493,100494,100495,100496,100497,100498,100499}'::bigint[])) AND (rel = te.rel) AND (oid_dst = te.oid_dst))
              Heap Fetches: 4
Planning Time: 0.271 ms
Execution Time: 2.192 ms

3. 满足需求但性能较差的查询（耗时约30ms） #

能返回最大oid_src，但性能下降明显：

SELECT te.tm, max(te2.oid_src), te.rel, te.oid_dst
FROM mytable te, mytable te2
WHERE te.oid_src = 1 AND te.rel IN (116, 99) 
  AND te2.oid_src IN (100488, 100489, 100490, 100491, 100492, 100493, 100494,
                100495, 100496, 100497, 100498, 100499) 
  AND te2.oid_dst=te.oid_dst AND te2.rel=te.rel
GROUP BY te.tm, te.oid_src, te.rel, te.oid_dst
ORDER BY te.tm DESC
LIMIT 20;

对应的EXPLAIN ANALYZE结果：

Limit  (cost=8106.18..8106.23 rows=20 width=34) (actual time=28.779..28.784 rows=20 loops=1)
  ->  Sort  (cost=8106.18..8116.54 rows=4147 width=34) (actual time=28.778..28.781 rows=20 loops=1)
        Sort Key: te.tm DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=7954.36..7995.83 rows=4147 width=34) (actual time=23.854..26.601 rows=18910 loops=1)
              Group Key: te.rel, te.oid_dst
              Batches: 1  Memory Usage: 2577kB
              ->  Hash Join  (cost=1674.78..3477.46 rows=596920 width=34) (actual time=7.229..16.944 rows=29449 loops=1)
                    Hash Cond: ((te2.oid_dst = te.oid_dst) AND (te2.rel = te.rel))
                    ->  Index Only Scan using pk_src_rel_dst on mytable te2  (cost=0.42..1647.90 rows=29199 width=18) (actual time=0.010..3.558 rows=29499 loops=1)
                          Index Cond: (oid_src = ANY ('{100488,100489,100490,100491,100492,100493,100494,100495,100496,100497,100498,100499}'::bigint[]))
                          Heap Fetches: 2393
                    ->  Hash  (cost=1604.85..1604.85 rows=4634 width=26) (actual time=7.181..7.182 rows=21099 loops=1)
                          Buckets: 32768 (originally 8192)  Batches: 1 (originally 1)  Memory Usage: 1575kB
                          ->  Bitmap Heap Scan on mytable te  (cost=168.34..1604.85 rows=4634 width=26) (actual time=1.008..4.177 rows=21099 loops=1)
                                Recheck Cond: ((oid_src = 1) AND (rel = ANY ('{116,99}'::integer[])))
                                Heap Blocks: exact=467
                                ->  Bitmap Index Scan on pk_src_rel_dst  (cost=0.00..167.18 rows=4634 width=0) (actual time=0.966..0.966 rows=21099 loops=1)
                                      Index Cond: ((oid_src = 1) AND (rel = ANY ('{116,99}'::integer[])))
Planning Time: 0.311 ms
Execution Time: 29.347 ms

4. 优化后的CTE查询（耗时约3ms） #

已修正原SQL中的语法错误（添加FROM关键字），性能接近EXISTS子查询且满足需求：

WITH wanted AS (
    SELECT tew.tm, tew.oid_dst, tew.rel FROM mytable tew
    WHERE tew.oid_src=1 AND tew.rel IN (116, 99)
)
SELECT w.tm, MAX(te.oid_src), te.rel, te.oid_dst FROM mytable te
 INNER JOIN wanted w ON w.oid_dst=te.oid_dst AND w.rel=te.rel
 WHERE te.oid_src IN (100488, 100489, 100490, 100491, 100492, 100493, 100494, 100495, 100496, 100497, 100498, 100499) AND te.rel IN (116, 99)
 GROUP BY w.tm, te.oid_dst, te.rel
 ORDER BY w.tm DESC
 LIMIT 20;

对应的EXPLAIN ANALYZE结果：

Limit  (cost=9.91..15.74 rows=20 width=26) (actual time=2.433..2.807 rows=20 loops=1)
  ->  GroupAggregate  (cost=9.91..46041.22 rows=157903 width=26) (actual time=2.432..2.805 rows=20 loops=1)
        Group Key: tew.tm, te.oid_dst, te.rel
        ->  Incremental Sort  (cost=9.91..42883.16 rows=157903 width=26) (actual time=2.429..2.793 rows=37 loops=1)
              Sort Key: tew.tm DESC, te.oid_dst, te.rel
              Presorted Key: tew.tm
              Full-sort Groups: 2  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB
              ->  Nested Loop  (cost=0.85..36767.75 rows=157903 width=26) (actual time=0.122..2.775 rows=65 loops=1)
                    ->  Index Scan using ix_tm on mytable tew  (cost=0.42..10555.40 rows=4634 width=18) (actual time=0.022..0.976 rows=129 loops=1)
                          Filter: ((rel = ANY ('{116,99}'::integer[])) AND (oid_src = 1))
                          Rows Removed by Filter: 4589
                    ->  Memoize  (cost=0.43..6.30 rows=1 width=18) (actual time=0.011..0.014 rows=1 loops=129)
                          Cache Key: tew.oid_dst, tew.rel
                          Cache Mode: logical
                          Hits: 0  Misses: 129  Evictions: 0  Overflows: 0  Memory Usage: 13kB
                          ->  Index Only Scan using pk_src_rel_dst on "mytable" te  (cost=0.42..6.29 rows=1 width=18) (actual time=0.010..0.013 rows=1 loops=129)
                                Index Cond: ((oid_src = ANY ('{100488,100489,100490,100491,100492,100493,100494,100495,100496,100497,100498,100499}'::bigint[])) AND (rel = tew.rel) AND (oid_dst = tew.oid_dst))
                                Filter: (rel = ANY ('{116,99}'::integer[]))
                                Heap Fetches: 8
Planning Time: 0.363 ms
Execution Time: 2.951 ms

求助 #

目前已实现耗时约3ms的CTE查询，寻求进一步的优化建议或潜在问题排查方向。

内容的提问来源于stack exchange，提问作者BritBlaster