sql - 索引扫描不能与 LIMIT 一起正常工作

Question

我在 PostgreSQL 9.1 中做了简单的实验。我创建了 test 表如下:

CREATE TABLE test
(
  id serial NOT NULL,
  CONSTRAINT id PRIMARY KEY (id )
)
CREATE INDEX id_idx
  ON test
  USING btree
  (id );

然后我添加一些数据:

insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
...many times :)

现在我有了包含 10'000 行的 test 表。我的第一个实验是通过 id 获取行:

explain select * from test where id = 50;

Index Scan using id_idx on test  (cost=0.00..8.27 rows=1 width=4)
  Index Cond: (id = 50)

好的，这里没有什么奇怪的。让我们按值范围进行查询:

explain select * from test where id >= 50;

Seq Scan on test  (cost=0.00..170.00 rows=9951 width=4)
  Filter: (id >= 50)

我们通过顺序扫描获得了 9951 行，但是如果我只想获得第一行怎么办:

explain select * from test where id >= 50 limit 1;

Limit  (cost=0.00..0.02 rows=1 width=4)
  ->  Seq Scan on test  (cost=0.00..170.00 rows=9951 width=4)
        Filter: (id >= 50)

我希望看到使用 rows=1 进行索引扫描，但我又进行了顺序扫描(扫描后有限制)。有什么方法可以通过 SQL 查询实现这种行为吗？

注意:我有类似的 MongoDB 查询:

> db.test.find({'dt':{$gte:ISODate("2013-07-20T00:00:00.00Z")}}).count()
10000
> db.test.find({'dt':{$gte:ISODate("2013-07-20T00:00:00.00Z")}}).limit(1).explain()
{
    "cursor" : "BtreeCursor dt_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 13,
    "indexBounds" : {
        "dt" : [
            [
                ISODate("2013-07-20T00:00:00Z"),
                ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
            ]
        ]
    },
    "server" : "******:27017"
}

在这种情况下，MongoDB 使用索引扫描仅扫描了 1 个文档，这非常好。

Answer 1

首先，额外的索引是不必要的

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "id" for table "test"

第二:总是运行 explain analyze 而不是 plain explain，你会得到真实的值。在这种情况下，表可能很热(已经在内存中)，所以 SEQ SCAN 真的非常非常快!

第三:您没有指定任何顺序，因此 Postgresql 可以自由地给您任何随机顺序，因此任何一个随机 ID >= 50。这很可能不是您想要的。 PostgreSQL 在单个页面上存储许多元组；并且知道这种情况下的数据分布，很可能在第一页上有一个 id >= 50 的元组，因此这种情况下的 SEQ SCAN 方法是正确且最快的。

第四步:在进行任何实际分析之前运行 VACUUM ANALYZE。

在使用 EXPLAIN ANALYZE 进行 VACUUM ANALYZE 之后，我得到:

# EXPLAIN ANALYZE SELECT * FROM test WHERE id >= 50 ORDER BY id LIMIT 1;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=0.028..0.029 rows=1 loops=1)
   ->  Index Scan using id on test  (cost=0.00..416.47 rows=12241 width=4) (actual time=0.027..0.027 rows=1 loops=1)
         Index Cond: (id >= 50)
 Total runtime: 0.059 ms
(4 rows)