时间戳

首页 > 解决方案 > 从大表中获取每个时间戳的最新记录 - 不使用索引

sql - 从大表中获取每个时间戳的最新记录 - 不使用索引

问题描述

我有几个临时表，其中记录被定期插入/更新（不删除）。

每个表都有一个“更新前”触发器，用当前时间戳更新时间戳列。

有一个进程定期运行，根据存储在控制表中的时间戳从每个临时表中获取最新记录（增量）。这是使用物化视图完成的。

每次上述过程运行时，控制表都会使用从物化视图中找到的 max(timestamp) 进行更新

对照表：

id | staging_table_name | input_last_update_timestamp |
---+--------------------+-----------------------------+
 1 | stg_table1         | 2018-06-29 12:57:19         |
 2 | stg_table2         | 2018-06-29 13:52:19         |

stg_table1

id      | internal_timestamp  
--------+--------------------
6875303 | 2018-06-29 14:18:17 
6874765 | 2018-06-29 14:18:17 
6875095 | 2018-06-29 14:18:17 
6867996 | 2018-06-29 14:18:17 
6873723 | 2018-06-29 14:18:17 
6874594 | 2018-06-29 14:18:17 
6868561 | 2018-06-29 14:18:17 
6875292 | 2018-06-29 14:18:00 
6874595 | 2018-06-29 14:18:00 
6875300 | 2018-06-29 14:18:00

我尝试了以下查询，但没有一个使用我在暂存表的“internal_timestamp”列上的索引

查询1：

SELECT 
    p.id,
    p.internal_timestamp
FROM 
    staging_scm.stg_table1 p,
    control_staging_scm.control_table o
WHERE 
    p.internal_timestamp > o.input_last_update_timestamp 
    AND o.id = 21

查询2

SELECT 
    p.id,
    p.internal_timestamp
FROM 
    staging_scm.stg_table1 p
JOIN 
    control_staging_scm.control_table o ON p.internal_timestamp > o.input_last_update_timestamp
WHERE 
    o.id = 21

查询3

SELECT 
    p.id,
    p.internal_timestamp
FROM 
    staging_scm.stg_table1 p
WHERE 
    p.internal_timestamp > (SELECT o.input_last_update_timestamp 
                            FROM control_staging_scm.control_table o 
                            WHERE o.id = 21)

解释计划：

Query 1 and 2
Nested Loop  (cost=0.03..203273.39 rows=1539352 width=12) (actual time=2013.969..2058.475 rows=520 loops=1)
  Join Filter: (p.internal_timestamp > o.input_last_update_timestamp)
  Rows Removed by Join Filter: 4615088
  Buffers: shared hit=173254
  ->  Index Scan using control_table_pkey on control_table o  (cost=0.03..4.03 rows=1 width=8) (actual time=0.011..0.014 rows=1 loops=1)
        Index Cond: (id = 21)
        Buffers: shared hit=2
  ->  Seq Scan on stg_table1 p  (cost=0.00..187106.17 rows=4618055 width=12) (actual time=0.003..419.628 rows=4615608 loops=1)
        Buffers: shared hit=173252
Planning time: 0.110 ms
Execution time: 2058.533 ms

Query 3

Seq Scan on stg_table1 p  (cost=4.03..189419.23 rows=1539352 width=12) (actual time=2020.801..2054.617 rows=675 loops=1)
  Filter: (internal_timestamp > $0)
  Rows Removed by Filter: 4614988
  Buffers: shared hit=173254
  InitPlan 1 (returns $0)
    ->  Index Scan using control_table_pkey on control_table o  (cost=0.03..4.03 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)
          Index Cond: (id = 21)
          Buffers: shared hit=2
Planning time: 0.155 ms
Execution time: 2054.694 ms

当我设置 enable_seqscan = OFF 时，使用索引并且性能好几个数量级

解释计划（Seqscan OFF）

    Nested Loop  (cost=41794.55..225088.07 rows=1539618 width=12) (actual time=0.100..0.557 rows=407 loops=1)
  Buffers: shared hit=97
  ->  Index Scan using control_table_pkey on control_table o  (cost=0.03..4.03 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)
        Index Cond: (id = 21)
        Buffers: shared hit=2
  ->  Bitmap Heap Scan on stg_table1 p  (cost=41794.52..220465.18 rows=1539618 width=12) (actual time=0.085..0.317 rows=407 loops=1)
        Recheck Cond: (internal_timestamp > o.input_last_update_timestamp)
        Heap Blocks: exact=90
        Buffers: shared hit=95
        ->  Bitmap Index Scan on stg_table1_internal_timestamp_idx  (cost=0.00..41717.54 rows=1539618 width=0) (actual time=0.070..0.070 rows=407 loops=1)
              Index Cond: (internal_timestamp > o.input_last_update_timestamp)
              Buffers: shared hit=5
Planning time: 0.131 ms
Execution time: 0.631 ms

不用说我在临时表上运行了分析，并且我已经相应地设置了 autovacuum/autoanalyze

那么计划者在临时表上的“internal_timestamp”上使用索引需要什么？

更新 1

在尝试下面的@Laurenz 建议之前，我很好奇 CTE 或标量函数在哪里可以解决问题。

但不幸的是，优化器在两种解决方案中都没有使用索引

CTE

WITH x AS (
    SELECT o.input_last_update_timestamp 
    FROM control_staging_scm.control_table o 
    WHERE o.id = 21
)
SELECT 
    p.id,
    p.internal_timestamp
FROM 
    staging_scm.stg_table1 p
WHERE 
    p.internal_timestamp > (SELECT x.input_last_update_timestamp FROM x)

标量函数

CREATE OR REPLACE FUNCTION control_staging_scm.last_update_timestamp(_table_id integer)
RETURNS timestamp without time zone
AS $function$

   SELECT o.input_last_update_timestamp FROM control_staging_scm.control_table o WHERE o.id = $1;

$function$ LANGUAGE 'sql';


SELECT 
    p.id,
    p.internal_timestamp
FROM 
    staging_scm.stg_table1 p
WHERE 
    p.internal_timestamp > (SELECT control_staging_scm.last_update_timestamp(21))

我期待/希望在执行主查询之前计算值（时间戳）并可供优化器使用。

如果有人指出优化器在上述情况下的内部行为，那就太好了！

标签： sqlpostgresqlperformanceindexingpostgresql-9.4

解决方案

优化器非常清楚 只会有一个匹配行control_table，但它无法预测该列将具有什么值（仅在查询执行时才知道），因此它input_last_update_timestamp无法知道其中有多少结果行stg_table1应该期待。

缺乏这方面的知识，它回退到估计将选择三分之一的行，这最好通过顺序扫描来完成。

您可以通过将查询分成两部分来改进它：

SELECT o.input_last_update_timestamp 
FROM control_staging_scm.control_table o 
WHERE o.id = 21;

SELECT p.id, p.internal_timestamp
FROM staging_scm.stg_table1 p
WHERE p.internal_timestamp > <result from first query>;

然后在计划第二次查询时会知道实际值，如果只有几行符合条件，PostgreSQL 将选择索引扫描。

推荐阅读