首页 文章

如何使用GROUP BY DATE优化MAX SQL查询

提问于
浏览 次
0

我正在尝试从具有3M行的表中优化查询 .

列是 value ， datetime 和 point_id .

SELECT DATE(datetime), MAX(value) FROM historical_points WHERE point_id=1 GROUP BY DATE(datetime);

此查询需要2秒 .

我尝试索引 point_id=1 ，但结果并没有好多少 .

是否可以索引 MAX 查询或有更好的方法吗？也许是INNER JOIN？

编辑：这是类似的解释分析，更好地处理案件 . 这也是ha性能问题 .

EXPLAIN ANALYZE SELECT DATE(datetime), MAX(value), MIN(value) FROM buildings_hispoint WHERE point_id=64 AND datetime BETWEEN '2017-09-01 00:00:00' AND '2017-10-01 00:00:00' GROUP BY DATE(datetime);

>GroupAggregate  (cost=84766.65..92710.99 rows=336803 width=68) (actual time=1461.060..2701.145 rows=21 loops=1)
>  Group Key: (date(datetime))
>  ->  Sort  (cost=84766.65..85700.23 rows=373430 width=14) (actual time=1408.445..1547.929 rows=523621 loops=1)
>        Sort Key: (date(datetime))
>        Sort Method: external sort  Disk: 11944kB
>        ->  Bitmap Heap Scan on buildings_hispoint  (cost=10476.02..43820.81 rows=373430 width=14) (actual time=148.970..731.154 rows=523621 loops=1)
>              Recheck Cond: (point_id = 64)
>              Filter: ((datetime >= '2017-09-01 00:00:00+02'::timestamp with time zone) AND (datetime               Rows Removed by Filter: 35712
>              Heap Blocks: exact=14422
>              ->  Bitmap Index Scan on buildings_measurementdatapoint_ffb10c68  (cost=0.00..10382.67 rows=561898 width=0) (actual time=125.150..125.150 rows=559333 loops=1)
>                    Index Cond: (point_id = 64)
>Planning time: 0.284 ms
>Execution time: 2704.566 ms
sql postgresql

2 回答

  • 0

    没有看到 EXPLAIN 输出很难说些什么 . 我的猜测是你必须在索引定义中包含 DATE() 调用：

    CREATE INDEX historical_points_idx ON historical_points (DATE(datetime), point_id);

    此外，如果 point_id 具有比 DATE(datetime) 更多的不同值，则必须反转列顺序：

    CREATE INDEX historical_points_idx ON historical_points (point_id, DATE(datetime));

    请记住，色谱柱的基数对计划者来说非常重要，优先选择具有高选择性的色谱柱 .

    回复于
  • 0
    SELECT DISTINCT ON (DATE(datetime)) DATE(datetime), value 
FROM historical_points WHERE point_id=1
ORDER BY DATE(datetime) DESC, value DESC;

    将计算索引放在 DATE(datetime), value 上 . [我希望那些不是你真正的专栏名称 . 使用像 VALUE 这样的保留字作为列名是一种混淆的方法 .

    SELECT DISTINCT 将像 GROUP ON 一样工作 . ORDER BY 替换了 MAX ，如果已编入索引，则会很快 .

    我欠这个技术给@ErwinBrandstetter .

    回复于

相关问题