sql - 如何理解 postgres EXPLAIN 输出

Question

EXPLAIN SELECT a.name, m.name FROM Casting c JOIN Movie m ON c.m_id = m.m_id JOIN Actor a ON a.a_id = c.a_id AND c.a_id < 50;

输出

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=26.20..18354.49 rows=1090 width=27) (actual time=0.240..5.603 rows=1011 loops=1)
   ->  Nested Loop  (cost=25.78..12465.01 rows=1090 width=15) (actual time=0.236..4.046 rows=1011 loops=1)
         ->  Bitmap Heap Scan on casting c  (cost=25.35..3660.19 rows=1151 width=8) (actual time=0.229..1.059 rows=1011 loops=1)
               Recheck Cond: (a_id < 50)
               Heap Blocks: exact=989
               ->  Bitmap Index Scan on casting_a_id_index  (cost=0.00..25.06 rows=1151 width=0) (actual time=0.114..0.114 rows=1011 loops=1)
                     Index Cond: (a_id < 50)
         ->  Index Scan using movie_pkey on movie m  (cost=0.42..7.64 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=1011)
               Index Cond: (m_id = c.m_id)
   ->  Index Scan using actor_pkey on actor a  (cost=0.42..5.39 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=1011)
         Index Cond: (a_id = c.a_id)
 Planning time: 0.334 ms
 Execution time: 5.672 ms
(13 rows)

我想了解查询规划器的工作原理？我能够理解它选择的过程，但我不明白为什么？有人可以根据查询选择性和成本模型或任何影响选择的参数来解释这些查询中的查询优化器选择(查询处理算法的选择，连接顺序)吗？另外为什么在索引扫描后使用 Recheck Cond？

Answer 1

位图堆扫描有两个原因:

• PostgreSQL 必须检查找到的行对于当前事务是否可见。请记住，PostgreSQL 将旧行版本保留在表中直到 VACUUM。删除它们。此可见性信息未存储在索引中。

• 如果 work_mem不够大，无法包含每个表行一位的位图，PostgreSQL 每个表页使用一位，这会丢失一些信息。 PostgreSQL 需要检查有损 block 以查看 block 中的哪些行真正满足条件。
  你可以在使用EXPLAIN (ANALYZE, BUFFERS)时看到这个，然后 PostgreSQL 将显示是否存在有损匹配，请参阅 this example on rextester :

  ->  Bitmap Heap Scan on t  (cost=177.14..4719.43 rows=9383 width=0)
                             (actual time=2.130..144.729 rows=10001 loops=1)
        Recheck Cond: (val = 10)
        Rows Removed by Index Recheck: 738586
        Heap Blocks: exact=646 lossy=3305
        Buffers: shared hit=1891 read=2090
        ->  Bitmap Index Scan on t_val_idx  (cost=0.00..174.80 rows=9383 width=0)
                                            (actual time=1.978..1.978 rows=10001 loops=1)
              Index Cond: (val = 10)
              Buffers: shared read=30
  

我无法在这个答案中解释整个 PostgreSQL 优化器，但它所做的是尝试所有可能的方法来计算结果，估计每种方法的成本并选择最便宜的方案。

为了估计结果集的大小，它使用对象定义和表统计，其中包含有关列值如何分布的详细数据。

然后它会计算需要顺序读取和随机访问的磁盘 block 数量(I/O 成本)，以及需要处理多少表和索引行以及函数调用(CPU 成本)才能得出总计。可以配置总计中每个组件的权重。

通常最好的计划是通过首先应用最具选择性的条件来尽快减少结果行的数量。在您的情况下，这似乎是 casting.a_id < 50 .

如果外部(EXPLAIN 输出中的上部)表中的行数较小，则通常首选嵌套循环连接。