sql - 奇怪的 pgsql 查询性能

Question

我有这样的关系

R ( EDGE INTEGER, DIHEDRAL INTEGER, FACE INTEGER , VALENCY INTEGER)

我测试了两次，64 行表 R 和 128 行表 R。但更简单的一个比第二个花费的时间要多得多。解释如下(它在 explain.depesz.com 上显示错误)。谁能帮我检查一下为什么？谢谢。

计划 64 行:

HashAggregate  (cost=260.16..260.17 rows=1 width=12) (actual rows=64 loops=1)
->  Nested Loop  (cost=89.44..260.15 rows=1 width=12) (actual rows=256 loops=1)
     Join Filter: ((f1.face < f2.face) AND (e3.edge <> f1.edge) AND (e4.edge <> e3.edge) AND (f1.edge = f2.edge) AND (f1.face =
e3.face))
     Rows Removed by Join Filter: 142606080
     ->  Nested Loop  (cost=41.91..167.59 rows=1 width=16) (actual rows=557056 loops=1)
           ->  Nested Loop  (cost=41.91..125.71 rows=1 width=8) (actual rows=256 loops=1)
                 Join Filter: ((e5.edge <> f2.edge) AND (e5.edge <> e2.edge) AND (e2.face = e5.face))
                 Rows Removed by Join Filter: 1113856
                 ->  Hash Join  (cost=41.91..83.73 rows=1 width=16) (actual rows=512 loops=1)
                       Hash Cond: (f2.face = e2.face)
                       Join Filter: (e2.edge <> f2.edge)
                       Rows Removed by Join Filter: 256
                       ->  Seq Scan on r f2  (cost=0.00..41.76 rows=12 width=8) (actual rows=384 loops=1)
                             Filter: (valency = 3)
                             Rows Removed by Filter: 1920
                       ->  Hash  (cost=41.76..41.76 rows=12 width=8) (actual rows=2176 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 85kB
                             ->  Seq Scan on r e2  (cost=0.00..41.76 rows=12 width=8) (actual rows=2176 loops=1)
                                   Filter: (dihedral = 2)
                                   Rows Removed by Filter: 128
                 ->  Seq Scan on r e5  (cost=0.00..41.76 rows=12 width=8) (actual rows=2176 loops=512)
                       Filter: (dihedral = 2)
                       Rows Removed by Filter: 128
           ->  Seq Scan on r e3  (cost=0.00..41.76 rows=12 width=8) (actual rows=2176 loops=256)
                 Filter: (dihedral = 2)
                 Rows Removed by Filter: 128
     ->  Hash Join  (cost=47.53..92.32 rows=11 width=16) (actual rows=256 loops=557056)
           Hash Cond: (e4.face = f1.face)
           Join Filter: (e4.edge <> f1.edge)
           Rows Removed by Join Filter: 128
           ->  Seq Scan on r e4  (cost=0.00..36.01 rows=2301 width=8) (actual rows=2304 loops=557056)
           ->  Hash  (cost=47.52..47.52 rows=1 width=8) (actual rows=128 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 5kB
                 ->  Seq Scan on r f1  (cost=0.00..47.52 rows=1 width=8) (actual rows=128 loops=1)
                       Filter: ((valency = 3) AND (dihedral = 1))
                       Rows Removed by Filter: 2176
Total runtime: 159268.541 ms
(37 rows)

规划 128 行

HashAggregate  (cost=501.28..501.29 rows=1 width=12) (actual rows=128 loops=1)
->  Nested Loop  (cost=171.98..501.27 rows=2 width=12) (actual rows=512 loops=1)
     Join Filter: ((e3.edge <> f1.edge) AND (e4.edge <> e3.edge) AND (f1.face = e3.face))
     Rows Removed by Join Filter: 2227712
     ->  Seq Scan on r e3  (cost=0.00..80.31 rows=22 width=8) (actual rows=4352 loops=1)
           Filter: (dihedral = 2)
           Rows Removed by Filter: 256
     ->  Materialize  (cost=171.98..420.08 rows=2 width=20) (actual rows=512 loops=4352)
           ->  Nested Loop  (cost=171.98..420.07 rows=2 width=20) (actual rows=512 loops=1)
                 Join Filter: ((f1.face < f2.face) AND (f1.edge = f2.edge))
                 Rows Removed by Join Filter: 261632
                 ->  Nested Loop  (cost=80.59..242.23 rows=1 width=8) (actual rows=512 loops=1)
                       Join Filter: ((e5.edge <> f2.edge) AND (e5.edge <> e2.edge) AND (e2.face = e5.face))
                       Rows Removed by Join Filter: 4455936
                       ->  Seq Scan on r e5  (cost=0.00..80.31 rows=22 width=8) (actual rows=4352 loops=1)
                             Filter: (dihedral = 2)
                             Rows Removed by Filter: 256
                       ->  Materialize  (cost=80.59..161.05 rows=2 width=16) (actual rows=1024 loops=4352)
                             ->  Hash Join  (cost=80.59..161.04 rows=2 width=16) (actual rows=1024 loops=1)
                                   Hash Cond: (f2.face = e2.face)
                                   Join Filter: (e2.edge <> f2.edge)
                                   Rows Removed by Join Filter: 512
                                   ->  Seq Scan on r f2  (cost=0.00..80.31 rows=22 width=8) (actual rows=768 loops=1)
                                         Filter: (valency = 3)
                                         Rows Removed by Filter: 3840
                                   ->  Hash  (cost=80.31..80.31 rows=22 width=8) (actual rows=4352 loops=1)
                                         Buckets: 1024  Batches: 1  Memory Usage: 170kB
                                         ->  Seq Scan on r e2  (cost=0.00..80.31 rows=22 width=8) (actual rows=4352 loops=1)
                                               Filter: (dihedral = 2)
                                               Rows Removed by Filter: 256
                 ->  Hash Join  (cost=91.39..177.51 rows=22 width=16) (actual rows=512 loops=512)
                       Hash Cond: (e4.face = f1.face)
                       Join Filter: (e4.edge <> f1.edge)
                       Rows Removed by Join Filter: 256
                       ->  Seq Scan on r e4  (cost=0.00..69.25 rows=4425 width=8) (actual rows=4608 loops=512)
                       ->  Hash  (cost=91.38..91.38 rows=1 width=8) (actual rows=256 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 10kB
                             ->  Seq Scan on r f1  (cost=0.00..91.38 rows=1 width=8) (actual rows=256 loops=1)
                                   Filter: ((valency = 3) AND (dihedral = 1))
                                   Rows Removed by Filter: 4352
 Total runtime: 1262.761 ms
 (41 rows)

Answer 1

查询规划器使用行计数/索引大小/等方面的统计信息。估计如何从查询中获得最佳性能。在查询之后立即批量插入行可能不会显示最佳性能，因为这些统计信息可能已过时。

为确保计划者做出明智的选择，您需要调用 ANALYZE在运行您的 EXPLAIN 查询之前。

在您的特定场景中，规划者很可能在第一种情况(64 行)中做出了错误的选择，而在第二种情况(128 行)中做出了正确的选择。