sql - 为什么在此查询中 LIMIT 2 比 LIMIT 1 长几个数量级？

Question

我正在使用 PostgreSQL 9.3。这应该在具有 100,000 多行的任何表上重现。 EXPLAIN ANALYZE 显示使用 LIMIT 2 扫描了更多行，但我不明白为什么。

限制 1:

EXPLAIN ANALYZE WITH base AS (
  SELECT *, ROW_NUMBER() OVER () AS rownum FROM a_big_table
), filter AS (
  SELECT rownum, true AS thing FROM base
) SELECT * FROM base LEFT JOIN filter USING (rownum) WHERE filter.thing LIMIT 1

结果:

Limit  (cost=283512.19..283517.66 rows=1 width=2114) (actual time=0.019..0.019 rows=1 loops=1)
  CTE base
    ->  WindowAgg  (cost=0.00..188702.69 rows=4740475 width=101) (actual time=0.008..0.008 rows=1 loops=1)
          ->  Seq Scan on a_big_table  (cost=0.00..129446.75 rows=4740475 width=101) (actual time=0.003..0.003 rows=1 loops=1)
  CTE filter
    ->  CTE Scan on base base_1  (cost=0.00..94809.50 rows=4740475 width=8) (actual time=0.000..0.000 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..307677626611.24 rows=56180269915 width=2114) (actual time=0.018..0.018 rows=1 loops=1)
        Join Filter: (base.rownum = filter.rownum)
        ->  CTE Scan on base  (cost=0.00..94809.50 rows=4740475 width=2113) (actual time=0.011..0.011 rows=1 loops=1)
        ->  CTE Scan on filter  (cost=0.00..94809.50 rows=2370238 width=9) (actual time=0.002..0.002 rows=1 loops=1)
              Filter: thing
Total runtime: 0.057 ms

限制 2:

EXPLAIN ANALYZE WITH base AS (
  SELECT *, ROW_NUMBER() OVER () AS rownum FROM a_big_table
), filter AS (
  SELECT rownum, true AS thing FROM base
) SELECT * FROM base LEFT JOIN filter USING (rownum) WHERE filter.thing LIMIT 2

结果:

Limit  (cost=283512.19..283523.14 rows=2 width=2114) (actual time=0.018..14162.283 rows=2 loops=1)
  CTE base
    ->  WindowAgg  (cost=0.00..188702.69 rows=4740475 width=101) (actual time=0.008..4443.359 rows=4714243 loops=1)
          ->  Seq Scan on a_big_table  (cost=0.00..129446.75 rows=4740475 width=101) (actual time=0.002..1421.622 rows=4714243 loops=1)
  CTE filter
    ->  CTE Scan on base base_1  (cost=0.00..94809.50 rows=4740475 width=8) (actual time=0.001..10214.684 rows=4714243 loops=1)
  ->  Nested Loop  (cost=0.00..307677626611.24 rows=56180269915 width=2114) (actual time=0.018..14162.280 rows=2 loops=1)
        Join Filter: (base.rownum = filter.rownum)
        Rows Removed by Join Filter: 4714243
        ->  CTE Scan on base  (cost=0.00..94809.50 rows=4740475 width=2113) (actual time=0.011..0.028 rows=2 loops=1)
        ->  CTE Scan on filter  (cost=0.00..94809.50 rows=2370238 width=9) (actual time=0.009..6595.770 rows=2357122 loops=2)
              Filter: thing
Total runtime: 14247.374 ms

Answer 1

引擎先运行，然后限制。因此，您可以看到更多行。