sql - Postgres: which index to add

Question

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.

A basic index on createdOnUtc helps already.

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?

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

question from:https://stackoverflow.com/questions/65943733/postgres-which-index-to-add

Answer 1

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.

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.