sql - 如何防止更改某些值的执行计划

Question

我在 PosgreSQL 9.1.9 中有一张 table .有一个架构:

CREATE TABLE chpl_text
(
  id integer NOT NULL DEFAULT nextval('chpl_text_id_seq1'::regclass),
  page_id bigint NOT NULL,
  page_idx integer NOT NULL,
  ...
);

我在这个表中有大约 40000000 (40M) 行。现在，有一个查询:

SELECT
  ...
FROM chpl_text
ORDER BY id
LIMIT 100000
OFFSET N

N <= 5300000 一切顺利.执行计划看起来像这样

Limit  (cost=12743731.26..12984179.02 rows=100000 width=52)
  ->  Index Scan using chpl_text_pkey on chpl_text t  (cost=0.00..96857560.86 rows=40282164 width=52)

但对于 N >= 5400000它神奇地变成了

Limit  (cost=13042543.16..13042793.16 rows=100000 width=52)
  ->  Sort  (cost=13029043.16..13129748.57 rows=40282164 width=52)
        Sort Key: id
        ->  Seq Scan on chpl_text t  (cost=0.00..1056505.64 rows=40282164 width=52)

导致运行时间非常长。

如何防止 postresql 更改查询计划以获得更高的偏移量？

注意:我知道，大偏移量一点也不好，但我不得不在这里使用它们。

Answer 1

如果 Postgres 配置得当，您的统计数据是最新的(ANALYZE 或 autovacuum)并且成本设置合理，Postgres 通常更好知道何时执行索引扫描或顺序扫描。详情及链接:
Keep PostgreSQL from sometimes choosing a bad query plan

要在没有顺序扫描的情况下实际测试性能，“禁用”它(仅在调试 session 中!)

SET enable_seqscan=OFF;

More in the manual.

然后再次运行EXPLAIN ANALYZE ...

此外，the release of Postgres 9.2 had a focus on "big data" .对于给定的用例，您应该紧急考虑升级到 current release .

您还可以使用 CTE 和 row_number() 尝试这个替代查询，看看查询计划是否更有利:

WITH cte AS (
   SELECT ..., row_number() OVER (ORDER BY id) AS rn
   FROM   chpl_text
   )
SELECT ...
FROM   cte
WHERE  rn BETWEEN N+1 AND N+100000
ORDER  BY id;

情况并非总是如此，但可能在您的特殊情况下。