Postgres 14

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 TABLE

--скорость вставки без индекса
EXPLAIN ANALYZE
INSERT INTO data_log
SELECT 1,'2023-07-07 00:00';

QUERY PLAN

Insert on data_log  (cost=0.00..0.01 rows=0 width=0) (actual time=0.166..0.167 rows=0 loops=1)

  ->  Result  (cost=0.00..0.01 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=1)

Planning Time: 0.056 ms

Execution Time: 0.224 ms

EXPLAIN

TRUNCATE data_log RESTART IDENTITY;
--создаем индекс битри
CREATE INDEX ix_data_log_id_creation_date ON data_log(id, creation_date);

TRUNCATE TABLE

CREATE INDEX

--скорость вставки с битри
EXPLAIN ANALYZE
INSERT INTO data_log
SELECT 1,'2023-07-07 00:00';

QUERY PLAN

Insert on data_log  (cost=0.00..0.01 rows=0 width=0) (actual time=0.902..0.903 rows=0 loops=1)

  ->  Result  (cost=0.00..0.01 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=1)

Planning Time: 0.037 ms

Execution Time: 0.925 ms

EXPLAIN

TRUNCATE data_log RESTART IDENTITY;
DROP INDEX ix_data_log_id_creation_date;
--создаем индекс брин
CREATE INDEX ix_data_log_id_creation_date ON data_log USING BRIN(id, creation_date) with (pages_per_range=3);

TRUNCATE TABLE

DROP INDEX

CREATE INDEX

--скорость вставки с брин
EXPLAIN ANALYZE
INSERT INTO data_log
SELECT 1,'2023-07-07 00:00';

QUERY PLAN

Insert on data_log  (cost=0.00..0.01 rows=0 width=0) (actual time=0.224..0.224 rows=0 loops=1)

  ->  Result  (cost=0.00..0.01 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=1)

Planning Time: 0.036 ms

Execution Time: 0.270 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT *
FROM (
    SELECT id, max(creation_date)
    FROM data_log
    GROUP BY id
) t
WHERE id= 1

QUERY PLAN

GroupAggregate  (cost=0.00..35.65 rows=10 width=12) (actual time=0.040..0.041 rows=1 loops=1)

  Group Key: data_log.id

  ->  Seq Scan on data_log  (cost=0.00..35.50 rows=10 width=12) (actual time=0.012..0.012 rows=1 loops=1)

        Filter: (id = 1)

Planning Time: 0.497 ms

Execution Time: 0.142 ms

EXPLAIN

-- находим максимальную дату у ойди не столь очевидным способом =)
EXPLAIN ANALYZE
SELECT *
FROM  data_log  l
  left join data_log  ll on l.creation_date<ll.creation_date and ll.id = 1
WHERE ll.id is null
 and l.id = 1

QUERY PLAN

Nested Loop Anti Join  (cost=0.00..72.16 rows=7 width=24) (actual time=0.031..0.032 rows=1 loops=1)

  Join Filter: (l.creation_date < ll.creation_date)

  Rows Removed by Join Filter: 1

  ->  Seq Scan on data_log l  (cost=0.00..35.50 rows=10 width=12) (actual time=0.008..0.009 rows=1 loops=1)

        Filter: (id = 1)

  ->  Materialize  (cost=0.00..35.55 rows=10 width=12) (actual time=0.019..0.020 rows=1 loops=1)

        ->  Seq Scan on data_log ll  (cost=0.00..35.50 rows=10 width=12) (actual time=0.005..0.005 rows=1 loops=1)

              Filter: (id = 1)

Planning Time: 0.395 ms

Execution Time: 0.079 ms

EXPLAIN