主页 / dba / 问题 / 151169
Accepted
ARX
Asked: 2016-10-01 10:05:39 +0800 CST

视图对 PostgreSQL 的性能有害吗？

  • 772

以下摘自一本关于 db design 的书（Beginning Database Design ISBN: 0-7645-7490-6）：

使用视图的危险在于过滤针对视图的查询，期望读取非常大的表的一小部分。任何过滤都应该在视图中完成，因为对视图本身的任何过滤都是在视图中的查询完成执行后应用的。视图通常可用于加快开发过程，但从长远来看会完全破坏数据库性能。

以下是 PostgreSQL 9.5 文档的摘录：

自由使用视图是良好 SQL 数据库设计的一个关键方面。视图允许您在一致的接口后面封装表结构的细节，这些细节可能会随着应用程序的发展而改变。

这两个来源似乎相互矛盾（“不要用视图设计”与“用视图做设计”）。

但是，在 PG 中，视图是使用规则系统实现的。因此，可能（这是我的问题）对视图的任何过滤都被重写为视图内的过滤器，从而导致对基础表执行单个查询。

我的解释是否正确并且 PG 将 WHERE 子句组合到视图中和视图之外？还是一个接一个地单独运行它们？任何简短的、独立的、正确的（可编译的）示例？

postgresql optimization

4 个回答

  1. Best Answer

    书错了。

    从视图中选择与运行底层 SQL语句一样快或慢——您可以使用explain analyze.

    Postgres 优化器（以及许多其他现代 DBMS 的优化器）将能够将视图上的谓词下推到实际的视图语句中——只要这是一个简单的语句（同样，这可以使用 来验证explain analyze）。

    关于性能的“坏名声”——我认为——源于你过度使用视图并开始构建使用视图的视图时使用视图。与没有视图的手工定制的语句相比，这通常会导致语句做得太多，例如因为不需要一些中间表。在几乎所有情况下，优化器都不够聪明，无法删除那些不需要的表/连接或将谓词下推到多个视图级别（其他 DBMS 也是如此）。

    • 96

  2. 给你一个@a_horse解释的例子：

    Postgres 实现了信息模式，它由（有时是复杂的）视图组成，以标准化的形式提供有关 DB 对象的信息。这既方便又可靠——而且比直接访问 Postgres 目录表要昂贵得多。


    非常简单的示例，从信息模式中获取表的所有可见列......

    SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 'big'
AND    table_schema = 'public';

    ...来自系统目录：

    SELECT attname
FROM   pg_catalog.pg_attribute
WHERE  attrelid = 'public.big'::regclass
AND    attnum > 0
AND    NOT attisdropped;

    比较两者的查询计划和执行时间EXPLAIN ANALYZE。

    • 第一个查询基于视图information_schema.columns，它连接到我们根本不需要的多个表。

    • 第二个查询只扫描一张表pg_catalog.pg_attribute，因此要快得多。（但第一个查询在普通数据库中仍然只需要几毫秒。）

    细节：

    • 25

  3. 编辑：

    抱歉，我需要收回我的断言，即接受的答案并不总是正确的——它表明视图总是与写为子查询的相同内容相同。我认为这是无可争辩的，我想我现在知道我的情况发生了什么。

    我现在也认为原始问题有更好的答案。

    最初的问题是关于是否应该指导实践使用视图（而不是，例如，在可能需要维护两次或更多次的例程中重复 SQL）。

    我的回答是“如果您的查询使用窗口函数或其他任何会导致优化器在查询成为子查询时以不同方式对待查询的任何东西，则不会，因为创建子查询的行为（无论是否表示为视图）可能会降低性能如果您在运行时使用参数进行过滤。

    我的窗口函数的复杂性是不必要的。对此的解释计划：

    SELECT DISTINCT ts.train_service_key,
            pc.assembly_key,
            count(*) OVER 
              (PARTITION BY ts.train_service_key) AS train_records
FROM staging.train_service ts
   JOIN staging.portion_consist pc 
     USING (ds_code, train_service_key)
WHERE assembly_key = '185132';

    比这要便宜得多：

    SELECT *
FROM (SELECT DISTINCT ts.train_service_key,
            pc.assembly_key,
            count(*) OVER
              (PARTITION BY ts.train_service_key) AS train_records
FROM staging.train_service ts
   JOIN staging.portion_consist pc
     USING (ds_code, train_service_key)) AS query
WHERE assembly_key = '185132';

    希望这更具体和有帮助。

    根据我最近的经验（导致我找到这个问题），上面接受的答案在所有情况下都不正确。我有一个相对简单的查询，其中包含一个窗口函数：

    SELECT DISTINCT ts.train_service_key,
                pc.assembly_key,
                dense_rank() OVER (PARTITION BY ts.train_service_key
                ORDER BY pc.through_idx DESC, pc.first_portion ASC,
               ((CASE WHEN (NOT ts.primary_direction)
                 THEN '-1' :: INTEGER
                 ELSE 1
                 END) * pc.first_seq)) AS coach_block_idx
FROM (staging.train_service ts
JOIN staging.portion_consist pc USING (ds_code, train_service_key))

    如果我添加此过滤器：

    where assembly_key = '185132'

    我得到的解释计划如下：

    QUERY PLAN
Unique  (cost=11562.66..11568.77 rows=814 width=43)
  ->  Sort  (cost=11562.66..11564.70 rows=814 width=43)
    Sort Key: ts.train_service_key, (dense_rank() OVER (?))
    ->  WindowAgg  (cost=11500.92..11523.31 rows=814 width=43)
          ->  Sort  (cost=11500.92..11502.96 rows=814 width=35)
                Sort Key: ts.train_service_key, pc.through_idx DESC, pc.first_portion, ((CASE WHEN (NOT ts.primary_direction) THEN '-1'::integer ELSE 1 END * pc.first_seq))
                ->  Nested Loop  (cost=20.39..11461.57 rows=814 width=35)
                      ->  Bitmap Heap Scan on portion_consist pc  (cost=19.97..3370.39 rows=973 width=38)
                            Recheck Cond: (assembly_key = '185132'::text)
                            ->  Bitmap Index Scan on portion_consist_assembly_key_index  (cost=0.00..19.72 rows=973 width=0)
                                  Index Cond: (assembly_key = '185132'::text)
                      ->  Index Scan using train_service_pk on train_service ts  (cost=0.43..8.30 rows=1 width=21)
                            Index Cond: ((ds_code = pc.ds_code) AND (train_service_key = pc.train_service_key))

    这是使用 train service 表上的主键索引和 part_consist 表上的非唯一索引。它在 90 毫秒内执行。

    我创建了一个视图（将其粘贴在这里是绝对清楚的，但它实际上是视图中的查询）：

    CREATE OR REPLACE VIEW staging.v_unit_coach_block AS
SELECT DISTINCT ts.train_service_key,
            pc.assembly_key,
            dense_rank() OVER (PARTITION BY ts.train_service_key
              ORDER BY pc.through_idx DESC, pc.first_portion ASC, (
                (CASE
              WHEN (NOT ts.primary_direction)
                THEN '-1' :: INTEGER
              ELSE 1
              END) * pc.first_seq)) AS coach_block_idx
 FROM (staging.train_service ts
  JOIN staging.portion_consist pc USING (ds_code, train_service_key))

    当我使用相同的过滤器查询此视图时：

    select * from staging.v_unit_coach_block
where assembly_key = '185132';

    这是解释计划：

    QUERY PLAN
Subquery Scan on v_unit_coach_block  (cost=494217.13..508955.10     rows=3275 width=31)
Filter: (v_unit_coach_block.assembly_key = '185132'::text)
 ->  Unique  (cost=494217.13..500767.34 rows=655021 width=43)
    ->  Sort  (cost=494217.13..495854.68 rows=655021 width=43)
          Sort Key: ts.train_service_key, pc.assembly_key, (dense_rank() OVER (?))
          ->  WindowAgg  (cost=392772.16..410785.23 rows=655021 width=43)
                ->  Sort  (cost=392772.16..394409.71 rows=655021 width=35)
                      Sort Key: ts.train_service_key, pc.through_idx DESC, pc.first_portion, ((CASE WHEN (NOT ts.primary_direction) THEN '-1'::integer ELSE 1 END * pc.first_seq))
                      ->  Hash Join  (cost=89947.40..311580.26 rows=655021 width=35)
                            Hash Cond: ((pc.ds_code = ts.ds_code) AND (pc.train_service_key = ts.train_service_key))
                            ->  Seq Scan on portion_consist pc  (cost=0.00..39867.86 rows=782786 width=38)
                            ->  Hash  (cost=65935.36..65935.36 rows=1151136 width=21)
                                  ->  Seq Scan on train_service ts  (cost=0.00..65935.36 rows=1151136 width=21)

    这是对两个表进行全面扫描，需要 17 秒。

    在我遇到这个问题之前，我一直在自由地使用 PostgreSQL 的视图（理解了在接受的答案中表达的广泛持有的观点）。如果我需要预聚合过滤，我会特别避免使用视图，为此我会使用集合返回函数。

    我也知道 PostgreSQL 中的 CTE 在设计上是严格单独评估的，因此我不会像使用 SQL Server 那样使用它们，例如，它们似乎被优化为子查询。

    因此，我的回答是，在某些情况下，视图的执行与它们所基于的查询不完全相同，因此建议谨慎。我正在使用基于 PostgreSQL 9.6.6 的 Amazon Aurora。

    • 12

  4. （我是视图的超级粉丝，但你必须非常小心这里的 PG，我想鼓励大家在 PG中也使用视图，以提高查询/代码的可理解性和可维护性）

    实际上，可悲的是（警告：）在 Postgres 中使用视图给我们带来了真正的问题，并严重降低了我们的性能，具体取决于我们在其中使用的功能：-（（至少对于 v10.1 / upd：关于 v12 见下文）。（对于 Oracle 等其他现代数据库系统，情况并非如此。）

    因此，可能（这是我的问题）对视图的任何过滤......导致对基础表执行单个查询。

    （取决于你的确切意思 - 不 - 中间临时表可能会物化你可能不想成为或谓词没有被按下的地方......）

    我知道至少有两个主要的“特性”，它们让我们在从 Oracle 迁移到 Postgres的过程中感到失望，所以我们不得不在一个项目中放弃 PG：

    • CTE（with-clause 子查询/公用表表达式）（通常）可用于构建更复杂的查询（即使在较小的应用程序中），但在 PG 中设计为实现为“隐藏”优化器提示（生成例如非索引临时表）和因此违反了（对我和其他许多重要的）声明性 SQL 的概念（Oracle 文档）（更新 2020-08-02：对于PG v12+ ，这些示例查询计划现在应该是相同的）：例如

      • 简单查询：

            explain

      select * from pg_indexes where indexname='pg_am_name_index'

    /* result: 

    Nested Loop Left Join  (cost=12.38..26.67 rows=1 width=260)
      ...
      ->  Bitmap Index Scan on pg_class_relname_nsp_index  (cost=0.00..4.29 rows=2 width=0)
                                             Index Cond: (relname = 'pg_am_name_index'::name)
      ...
    */

      • 使用一些 CTE 重写：

            explain

      with 

      unfiltered as (
        select * from pg_indexes
      ) 

      select * from unfiltered where indexname='pg_am_name_index'

    /* result:

    CTE Scan on unfiltered  (cost=584.45..587.60 rows=1 width=288)
       Filter: (indexname = 'pg_am_name_index'::name)
       CTE unfiltered
         ->  Hash Left Join  (cost=230.08..584.45 rows=140 width=260)  
    ...
    */

      • 更多讨论来源等：https ://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

    • 带有over-statements 的窗口函数可能无法使用 （通常在视图中使用，例如作为基于更复杂查询的报告的来源）

    with-clauses 自 v12 起可能有效

    2020-08-01 更新：查看v12with文档在 您的部分或所有情况下，现在有一个长期需要的优化（但如果在那里使用窗口函数，仍然可能无法正常工作，如上文所述）：

    但是，如果 WITH 查询是非递归且无副作用的（即，它是不包含易失函数的 SELECT），则可以将其折叠到父查询中，从而允许两个查询级别的联合优化。默认情况下，如果父查询仅引用一次 WITH 查询，则会发生这种情况，但如果它多次引用 WITH 查询则不会。您可以通过指定MATERIALIZED 来强制单独计算 WITH 查询或指定NOT MATERIALIZED来覆盖该决定强制将其合并到父查询中。后一种选择有重复计算 WITH 查询的风险，但如果每次使用 WITH 查询只需要 WITH 查询完整输出的一小部分，它仍然可以节省净成本。

    - 子句的解决方法with(PG v10)

    我们会将所有“内联视图”转换为带有特殊前缀的真实视图，这样它们就不会弄乱视图的列表/命名空间，并且可以很容易地与原始“外部视图”相关联：-/

    我们的窗口函数解决方案

    我们使用 Oracle 数据库成功实现了它。

    • 3

相关问题