视图是否对PostgreSQL的性能有害？

#1 楼

这本书是错的。

从视图中进行选择与运行基础SQL语句一样快或慢–您可以使用explain analyze轻松地检查一下。

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

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

#2 楼

给你一个@a_horse解释的例子：这是方便且可靠的，并且比直接访问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，因此速度更快。 （但是在通用数据库中，第一个查询仍然只需要几毫秒。）


详细信息：

查询以返回查询的输出列名称和数据类型，表格或视图

#3 楼

编辑：

道歉，我需要撤回断言，即接受的答案并不总是正确的-它声明视图始终与编写为子查询的同一事物相同。我认为这是无可争辩的，我想我现在知道我的情况了。

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

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

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

，我的window函数的复杂性是不必要的。对此的说明计划：

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))

这是在火车服务表上使用主键索引，在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。

#4 楼

（我是视图的忠实拥护者，但是您在这里必须非常小心地使用PG，我希望鼓励大家也普遍使用PG中的视图，以提高查询/代码的易懂性和可维护性） （警告：）在Postgres中使用视图会导致我们真正的问题，并严重降低我们的性能，具体取决于我们在其中使用的功能：-（（至少对于v10.1 / upd：关于v12，请参见下文）。
（对于其他现代数据库系统（如Oracle）则不是这样。）因此，可能（这是我的问题）针对视图的任何筛选...导致针对底层的单个查询执行表。

（取决于您的确切意思-否-可能会实现中间临时表，而您可能不想成为中间临时表，或者未在谓词上施加下推...）
至少知道两个主要的“功能”，这使我们陷入了从Oracle到Postgres的迁移过程，因此我们不得不在一个项目中放弃PG：

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 -statement的窗口函数可能不可用
（通常用于视图中，例如，作为基于更复杂查询的报告源）




with-自v12起，子句可能会起作用
更新2020-08-01：在某些或所有情况下查看v12 with文档，现在已经存在需要长期优化的功能（但是，如上所示，如果在其中使用窗口功能，仍然可能无法正常工作）： />
但是，如果WITH查询是非递归且无副作用的（即
，它是一个不包含易失函数的SELECT），则可以将其
折叠成父查询，可以共同优化两个查询级别。默认情况下，如果父查询仅引用一次WITH查询，而不是多次引用WITH一次查询，则不会发生这种情况。您可以通过指定MATERIALIZED
强制单独计算WITH查询，或通过指定NOT
MATERIALIZED强制将其合并到父查询中来覆盖该决定。
后一种选择有可能会重复计算WITH查询，但是如果WITH查询的每次使用都只需要WITH查询的全部输出的一小部分，它仍然可以节省净额。

我们对with -clauses（PG v10）的解决方法
我们将所有“内联视图”转换为带有特殊前缀的实际视图，以免混淆列表或命名空间。视图，并且可以轻松地与原始“外部视图”相关：-/

我们针对窗口函数的解决方案
我们使用Oracle数据库成功实现了该解决方案。