Postgres:要添加的索引 [英] Postgres: which index to add

问题描述

我有一个主要用于此查询的表(这里仅使用3列，分别是 meter ， timeStampUtc 和 createdOnUtc ，但是是表格中的其他)，这会花费太长时间:

I have a table mainly used by this query (only 3 columns are in use here, meter, timeStampUtc and createdOnUtc, but there are other in the table), which starts to take too long:

select
    rank() over (order by mr.meter, mr."timeStampUtc") as row_name
  , max(mr."createdOnUtc") over (partition by mr.meter, mr."timeStampUtc") as "createdOnUtc"
from
    "MeterReading" mr
where
    "createdOnUtc" >= '2021-01-01'
order by row_name
;

(这是显示我的问题的最小查询.它本身可能没有太大意义，或者可能会被重写)

(this is the minimal query to show my issue. It might not make too much sense on its own, or could be rewritten)

我想知道使用哪个索引(或其他技术)来优化此特定查询.

I am wondering which index (or other technique) to use to optimise this particular query.

关于 createdOnUtc 的基本索引已经有所帮助.

A basic index on createdOnUtc helps already.

我主要想知道这两个Windows函数.它们非常相似，因此我将它们分解(以相同的 partition by 和 order by 命名的窗口)，它没有任何作用.在 meter上添加索引"timeStampUtc" 也不起作用(查询计划不变).

I am mostly wondering about those 2 windows functions. They are very similar, so I factorised them (named window with thus identical partition by and order by), it had no effect. Adding an index on meter, "timeStampUtc" had no effect either (query plan unchanged).

是否无法在窗口函数内的那两列上使用索引?

Is there no way to use an index on those 2 columns inside a window function?

编辑-解释分析输出:使用createdOnUtc索引

Edit - explain analyze output: using the createdOnUtc index

Sort  (cost=8.51..8.51 rows=1 width=40) (actual time=61.045..62.222 rows=26954 loops=1)
   Sort Key: (rank() OVER (?))
   Sort Method: quicksort  Memory: 2874kB
   ->  WindowAgg  (cost=8.46..8.50 rows=1 width=40) (actual time=18.373..57.892 rows=26954 loops=1)
         ->  WindowAgg  (cost=8.46..8.48 rows=1 width=40) (actual time=18.363..32.444 rows=26954 loops=1)
               ->  Sort  (cost=8.46..8.46 rows=1 width=32) (actual time=18.353..19.663 rows=26954 loops=1)
                     Sort Key: meter, "timeStampUtc"
                     Sort Method: quicksort  Memory: 2874kB
                     ->  Index Scan using "MeterReading_createdOnUtc_idx" on "MeterReading" mr  (cost=0.43..8.45 rows=1 width=32) (actual time=0.068..8.059 rows=26954 loops=1)
                           Index Cond: ("createdOnUtc" >= '2021-01-01 00:00:00'::timestamp without time zone)
 Planning Time: 0.082 ms
 Execution Time: 63.698 ms

推荐答案

是否无法在窗口函数内的那两列上使用索引?

Is there no way to use an index on those 2 columns inside a window function?

那是正确的；窗口函数不能使用索引，因为只能进行仅的工作，否则最终结果将是所有数据选择都已完成.从文档.

That is correct; a window function cannot use an index, as the work only on what otherwise would be the final result, all data selection has already finished. From the documentation.

窗口函数考虑的行是虚拟"行查询的FROM子句所产生的表格"，并由其WHERE过滤，GROUP BY和HAVING子句(如果有).例如，删除了一行因为它不满足WHERE条件，所以任何窗口都看不到功能.一个查询可以包含多个分割的窗口函数数据使用不同的OVER子句以不同的方式，但是它们都对该虚拟表定义的同一行进行操作.

The rows considered by a window function are those of the "virtual table" produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table.

索引的目的是加速该虚拟表"的创建.应用索引只会减慢速度:数据已经在内存中.扫描任何索引的速度都快几个数量级.

The purpose of an index is to speed up the creation of that "virtual table". Applying an index would just slow things down: the data is already in memory. Scanning it is orders of magnitude faster any any index.

这篇关于Postgres:要添加的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！