主页 / dba / 问题 / 101956
Accepted
Dean MacGregor
Asked: 2015-05-20 12:54:37 +0800 CST

优化视图（和基础表）以将时间戳平均为小时

  • 772

我有这张桌子：

CREATE TABLE spp.rtprices (
  "interval" timestamp without time zone NOT NULL,
  rtlmp numeric(12,6),
  rtmcc numeric(12,6),
  rtmcl numeric(12,6),
  node_id integer NOT NULL,
  CONSTRAINT rtprices_pkey PRIMARY KEY ("interval", node_id),
  CONSTRAINT rtprices_node_id_fkey FOREIGN KEY (node_id)
      REFERENCES spp.nodes (node_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)

还有一个相关的索引：

CREATE INDEX rtprices_node_id_interval_idx ON spp.rtprices (node_id, "interval");

反对它我提出了这样的观点：

  CREATE OR REPLACE VIEW spp.rtprices_hourly AS 
  SELECT (rtprices."interval" - '00:05:00'::interval)::date::timestamp without time zone AS pricedate,
  date_part('hour'::text, date_trunc('hour'::text, rtprices."interval" - '00:05:00'::interval))::integer + 1 AS hour,
  rtprices.node_id,
  round(avg(rtprices.rtlmp), 2) AS rtlmp,
  round(avg(rtprices.rtmcc), 2) AS rtmcc,
  round(avg(rtprices.rtmcl), 2) AS rtmcl
  FROM spp.rtprices
  GROUP BY date_part('hour'::text, date_trunc('hour'::text, rtprices."interval" - '00:05:00'::interval))::integer + 1,
           rtprices.node_id,
           (rtprices."interval" - '00:05:00'::interval)::date::timestamp without time zone;

其重点是给出每小时数字列的平均值（时间戳每 5 分钟有一次数据）。问题在于，对于 24 条记录，一天的查询node_id需要超过 30 秒的时间。

explain analyze select * from spp.rtprices_hourly
where node_id=20 and pricedate='2015-02-02'

返回这个：

   "HashAggregate  (cost=1128767.71..1128773.79 rows=135 width=28) (actual time=31155.023..31155.065 rows=24 loops=1)"
"  Group Key: ((date_part('hour'::text, date_trunc('hour'::text, (rtprices."interval" - '00:05:00'::interval))))::integer + 1), rtprices.node_id, (((rtprices."interval" - '00:05:00'::interval))::date)::timestamp without time zone"
"  ->  Bitmap Heap Scan on rtprices  (cost=10629.42..1128732.91 rows=2320 width=28) (actual time=25071.410..31153.715 rows=288 loops=1)"
"        Recheck Cond: (node_id = 20)"
"        Rows Removed by Index Recheck: 7142233"
"        Filter: (((("interval" - '00:05:00'::interval))::date)::timestamp without time zone = '2015-02-02 00:00:00'::timestamp without time zone)"
"        Rows Removed by Filter: 124909"
"        Heap Blocks: exact=43076 lossy=82085"
"        ->  Bitmap Index Scan on rtprices_node_id_interval_idx  (cost=0.00..10628.84 rows=464036 width=0) (actual time=68.999..68.999 rows=125197 loops=1)"
"              Index Cond: (node_id = 20)"
"Planning time: 5.243 ms"
"Execution time: 31155.392 ms"
postgresql index-tuning

1 个回答

  1. Best Answer

    更简单的视图

    为了这个目标：

    重点是给出每小时数字列的平均值

    .. 截断到完整的时间似乎同样好，这更简单也更便宜：

    CREATE OR REPLACE VIEW spp.rtprices_hourly AS 
SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
GROUP  BY 1, 2;

    更快的查询

    无论哪种方式，对具有可搜索谓词的视图的等效查询将是：

    SELECT *
FROM   spp.rtprices_hourly
WHERE  node_id = 20
AND    hour >= '2015-02-02 0:0'::timestamp
AND    hour <  '2015-02-03 0:0'::timestamp;

    这更快，但仍然没有达到预期的速度。主要的性能损失是因为索引只能与 上的索引条件一起使用node_id，它在视图中保留为原始状态。rtprices_node_id_interval_idx这就是为什么您的索引node_idfirst 很重要。为什么？

    在从堆中获取元组（已从表中读取行）之后hour，必须过滤第二个谓词。大部分行在流程后期被丢弃，很多工作都是徒劳的。

    直接查询更快

    在聚合之前运行原始查询并应用谓词会快得多：

    SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
WHERE  node_id = 20
AND    "interval" >= '2015-02-02 0:0'::timestamp
AND    "interval" <  '2015-02-03 0:0'::timestamp
GROUP  BY 1, 2;

    您现在将看到所有谓词的索引条件。更有效的索引仍然是node_id第一个。为什么？

    快速而简短：创建一个函数

    所以，这不会很好地处理视图。改用函数：

    CREATE OR REPLACE FUNCTION rtprices_hourly(_node_id int
                                         , _from timestamp
                                         , _to timestamp = NULL)
  RETURNS TABLE (
    hour    timestamp
  , node_id int
  , rtlmp   numeric
  , rtmcc   numeric
  , rtmcl   numeric) AS
$func$
SELECT date_trunc('hour', r."interval")  -- AS hour
     , r.node_id
     , round(avg(r.rtlmp), 2)  -- AS rtlmp
     , round(avg(r.rtmcc), 2)  -- AS rtmcc
     , round(avg(r.rtmcl), 2)  -- AS rtmcl
FROM   spp.rtprices r
WHERE  r.node_id     = _node_id
AND    r."interval" >= _from
AND    r."interval" <  COALESCE(_to, _from + interval '1 day')
GROUP  BY 1, 2
$func$  LANGUAGE sql STABLE;
    • 小心 OUT 参数和列名之间的命名冲突。这就是为什么我在这里对所有列进行表限定。

    现在您可以通过一个简单的查询获得最佳性能：

    SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp, '2015-2-3 0:0'::timestamp);

    我添加了一个方便的功能，如果你省略第二个参数，则默认为“一天后”：

    SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp);

    有关函数参数和默认值的更多信息：

    您可以查询任何范围：

    SELECT * FROM rtprices_hourly(1, '2015-2-2 10:0'::timestamp, '2015-2-2 20:0'::timestamp);
    • 2

相关问题