postgresql – 如果查询在逻辑上相同,为什么计划会有所不同？

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了postgresql – 如果查询在逻辑上相同,为什么计划会有所不同？，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
我写了两个函数来回答 Seven Databases in Seven Weeks第3天的第一个作业问题.

Create a stored procedure where you can input a movie title or actor’s name you like,and it will return the top five suggestions based on either movies the actor has starred in or films with similar genres.

我的第一次尝试是正确但缓慢.返回结果可能需要2000毫秒.

  1. CREATE OR REPLACE FUNCTION suggest_movies(IN query text,IN result_limit integer DEFAULT 5)
  2.   RETURNS TABLE(movie_id integer,title text) AS
  3. $BODY$
  4. WITH suggestions AS (
  5.  
  6.   SELECT
  7.     actors.name AS entity_term,movies.movie_id AS suggestion_id,movies.title AS suggestion_title,1 AS rank
  8.   FROM actors
  9.   INNER JOIN movies_actors ON (actors.actor_id = movies_actors.actor_id)
  10.   INNER JOIN movies ON (movies.movie_id = movies_actors.movie_id)
  11.  
  12.   UNION ALL
  13.  
  14.   SELECT
  15.     searches.title AS entity_term,suggestions.movie_id AS suggestion_id,suggestions.title AS suggestion_title,RANK() OVER (PARTITION BY searches.movie_id ORDER BY cube_distance(searches.genre,suggestions.genre)) AS rank
  16.   FROM movies AS searches
  17.   INNER JOIN movies AS suggestions ON
  18.     (searches.movie_id <> suggestions.movie_id) AND
  19.     (cube_enlarge(searches.genre,2,18) @> suggestions.genre)
  20. )
  21. SELECT suggestion_id,suggestion_title
  22. FROM suggestions
  23. WHERE entity_term = query
  24. ORDER BY rank,suggestion_id
  25. LIMIT result_limit;
  26. $BODY$
  27. LANGUAGE sql;

我的第二次尝试是正确而快速的.我通过将过滤器从CTE推进到联合的每个部分来优化它.

我从外部查询中删除了这一行：

  1. WHERE entity_term = query

我在第一个内部查询中添加了这一行：

  1. WHERE actors.name = query

我将此行添加到第二个内部查询：

  1. WHERE movies.title = query

第二个函数大约需要10毫秒才能返回相同的结果.

除了函数定义之外,数据库没有什么不同.

为什么Postgresql会为这两个逻辑上等效的查询生成这样的不同计划？

第一个函数的EXPLAIN ANALYZE计划如下所示：

  1. Limit  (cost=7774.18..7774.19 rows=5 width=44) (actual time=1738.566..1738.567 rows=5 loops=1)
  2.    CTE suggestions
  3.      ->  Append  (cost=332.56..7337.19 rows=19350 width=285) (actual time=7.113..1577.823 rows=383024 loops=1)
  4.            ->  Subquery Scan on "*SELECT* 1"  (cost=332.56..996.80 rows=11168 width=33) (actual time=7.113..22.258 rows=11168 loops=1)
  5.                  ->  Hash Join  (cost=332.56..885.12 rows=11168 width=33) (actual time=7.110..19.850 rows=11168 loops=1)
  6.                        Hash Cond: (movies_actors.movie_id = movies.movie_id)
  7.                        ->  Hash Join  (cost=143.19..514.27 rows=11168 width=18) (actual time=4.326..11.938 rows=11168 loops=1)
  8.                              Hash Cond: (movies_actors.actor_id = actors.actor_id)
  9.                              ->  Seq Scan on movies_actors  (cost=0.00..161.68 rows=11168 width=8) (actual time=0.013..1.648 rows=11168 loops=1)
  10.                              ->  Hash  (cost=80.86..80.86 rows=4986 width=18) (actual time=4.296..4.296 rows=4986 loops=1)
  11.                                    Buckets: 1024  Batches: 1  Memory Usage: 252kB
  12.                                    ->  Seq Scan on actors  (cost=0.00..80.86 rows=4986 width=18) (actual time=0.009..1.681 rows=4986 loops=1)
  13.                        ->  Hash  (cost=153.61..153.61 rows=2861 width=19) (actual time=2.768..2.768 rows=2861 loops=1)
  14.                              Buckets: 1024  Batches: 1  Memory Usage: 146kB
  15.                              ->  Seq Scan on movies  (cost=0.00..153.61 rows=2861 width=19) (actual time=0.003..1.197 rows=2861 loops=1)
  16.            ->  Subquery Scan on "*SELECT* 2"  (cost=6074.48..6340.40 rows=8182 width=630) (actual time=1231.324..1528.188 rows=371856 loops=1)
  17.                  ->  WindowAgg  (cost=6074.48..6258.58 rows=8182 width=630) (actual time=1231.324..1492.106 rows=371856 loops=1)
  18.                        ->  Sort  (cost=6074.48..6094.94 rows=8182 width=630) (actual time=1231.307..1282.550 rows=371856 loops=1)
  19.                              Sort Key: searches.movie_id,(cube_distance(searches.genre,suggestions_1.genre))
  20.                              Sort Method: external sort  Disk: 21584kB
  21.                              ->  Nested Loop  (cost=0.27..3246.72 rows=8182 width=630) (actual time=0.047..909.096 rows=371856 loops=1)
  22.                                    ->  Seq Scan on movies searches  (cost=0.00..153.61 rows=2861 width=315) (actual time=0.003..0.676 rows=2861 loops=1)
  23.                                    ->  Index Scan using movies_genres_cube on movies suggestions_1  (cost=0.27..1.05 rows=3 width=315) (actual time=0.016..0.277 rows=130 loops=2861)
  24.                                          Index Cond: (cube_enlarge(searches.genre,2::double precision,18) @> genre)
  25.                                          Filter: (searches.movie_id <> movie_id)
  26.                                          Rows Removed by Filter: 1
  27.    ->  Sort  (cost=436.99..437.23 rows=97 width=44) (actual time=1738.565..1738.566 rows=5 loops=1)
  28.          Sort Key: suggestions.rank,suggestions.suggestion_id
  29.          Sort Method: top-N heapsort  Memory: 25kB
  30.          ->  CTE Scan on suggestions  (cost=0.00..435.38 rows=97 width=44) (actual time=1281.905..1738.531 rows=43 loops=1)
  31.                Filter: (entity_term = 'Die Hard'::text)
  32.                Rows Removed by Filter: 382981
  33.  Total runtime: 1746.623 ms

第二个查询的EXPLAIN ANALYZE计划如下所示：

  1. Limit  (cost=43.74..43.76 rows=5 width=44) (actual time=1.231..1.234 rows=5 loops=1)
  2.    CTE suggestions
  3.      ->  Append  (cost=4.86..43.58 rows=5 width=391) (actual time=1.029..1.141 rows=43 loops=1)
  4.            ->  Subquery Scan on "*SELECT* 1"  (cost=4.86..20.18 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
  5.                  ->  Nested Loop  (cost=4.86..20.16 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
  6.                        ->  Nested Loop  (cost=4.58..19.45 rows=2 width=18) (actual time=0.045..0.045 rows=0 loops=1)
  7.                              ->  Index Scan using actors_name on actors  (cost=0.28..8.30 rows=1 width=18) (actual time=0.045..0.045 rows=0 loops=1)
  8.                                    Index Cond: (name = 'Die Hard'::text)
  9.                              ->  Bitmap Heap Scan on movies_actors  (cost=4.30..11.13 rows=2 width=8) (never executed)
  10.                                    Recheck Cond: (actor_id = actors.actor_id)
  11.                                    ->  Bitmap Index Scan on movies_actors_actor_id  (cost=0.00..4.30 rows=2 width=0) (never executed)
  12.                                          Index Cond: (actor_id = actors.actor_id)
  13.                        ->  Index Scan using movies_pkey on movies  (cost=0.28..0.35 rows=1 width=19) (never executed)
  14.                              Index Cond: (movie_id = movies_actors.movie_id)
  15.            ->  Subquery Scan on "*SELECT* 2"  (cost=23.31..23.40 rows=3 width=630) (actual time=0.982..1.081 rows=43 loops=1)
  16.                  ->  WindowAgg  (cost=23.31..23.37 rows=3 width=630) (actual time=0.982..1.064 rows=43 loops=1)
  17.                        ->  Sort  (cost=23.31..23.31 rows=3 width=630) (actual time=0.963..0.971 rows=43 loops=1)
  18.                              Sort Key: searches.movie_id,suggestions_1.genre))
  19.                              Sort Method: quicksort  Memory: 28kB
  20.                              ->  Nested Loop  (cost=4.58..23.28 rows=3 width=630) (actual time=0.808..0.916 rows=43 loops=1)
  21.                                    ->  Index Scan using movies_title on movies searches  (cost=0.28..8.30 rows=1 width=315) (actual time=0.025..0.027 rows=1 loops=1)
  22.                                          Index Cond: (title = 'Die Hard'::text)
  23.                                    ->  Bitmap Heap Scan on movies suggestions_1  (cost=4.30..14.95 rows=3 width=315) (actual time=0.775..0.844 rows=43 loops=1)
  24.                                          Recheck Cond: (cube_enlarge(searches.genre,18) @> genre)
  25.                                          Filter: (searches.movie_id <> movie_id)
  26.                                          Rows Removed by Filter: 1
  27.                                          ->  Bitmap Index Scan on movies_genres_cube  (cost=0.00..4.29 rows=3 width=0) (actual time=0.750..0.750 rows=44 loops=1)
  28.                                                Index Cond: (cube_enlarge(searches.genre,18) @> genre)
  29.    ->  Sort  (cost=0.16..0.17 rows=5 width=44) (actual time=1.230..1.231 rows=5 loops=1)
  30.          Sort Key: suggestions.rank,suggestions.suggestion_id
  31.          Sort Method: top-N heapsort  Memory: 25kB
  32.          ->  CTE Scan on suggestions  (cost=0.00..0.10 rows=5 width=44) (actual time=1.034..1.187 rows=43 loops=1)
  33.  Total runtime: 1.410 ms
没有针对CTE的自动谓词下推

Postgresql 9.3不为CTE做predicate pushdown.

执行谓词下推的优化器可以将where子句移动到内部查询中.目标是尽早过滤掉不相关的数据.只要新查询在逻辑上等效,引擎仍然会获取所有相关数据,因此只能更快地生成正确的结果.

核心开发人员Tom Lane暗示难以确定pgsql-performance mailing list的逻辑等价.

CTEs are also treated as optimization fences; this is not so much an
optimizer limitation as to keep the semantics sane when the CTE contains
a writable query.

优化器不区分只读CTE和可写CTE,因此在考虑计划时过于保守. ‘fence’处理阻止优化器移动CTE中的where子句,尽管我们可以看到它是安全的.

我们可以等待Postgresql团队改进CTE优化,但是为了获得良好的性能,你必须改变你的写作风格.

重写性能

这个问题已经显示了一种获得更好计划的方法.复制过滤条件基本上是硬编码谓词下推的效果.

在这两个计划中,引擎将结果行复制到工作表,以便对它们进行排序.工作表越大,查询越慢.

第一个计划将基表中的所有行复制到工作表,并扫描它以查找结果.为了使事情变得更慢,引擎必须扫描整个工作表,因为它没有索引.

这是一项荒谬的不必要工作.当基表中估计的19350行中只有估计的5个匹配行时,它会两次读取基表中的所有数据以找到答案.

第二个计划使用索引来查找匹配的行,并将这些行复制到工作表中.该索引为我们有效地过滤了数据.

在The Art of sql的page 85中,StéphaneFaroult提醒我们用户的期望.

To a very large extent,end users adjust thier patience to the number of rows they expect: when they ask for one needle,they pay little attention to the size of the haystack.

第二个计划与针一起扩展,因此更有可能让您的用户满意.

重写可维护性

新查询难以维护,因为您可以通过更改一个过滤器epxression而不是另一个过滤器epxression来引入缺陷.

如果我们只能写一次并且仍能获得良好的表现,那不是很好吗？

我们可以.优化器对子查询进行谓词下推.

一个更简单的例子更容易解释.

  1. CREATE TABLE a (c INT);
  2.  
  3. CREATE TABLE b (c INT);
  4.  
  5. CREATE INDEX a_c ON a(c);
  6.  
  7. CREATE INDEX b_c ON b(c);
  8.  
  9. INSERT INTO a SELECT 1 FROM generate_series(1,1000000);
  10.  
  11. INSERT INTO b SELECT 2 FROM a;
  12.  
  13. INSERT INTO a SELECT 3;

这将创建两个表,每个表都有一个索引列.他们一起包含一百万个,一百万个,一个三个.

您可以使用以下任一查询找到针3.

  1. -- CTE
  2. EXPLAIN ANALYZE
  3. WITH cte AS (
  4.   SELECT c FROM a
  5.   UNION ALL
  6.   SELECT c FROM b
  7. )
  8. SELECT c FROM cte WHERE c = 3;
  9.  
  10. -- Subquery
  11. EXPLAIN ANALYZE
  12. SELECT c
  13. FROM (
  14.   SELECT c FROM a
  15.   UNION ALL
  16.   SELECT c FROM b
  17. ) AS subquery
  18. WHERE c = 3;

CTE的计划很慢.引擎扫描三个表并读取大约四百万行.它需要近1000毫秒.

  1. CTE Scan on cte  (cost=33275.00..78275.00 rows=10000 width=4) (actual time=471.412..943.225 rows=1 loops=1)
  2.   Filter: (c = 3)
  3.   Rows Removed by Filter: 2000000
  4.   CTE cte
  5.     ->  Append  (cost=0.00..33275.00 rows=2000000 width=4) (actual time=0.011..409.573 rows=2000001 loops=1)
  6.           ->  Seq Scan on a  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.010..114.869 rows=1000001 loops=1)
  7.           ->  Seq Scan on b  (cost=0.00..18850.00 rows=1000000 width=4) (actual time=5.530..104.674 rows=1000000 loops=1)
  8. Total runtime: 948.594 ms

子查询的计划很快.引擎只是寻找每个指数.它需要不到一毫秒.

  1. Append  (cost=0.42..8.88 rows=2 width=4) (actual time=0.021..0.038 rows=1 loops=1)
  2.   ->  Index Only Scan using a_c on a  (cost=0.42..4.44 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)
  3.         Index Cond: (c = 3)
  4.         Heap Fetches: 1
  5.   ->  Index Only Scan using b_c on b  (cost=0.42..4.44 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1)
  6.         Index Cond: (c = 3)
  7.         Heap Fetches: 0
  8. Total runtime: 0.065 ms

有关交互式版本,请参阅SQLFiddle.

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问