Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data_log (
    id INT NOT NULL,
    creation_date TIMESTAMP NOT NULL
);
CREATE INDEX ix_data_log_id_creation_date ON data_log(id, creation_date);

CREATE TABLE

CREATE INDEX

-- SETSEED чтобы рандом был одинаковый
-- Создаем 500к записей по 20 случайных дат на один id
-- У себя на компе я тестил на 50кк. Тут столько не вывозит.
SELECT SETSEED(0.20091126);

WITH
ids AS (
    SELECT  id
    FROM generate_series(1, 500000 / 20) id
)

INSERT INTO data_log (id, creation_date)
SELECT
    id,
    '2023-01-10 10:00:00'::TIMESTAMP + RANDOM() * INTERVAL '90 days' AS creation_date
FROM
    ids,
    LATERAL generate_series(1, 20);

setseed

SELECT 1

INSERT 0 500000

SELECT * FROM data_log LIMIT 10;

id
creation_date

1
2023-02-12 05:05:13.097214

2
2023-04-03 19:31:26.880056

3
2023-02-11 08:43:15.515983

4
2023-01-22 02:25:04.548825

5
2023-02-22 17:57:05.072775

6
2023-02-26 13:38:15.945155

7
2023-03-14 01:37:47.961941

8
2023-04-02 08:10:06.922561

9
2023-03-05 13:11:23.218137

10
2023-03-13 06:30:13.390186

SELECT 10

-- Обычная группировка. На удивление очень быстро.
EXPLAIN ANALYZE
SELECT *
FROM (
    SELECT id, max(creation_date)
    FROM data_log
    GROUP BY id
) t
LIMIT 10;

QUERY PLAN

Limit  (cost=0.42..1631.44 rows=10 width=12) (actual time=0.123..0.235 rows=10 loops=1)

  ->  GroupAggregate  (cost=0.42..32618.66 rows=200 width=12) (actual time=0.122..0.233 rows=10 loops=1)

        Group Key: data_log.id

        ->  Index Only Scan using ix_data_log_id_creation_date on data_log  (cost=0.42..29859.60 rows=551412 width=12) (actual time=0.079..0.200 rows=201 loops=1)

              Heap Fetches: 201

Planning Time: 0.224 ms

Execution Time: 0.352 ms

EXPLAIN

-- Оконная функция обыкновенная. 
-- Самая поздняя дата после сортировки будет иметь номер 1.
EXPLAIN ANALYSE
WITH
sorted AS (
    SELECT
        id,
        creation_date,
        row_number() over (partition by id ORDER BY creation_date DESC) AS row_number
    FROM data_log
)

SELECT
    id,
    creation_date
FROM sorted
WHERE row_number = 1
LIMIT 10;

QUERY PLAN

Limit  (cost=398.14..772.73 rows=10 width=12) (actual time=0.124..0.286 rows=10 loops=1)

  ->  Subquery Scan on sorted  (cost=398.14..103672.35 rows=2757 width=12) (actual time=0.123..0.283 rows=10 loops=1)

        Filter: (sorted.row_number = 1)

        ->  WindowAgg  (cost=398.14..96779.70 rows=551412 width=20) (actual time=0.122..0.281 rows=10 loops=1)

              Run Condition: (row_number() OVER (?) <= 1)

              ->  Incremental Sort  (cost=398.14..87129.99 rows=551412 width=12) (actual time=0.100..0.232 rows=182 loops=1)

                    Sort Key: data_log.id, data_log.creation_date DESC

                    Presorted Key: data_log.id

                    Full-sort Groups: 5  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB

                    ->  Index Only Scan using ix_data_log_id_creation_date on data_log  (cost=0.42..29859.60 rows=551412 width=12) (actual time=0.031..0.124 rows=201 loops=1)

                          Heap Fetches: 201

Planning Time: 0.140 ms

Execution Time: 0.372 ms

EXPLAIN

-- А это чорная магия. Эмуляция так называемого Loose Index Scan.
-- Это когда вместо проверки каждой строки с id=1 проверяется сразу строка где id=2.
-- Чем меньше будет разных id в таблице, тем быстрее оно будет работать.
EXPLAIN ANALYSE
WITH RECURSIVE cte AS (
    (
        SELECT dl
        FROM   data_log dl
        ORDER  BY id, creation_date DESC
        LIMIT  1
    )
    UNION ALL
    SELECT (
        SELECT dl2
        FROM data_log dl2
        WHERE dl2.id > (cte.dl).id
        ORDER BY dl2.id, dl2.creation_date DESC
        LIMIT 1
    )
    FROM cte
)

SELECT
    (dl).id,
    (dl).creation_date
FROM cte
LIMIT 10;

QUERY PLAN

Limit  (cost=9728.21..9728.41 rows=10 width=12) (actual time=0.096..0.629 rows=10 loops=1)

  CTE cte

    ->  Recursive Union  (cost=170.40..9728.21 rows=101 width=36) (actual time=0.088..0.613 rows=10 loops=1)

          ->  Subquery Scan on "*SELECT* 1"  (cost=170.40..170.48 rows=1 width=36) (actual time=0.087..0.089 rows=1 loops=1)

                ->  Limit  (cost=170.40..170.47 rows=1 width=48) (actual time=0.087..0.087 rows=1 loops=1)

                      ->  Incremental Sort  (cost=170.40..41581.11 rows=551412 width=48) (actual time=0.086..0.087 rows=1 loops=1)

                            Sort Key: dl.id, dl.creation_date DESC

                            Presorted Key: dl.id

                            Full-sort Groups: 1  Sort Method: top-N heapsort  Average Memory: 25kB  Peak Memory: 25kB

                            ->  Index Scan using ix_data_log_id_creation_date on data_log dl  (cost=0.42..29859.60 rows=551412 width=48) (actual time=0.044..0.062 rows=21 loops=1)

          ->  WorkTable Scan on cte cte_1  (cost=0.00..955.57 rows=10 width=32) (actual time=0.056..0.057 rows=1 loops=9)

                SubPlan 1

                  ->  Limit  (cost=95.42..95.54 rows=1 width=48) (actual time=0.054..0.055 rows=1 loops=9)

                        ->  Incremental Sort  (cost=95.42..21530.83 rows=183804 width=48) (actual time=0.054..0.054 rows=1 loops=9)

                              Sort Key: dl2.id, dl2.creation_date DESC

                              Presorted Key: dl2.id

                              Full-sort Groups: 9  Sort Method: top-N heapsort  Average Memory: 25kB  Peak Memory: 25kB

                              ->  Index Scan using ix_data_log_id_creation_date on data_log dl2  (cost=0.42..17620.99 rows=183804 width=48) (actual time=0.009..0.022 rows=21 loops=9)

                                    Index Cond: (id > (cte_1.dl).id)

  ->  CTE Scan on cte  (cost=0.00..2.02 rows=101 width=12) (actual time=0.095..0.626 rows=10 loops=1)

Planning Time: 0.211 ms

Execution Time: 0.706 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT 
  *
FROM
(
    SELECT 
     id
     ,max(creation_date) over(partition by id) as creation_date
    FROM data_log
) max
GROUP BY 1,2
LIMIT 10;

QUERY PLAN

Limit  (cost=467.07..492.87 rows=10 width=12) (actual time=0.123..0.341 rows=10 loops=1)

  ->  Group  (cost=467.07..103672.35 rows=40000 width=12) (actual time=0.122..0.339 rows=10 loops=1)

        Group Key: data_log.id, (max(data_log.creation_date) OVER (?))

        ->  Incremental Sort  (cost=467.07..100915.29 rows=551412 width=12) (actual time=0.121..0.321 rows=181 loops=1)

              Sort Key: data_log.id, (max(data_log.creation_date) OVER (?))

              Presorted Key: data_log.id

              Full-sort Groups: 5  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB

              ->  WindowAgg  (cost=0.42..38130.78 rows=551412 width=12) (actual time=0.048..0.257 rows=201 loops=1)

                    ->  Index Only Scan using ix_data_log_id_creation_date on data_log  (cost=0.42..29859.60 rows=551412 width=12) (actual time=0.019..0.138 rows=221 loops=1)

                          Heap Fetches: 221

Planning Time: 0.102 ms

Execution Time: 0.372 ms

EXPLAIN
id	creation_date
1	2023-02-12 05:05:13.097214
2	2023-04-03 19:31:26.880056
3	2023-02-11 08:43:15.515983
4	2023-01-22 02:25:04.548825
5	2023-02-22 17:57:05.072775
6	2023-02-26 13:38:15.945155
7	2023-03-14 01:37:47.961941
8	2023-04-02 08:10:06.922561
9	2023-03-05 13:11:23.218137
10	2023-03-13 06:30:13.390186