PostgreSQL中视图min(PK)为何比order by PK limit 1慢数千倍

将4张表关联为视图后，执行min(ak)的速度比select ak order by ak limit 1慢近2000倍，explain (analyze, buffers)显示前者实际执行时间是后者的31000倍左右。

测试结果 #

testv=> select min(ak) from tjoin;
 min 
-----
   1
(1 row)

Time: 2541.766 ms (00:02.542)
testv=> select ak from tjoin order by ak limit 1;
 ak 
----
  1
(1 row)

Time: 1.449 ms

表结构 #

testv=> \d tbla
                             Table "public.tbla"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 apk    | bigint  |           | not null | nextval('tbla_apk_seq'::regclass)
 aval   | integer |           | not null | 
Indexes:
    "tbla_pkey" PRIMARY KEY, btree (apk)
Referenced by:
    TABLE "tblb" CONSTRAINT "tblb_ak_fkey" FOREIGN KEY (ak) REFERENCES tbla(apk) ON DELETE CASCADE

testv=> \d tblb
                             Table "public.tblb"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 bpk    | bigint  |           | not null | nextval('tblb_bpk_seq'::regclass)
 ak     | bigint  |           | not null | 
 bval   | integer |           | not null | 
Indexes:
    "tblb_pkey" PRIMARY KEY, btree (bpk)
Foreign-key constraints:
    "tblb_ak_fkey" FOREIGN KEY (ak) REFERENCES tbla(apk) ON DELETE CASCADE
Referenced by:
    TABLE "tblc" CONSTRAINT "tblc_bk_fkey" FOREIGN KEY (bk) REFERENCES tblb(bpk) ON DELETE CASCADE
    TABLE "tbld" CONSTRAINT "tbld_bk_fkey" FOREIGN KEY (bk) REFERENCES tblb(bpk) ON DELETE CASCADE

testv=> \d tblc
                Table "public.tblc"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 bk     | bigint  |           | not null | 
 cval   | integer |           | not null | 
Foreign-key constraints:
    "tblc_bk_fkey" FOREIGN KEY (bk) REFERENCES tblb(bpk) ON DELETE CASCADE

testv=> \d tbld
                Table "public.tbld"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 bk     | bigint  |           | not null | 
 dval   | integer |           | not null | 
Foreign-key constraints:
    "tbld_bk_fkey" FOREIGN KEY (bk) REFERENCES tblb(bpk) ON DELETE CASCADE

视图定义 #

JOIN方式视图 #

testv=> \d+ tjoin
                            View "public.tjoin"
 Column |  Type   | Collation | Nullable | Default | Storage | Description 
--------+---------+-----------+----------+---------+---------+-------------
 ak     | bigint  |           |          |         | plain   | 
 bk     | bigint  |           |          |         | plain   | 
 aval   | integer |           |          |         | plain   | 
 bval   | integer |           |          |         | plain   | 
 cval   | integer |           |          |         | plain   | 
 dval   | integer |           |          |         | plain   | 
View definition:
 SELECT a.apk AS ak,
    b.bpk AS bk,
    a.aval,
    b.bval,
    c.cval,
    d.dval
   FROM tbla a
     JOIN tblb b ON a.apk = b.ak
     JOIN tblc c ON b.bpk = c.bk
     JOIN tbld d ON b.bpk = d.bk;

WHERE子句方式视图 #

testv=> \d+ testv
                            View "public.testv"
 Column |  Type   | Collation | Nullable | Default | Storage | Description 
--------+---------+-----------+----------+---------+---------+-------------
 ak     | bigint  |           |          |         | plain   | 
 bk     | bigint  |           |          |         | plain   | 
 aval   | integer |           |          |         | plain   | 
 bval   | integer |           |          |         | plain   | 
 cval   | integer |           |          |         | plain   | 
 dval   | integer |           |          |         | plain   | 
View definition:
 SELECT a.apk AS ak,
    b.bpk AS bk,
    a.aval,
    b.bval,
    c.cval,
    d.dval
   FROM tbla a,
    tblb b,
    tblc c,
    tbld d
  WHERE b.ak = a.apk AND c.bk = b.bpk AND d.bk = b.bpk;

执行计划 #

MIN()查询执行计划 #

testv=> explain (analyze, buffers) select min(ak) from tjoin;
                                                                                 QUERY PLAN                                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=482949.93..482949.94 rows=1 width=8) (actual time=3035.563..3052.715 rows=1 loops=1)
   Buffers: shared hit=171941
   ->  Gather  (cost=482949.71..482949.92 rows=2 width=8) (actual time=3024.062..3052.711 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=171941
         ->  Partial Aggregate  (cost=481949.71..481949.72 rows=1 width=8) (actual time=3017.701..3018.181 rows=1 loops=3)
               Buffers: shared hit=171941
               ->  Parallel Hash Join  (cost=327199.32..471533.13 rows=4166633 width=8) (actual time=2094.352..2947.066 rows=3333333 loops=3)
                     Hash Cond: (c.bk = b.bpk)
                     Buffers: shared hit=171941
                     ->  Parallel Seq Scan on tblc c  (cost=0.00..95722.40 rows=4166740 width=8) (actual time=0.005..95.114 rows=3333333 loops=3)
                           Buffers: shared hit=54055
                     ->  Parallel Hash  (cost=275116.72..275116.72 rows=4166608 width=24) (actual time=2082.611..2083.090 rows=3333333 loops=3)
                           Buckets: 16777216  Batches: 1  Memory Usage: 679072kB
                           Buffers: shared hit=117768
                           ->  Hash Join  (cost=157474.58..275116.72 rows=4166608 width=24) (actual time=577.793..1565.587 rows=3333333 loops=3)
                                 Hash Cond: (b.ak = a.apk)
                                 Buffers: shared hit=117768
                                 ->  Parallel Hash Join  (cost=157446.08..264104.51 rows=4166608 width=24) (actual time=577.436..1343.833 rows=3333333 loops=3)
                                       Hash Cond: (d.bk = b.bpk)
                                       Buffers: shared hit=117750
                                       ->  Parallel Seq Scan on tbld d  (cost=0.00..95721.08 rows=4166608 width=8) (actual time=0.004..92.871 rows=3333333 loops=3)
                                             Buffers: shared hit=54055
                                       ->  Parallel Hash  (cost=105362.15..105362.15 rows=4166715 width=16) (actual time=569.707..569.707 rows=3333333 loops=3)
                                             Buckets: 16777216  Batches: 1  Memory Usage: 600320kB
                                             Buffers: shared hit=63695
                                             ->  Parallel Seq Scan on tblb b  (cost=0.00..105362.15 rows=4166715 width=16) (actual time=0.016..124.124 rows=3333333 loops=3)
                                                   Buffers: shared hit=63695
                                 ->  Hash  (cost=16.00..16.00 rows=1000 width=8) (actual time=0.319..0.319 rows=1000 loops=3)
                                       Buckets: 1024  Batches: 1  Memory Usage: 48kB
                                       Buffers: shared hit=18
                                       ->  Seq Scan on tbla a  (cost=0.00..16.00 rows=1000 width=8) (actual time=0.110..0.209 rows=1000 loops=3)
                                             Buffers: shared hit=18
 Planning:
   Buffers: shared hit=24
 Planning Time: 0.467 ms
 Execution Time: 3052.767 ms
(38 rows)

Time: 3053.871 ms (00:03.054)

ORDER BY LIMIT 1查询执行计划 #

testv=> explain (analyze, buffers) select ak from tjoin order by ak limit 1;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..300017.88 rows=1 width=8) (actual time=0.064..0.065 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Nested Loop  (cost=0.28..3000152047967.15 rows=9999920 width=8) (actual time=0.063..0.064 rows=1 loops=1)
         Join Filter: (b.bpk = c.bk)
         Buffers: shared hit=6
         ->  Nested Loop  (cost=0.28..1500146619277.46 rows=9999860 width=24) (actual time=0.054..0.055 rows=1 loops=1)
               Join Filter: (b.bpk = d.bk)
               Buffers: shared hit=5
               ->  Nested Loop  (cost=0.28..150190465.71 rows=10000115 width=16) (actual time=0.021..0.021 rows=1 loops=1)
                     Join Filter: (a.apk = b.ak)
                     Buffers: shared hit=4
                     ->  Index Only Scan using tbla_pkey on tbla a  (cost=0.28..44.27 rows=1000 width=8) (actual time=0.006..0.007 rows=1 loops=1)
                           Heap Fetches: 1
                           Buffers: shared hit=3
                     ->  Materialize  (cost=0.00..213696.73 rows=10000115 width=16) (actual time=0.013..0.013 rows=1 loops=1)
                           Buffers: shared hit=1
                           ->  Seq Scan on tblb b  (cost=0.00..163696.15 rows=10000115 width=16) (actual time=0.009..0.010 rows=1 loops=1)
                                 Buffers: shared hit=1
               ->  Materialize  (cost=0.00..204052.90 rows=9999860 width=8) (actual time=0.032..0.032 rows=1 loops=1)
                     Buffers: shared hit=1
                     ->  Seq Scan on tbld d  (cost=0.00..154053.60 rows=9999860 width=8) (actual time=0.006..0.006 rows=1 loops=1)
                           Buffers: shared hit=1
         ->  Materialize  (cost=0.00..204057.62 rows=10000175 width=8) (actual time=0.009..0.009 rows=1 loops=1)
               Buffers: shared hit=1
               ->  Seq Scan on tblc c  (cost=0.00..154056.75 rows=10000175 width=8) (actual time=0.005..0.005 rows=1 loops=1)
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=24
 Planning Time: 0.573 ms
 Execution Time: 0.098 ms
(30 rows)

Time: 1.408 ms

补充说明 #

• 多次测试不同表规模，每次测试前删除所有表和视图，结果一致：min(ak)始终远慢于select ak order by ak limit 1。
• 本次测试使用全量填充表，tblc和tbld每条记录都对应tblb主键值；实际业务中表可能非全量填充，因此拆分为外键关联的独立表。
• 逻辑上min(ak)仅依赖tbla的主键（BTREE索引），理论上与order by ak limit 1等价，但实际执行速度差异巨大。
• 测试环境：16核MacBook Pro M4，128GB内存，shared_buffers和work_mem均为4GB，重启后无交换，执行时剩余内存超30GB。

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