主页 / dba / 问题 / 33007
Accepted
dezso
Asked: 2013-01-17 02:09:33 +0800 CST

具有 LIMIT 的不同优先级伪列解决方案的性能

  • 772

我想知道关于stackoverflow 问题的答案中提供的解决方案的相对性能，我决定运行一些测试。

OP 希望在给定一组优先级递减的条件的情况下获得第一个匹配行。两种解决方案都涉及一个伪列，但一个（我的）涉及将多个SELECT语句UNION ALL组合在一起，而另一个则构建一个CASE表达式。

我分享我的结果，希望有人会觉得这有用。

postgresql index

2 个回答

  1. Best Answer

    您的测试设计有缺陷。您正在测试不正确的结果。

    我在您所指的 SO 上添加了问题的答案。

    在您的CASE 版本中，您不能添加ORDER BY col1, col2. 必须是ORDER BY precedence。但它仍然是不正确的。您必须对ORDER BY各个条件的分数求和才能首先获得满足最多条件的行。

    同样，您的UNION ALL 版本会产生不正确的结果。

    然而，这些似乎都不是必需的，有一个更简单、更快速的解决方案，使用UNION ALL. 参考我对SO问题的回答或者使用sqlfiddle来玩

    • 5

  2. 对于仅包含几行的表，我看不出执行时间有任何差异（虽然查询计划不同，但结果未显示）。

    然后我重新使用了之前实验中的测试表。该表包含 436421 行，结构如下：

                 Table "public.avg_test"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 col1   | timestamp without time zone |
 col2   | integer                     |

    现在测试运行如下（选择的条件返回不到所有行的 2% 没有LIMIT）：

    案例版本

    EXPLAIN ANALYZE SELECT 
      col1
    , col2
    , CASE 
          WHEN col1 = '2012-01-01 12:00:00' THEN 1
          WHEN col2 > 98 THEN 2 ELSE 100000 
      END AS precedence 
FROM avg_test
ORDER BY col1, col2
LIMIT 1;

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=11090.78..11090.78 rows=1 width=12) (actual time=144.559..144.559 rows=1 loops=1)
   ->  Sort  (cost=11090.78..12182.13 rows=436539 width=12) (actual time=144.557..144.557 rows=1 loops=1)
         Sort Key: col1, col2
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on avg_test  (cost=0.00..8908.09 rows=436539 width=12) (actual time=0.057..88.477 rows=436421 loops=1)
 Total runtime: 144.595 ms
(6 rows)

    联合所有版本

    EXPLAIN ANALYZE 
SELECT 
      col1
    , col2
    , 1 AS precedence 
FROM avg_test
WHERE col1 = '2012-01-01 12:00:00'

UNION ALL

SELECT 
      col1
    , col2
    , 2 AS precedence 
FROM avg_test
WHERE col2 > 98

ORDER BY col1, col2
LIMIT 1
;


                                                             QUERY PLAN                                                      
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=15766.57..15766.57 rows=1 width=16) (actual time=85.771..85.771 rows=1 loops=1)
   ->  Sort  (cost=15766.57..15788.75 rows=8873 width=16) (actual time=85.769..85.769 rows=1 loops=1)
         Sort Key: public.avg_test.col1, public.avg_test.col2
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Result  (cost=0.00..15722.20 rows=8873 width=16) (actual time=0.056..84.276 rows=8802 loops=1)
               ->  Append  (cost=0.00..15722.20 rows=8873 width=16) (actual time=0.056..83.153 rows=8802 loops=1)
                     ->  Seq Scan on avg_test  (cost=0.00..7816.74 rows=99 width=12) (actual time=0.056..39.860 rows=101 loops=1)
                           Filter: (col1 = '2012-01-01 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on avg_test  (cost=0.00..7816.74 rows=8774 width=12) (actual time=0.046..42.363 rows=8701 loops=1)
                           Filter: (col2 > 98)
 Total runtime: 85.815 ms
(11 rows)

    （两者都运行了几次以排除缓存影响。）

    在此设置中UNION ALL，速度更快，因为它命中的行少得多——这反映在顺序扫描和排序中。

    现在我创建了两个索引：

    CREATE INDEX idx_avg_1 ON avg_test (col1, col2);

CREATE INDEX idx_avg_2 ON avg_test (col2);

ANALYZE avg_test;

    随后的运行给出了截然不同的结果：

    带索引的 CASE 版本

                                                                 QUERY PLAN                                                      
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.05 rows=1 width=12) (actual time=0.024..0.025 rows=1 loops=1)
   ->  Index Scan using idx_avg_1 on avg_test  (cost=0.00..21105.89 rows=436421 width=12) (actual time=0.023..0.023 rows=1 loops=1)
 Total runtime: 0.056 ms
(3 rows)

    带索引的 UNION ALL 版本

                                                                        QUERY PLAN                                               
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3095.29..3095.30 rows=1 width=16) (actual time=12.037..12.037 rows=1 loops=1)
   ->  Sort  (cost=3095.29..3124.87 rows=11832 width=16) (actual time=12.036..12.036 rows=1 loops=1)
         Sort Key: public.avg_test.col1, public.avg_test.col2
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Result  (cost=0.00..3036.13 rows=11832 width=16) (actual time=0.026..10.181 rows=8802 loops=1)
               ->  Append  (cost=0.00..3036.13 rows=11832 width=16) (actual time=0.025..8.374 rows=8802 loops=1)
                     ->  Index Scan using idx_avg_1 on avg_test  (cost=0.00..187.92 rows=99 width=12) (actual time=0.025..0.055 rows=101 loops=1)
                           Index Cond: (col1 = '2012-01-01 12:00:00'::timestamp without time zone)
                     ->  Bitmap Heap Scan on avg_test  (cost=223.23..2729.89 rows=11733 width=12) (actual time=1.997..7.093 rows=8701 loops=1)
                           Recheck Cond: (col2 > 98)
                           ->  Bitmap Index Scan on idx_avg_2  (cost=0.00..220.30 rows=11733 width=0) (actual time=1.433..1.433 rows=8701 loops=1)
                                 Index Cond: (col2 > 98)
 Total runtime: 12.105 ms
(13 rows)

    这是一个巨大的差异，这次CASE是赢家。执行计划再简单不过了……

    • 3

相关问题