PostgreSQL忽略了时间戳列上的索引，即使使用索引查询速度更快。

Question

PostgreSQL忽略了时间戳列上的索引，即使使用索引查询速度更快。

sqlpostgresqlpostgresql-9.3postgresql-performancesql-execution-plan

4

在PostgreSQL 9.3上，我有一个拥有略多于一百万条记录的表格，该表格是这样创建的：

CREATE TABLE entradas
(
 id serial NOT NULL,
 uname text,
 contenido text,
 fecha date,
 hora time without time zone,
 fecha_hora timestamp with time zone,
 geom geometry(Point,4326),
 CONSTRAINT entradas_pkey PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE entradas
OWNER TO postgres;

CREATE INDEX entradas_date_idx
 ON entradas
 USING btree
 (fecha_hora);

CREATE INDEX entradas_gix
 ON entradas
 USING gist
 (geom);

我正在执行一个查询，按时间间隔聚合行，如下：

WITH x AS (
        SELECT t1, t1 + interval '15min' AS t2
        FROM   generate_series('2014-12-02 0:0' ::timestamp
                  ,'2014-12-02 23:45' ::timestamp, '15min') AS t1
        )

    select distinct
        x.t1,
        count(t.id) over w
    from x
    left join entradas  t  on t.fecha_hora >= x.t1
            AND t.fecha_hora < x.t2
    window w as (partition by x.t1)
    order by x.t1

这个查询大约需要50秒钟的时间。从explain的输出中，你可以看到没有使用timestamp索引：

Unique  (cost=86569161.81..87553155.15 rows=131199111 width=12)
 CTE x
   ->  Function Scan on generate_series t1  (cost=0.00..12.50 rows=1000 width=8)
   ->  Sort  (cost=86569149.31..86897147.09 rows=131199111 width=12)
     Sort Key: x.t1, (count(t.id) OVER (?))
     ->  WindowAgg  (cost=55371945.38..57667929.83 rows=131199111 width=12)
           ->  Sort  (cost=55371945.38..55699943.16 rows=131199111 width=12)
                 Sort Key: x.t1
                 ->  Nested Loop Left Join  (cost=0.00..26470725.90 rows=131199111 width=12)
                       Join Filter: ((t.fecha_hora >= x.t1) AND (t.fecha_hora < x.t2))
                       ->  CTE Scan on x  (cost=0.00..20.00 rows=1000 width=16)
                       ->  Materialize  (cost=0.00..49563.88 rows=1180792 width=12)
                             ->  Seq Scan on entradas t  (cost=0.00..37893.92 rows=1180792 width=12)

然而，如果我执行set enable_seqscan=false（我知道，这是不应该做的），那么查询将在不到一秒钟的时间内执行，并且explain输出显示它正在使用timestamp列上的索引。

Unique  (cost=91449584.16..92433577.50 rows=131199111 width=12)
CTE x
  ->  Function Scan on generate_series t1  (cost=0.00..12.50 rows=1000 width=8)
->  Sort  (cost=91449571.66..91777569.44 rows=131199111 width=12)
      Sort Key: x.t1, (count(t.id) OVER (?))
      ->  WindowAgg  (cost=60252367.73..62548352.18 rows=131199111 width=12)
            ->  Sort  (cost=60252367.73..60580365.51 rows=131199111 width=12)
                  Sort Key: x.t1
                  ->  Nested Loop Left Join  (cost=1985.15..31351148.25 rows=131199111 width=12)
                       ->  CTE Scan on x  (cost=0.00..20.00 rows=1000 width=16)
                        ->  Bitmap Heap Scan on entradas t  (cost=1985.15..30039.14 rows=131199 width=12)
                              Recheck Cond: ((fecha_hora >= x.t1) AND (fecha_hora < x.t2))
                              ->  Bitmap Index Scan on entradas_date_idx  (cost=0.00..1952.35 rows=131199 width=0)
                                   Index Cond: ((fecha_hora >= x.t1) AND (fecha_hora < x.t2))

即使使用查询更快，为什么postgresql不使用entradas_date_idx，除非我强制它使用？

如何让postgresql使用entradas_date_idx而不必使用set enable_seqscan=false？

- plablo09

1

计划者可能没有意识到CTE“x”仅涵盖了“entradas”的一小部分，您可以尝试将这些限制添加到where子句中。 - Jasen

尝试不使用CTE，因为它是查询规划器的已知障碍。 - Frank Heikens

4个回答

3

您可以简化查询：

SELECT x.t1, count(*) AS ct
FROM   generate_series('2014-12-02'::timestamp
                     , '2014-12-03'::timestamp
                     , '15 min'::interval) x(t1)
LEFT   JOIN entradas t ON t.fecha_hora >= x.t1
                      AND t.fecha_hora <  x.t1 + interval '15 min' 
GROUP  BY 1
ORDER  BY 1;

DISTINCT与窗口函数结合使用通常更昂贵（而且也更难估计）对于查询规划器来说。

CTE不是必需的，通常比子查询更昂贵。而且由于CTE是优化屏障，所以对于查询规划器来说更难估计。

看起来你想覆盖一整天，但你错过了最后15分钟。使用一个更简单的generate_series()表达式来覆盖整个一天（仍然不重叠相邻的日期）。

接下来，为什么你有fecha_hora timestamp带时区，而你还有fecha date和hora time [without time zone]？看起来应该是fecha_hora timestamp并删除冗余列？这也避免了与generate_series()表达式的数据类型的微妙差异 - 这通常不是问题，但timestamp取决于您会话的时区，而不是IMMUTABLE像timestamptz。

如果这还不够好，可以像@Daniel建议的那样添加一个冗余的WHERE条件来指导查询规划器。

基本的优化建议也同样适用：

防止PostgreSQL有时选择错误的查询计划

- Erwin Brandstetter

好的，fecha和hora列只是为了遗留问题而存在，现在并没有真正使用。是的，我错过了最后15分钟，谢谢你指出来！总的来说，这是一个优雅的解决方案，表明我必须从简单的角度去思考。 - plablo09

1

如果您的表格是新建立的，并且行数据最近被添加，Postgres 可能没有收集到足够的新数据统计信息。如果是这种情况，您可以尝试对该表进行 ANALYZE。

附注：请确保表格的统计目标不为零。

- AFE

我不确定这是否有影响，但我记得如果类型不同，则可能会避免使用索引。 - AFE

1

在索引使用方面，查询规划器尝试根据可用的索引、表统计数据和查询本身等因素做出明智的猜测，以找到执行查询的最佳方式。即使使用索引更快，有些情况下它仍会以顺序扫描的方式结束。这是因为在这些情况下（特别是当查询将返回大量行时），查询规划器并不知道使用索引比执行一堆索引扫描要快得多。

基本上，这是一个例子，你对于这种非常具体的情况比查询规划器更了解你的数据（它必须采取更通用、更广泛的视角，涵盖各种情况和可能的输入）。

对于像这样的情况，你知道通过enable_seqscan=false强制使用索引是没有问题的。对于某些特定情况，我也这样做，否则性能会大大降低，而我知道对于那些特定的查询，强制使用索引会使查询速度提高几个数量级。

但是需要记住两件事：

在查询之后，您应始终确保重新启用顺序扫描，否则它将保留到连接的其余部分中进行所有其他查询，这可能不是您想要的。如果您的查询发生变化，或者表中的数据显著增长，则可能不再更快地执行索引查询，尽管这肯定是可测试的。
使用CTE可能会对查询优化器有效地优化查询产生重大影响。我认为这不是这种情况的关键问题。

- khampson

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Vérité · Accepted Answer

错误估计的分析

问题的要点在于postgres计划程序不知道generate_series调用会产生多少个值和行数，然而必须估算有多少值和行数满足与大的entradas表的JOIN条件。在您的情况下，它失败了。

实际上，只有表的一小部分会被加入，但估计却偏向相反的方向，如EXPLAIN中所示:

->  Nested Loop Left Join  (cost=0.00..26470725.90 rows=131199111 width=12)
      Join Filter: ((t.fecha_hora >= x.t1) AND (t.fecha_hora < x.t2))
      ->  CTE Scan on x  (cost=0.00..20.00 rows=1000 width=16)
      ->  Materialize  (cost=0.00..49563.88 rows=1180792 width=12)
            ->  Seq Scan on entradas t  (cost=0.00..37893.92 rows=1180792 width=12)

entradas有估计的1180792行，x被估计为1000行，我相信这只是对于任何SRF调用的默认值。JOIN的结果被估计为131199111行，是大表格行数的100倍以上！

欺骗规划程序以获得更好的评估由于我们知道x中的时间戳属于一个狭窄的范围（一天），因此我们可以通过附加的JOIN条件向规划程序提供这些信息来帮助它：

 left join entradas  t 
         ON t.fecha_hora >= x.t1
        AND t.fecha_hora < x.t2
        AND (t.fecha_hora BETWEEN '2014-12-02'::timestamp
                             AND '2014-12-03'::timestamp)

(BETWEEN范围是否包括上限或稍微大一点并不重要，因为它会被其他条件严格过滤掉)。

规划者应该能够利用统计数据，认识到仅有很小一部分索引与这个值范围有关，并使用索引而不是连续扫描整个大表。