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

--скорость вставки без индекса
SELECT SETSEED(0.20091126);
EXPLAIN ANALYZE
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);
ANALYZE data_log;

setseed

SELECT 1

QUERY PLAN

Insert on data_log  (cost=0.01..18750.21 rows=0 width=0) (actual time=1349.804..1349.805 rows=0 loops=1)

  ->  Nested Loop  (cost=0.01..13750.20 rows=500000 width=12) (actual time=2.682..274.338 rows=500000 loops=1)

        ->  Function Scan on generate_series  (cost=0.00..0.20 rows=20 width=0) (actual time=0.016..0.029 rows=20 loops=1)

        ->  Function Scan on generate_series id  (cost=0.00..250.00 rows=25000 width=4) (actual time=0.132..3.703 rows=25000 loops=20)

Planning Time: 0.122 ms

Execution Time: 1350.209 ms

EXPLAIN

ANALYZE

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

TRUNCATE TABLE

CREATE INDEX

--скорость вставки с битри
SELECT SETSEED(0.20091126);
EXPLAIN ANALYZE
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);
ANALYZE data_log;

setseed

SELECT 1

QUERY PLAN

Insert on data_log  (cost=0.01..18750.21 rows=0 width=0) (actual time=3723.294..3723.296 rows=0 loops=1)

  ->  Nested Loop  (cost=0.01..13750.20 rows=500000 width=12) (actual time=1.645..427.164 rows=500000 loops=1)

        ->  Function Scan on generate_series  (cost=0.00..0.20 rows=20 width=0) (actual time=0.008..0.019 rows=20 loops=1)

        ->  Function Scan on generate_series id  (cost=0.00..250.00 rows=25000 width=4) (actual time=0.082..5.714 rows=25000 loops=20)

Planning Time: 0.080 ms

Execution Time: 3723.516 ms

EXPLAIN

ANALYZE

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

--скорость вставки с брин
SELECT SETSEED(0.20091126);
EXPLAIN ANALYZE
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);
ANALYZE data_log;

setseed

SELECT 1

QUERY PLAN

Insert on data_log  (cost=0.01..18750.21 rows=0 width=0) (actual time=1384.337..1384.339 rows=0 loops=1)

  ->  Nested Loop  (cost=0.01..13750.20 rows=500000 width=12) (actual time=2.538..276.842 rows=500000 loops=1)

        ->  Function Scan on generate_series  (cost=0.00..0.20 rows=20 width=0) (actual time=0.007..0.018 rows=20 loops=1)

        ->  Function Scan on generate_series id  (cost=0.00..250.00 rows=25000 width=4) (actual time=0.127..3.914 rows=25000 loops=20)

Planning Time: 0.080 ms

Execution Time: 1384.567 ms

EXPLAIN

ANALYZE

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

QUERY PLAN

GroupAggregate  (cost=12.02..2840.43 rows=20 width=12) (actual time=110.167..110.169 rows=1 loops=1)

  Group Key: data_log.id

  ->  Bitmap Heap Scan on data_log  (cost=12.02..2840.13 rows=20 width=12) (actual time=0.253..110.119 rows=20 loops=1)

        Recheck Cond: (id = 1)

        Rows Removed by Index Recheck: 499980

        Heap Blocks: lossy=2703

        ->  Bitmap Index Scan on ix_data_log_id_creation_date  (cost=0.00..12.01 rows=10009 width=0) (actual time=0.215..0.216 rows=27030 loops=1)

              Index Cond: (id = 1)

Planning Time: 0.471 ms

Execution Time: 110.335 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=24.04..5684.41 rows=13 width=24) (actual time=146.609..152.228 rows=1 loops=1)

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

  Rows Removed by Join Filter: 67

  ->  Bitmap Heap Scan on data_log l  (cost=12.02..2840.13 rows=20 width=12) (actual time=0.140..66.861 rows=20 loops=1)

        Recheck Cond: (id = 1)

        Rows Removed by Index Recheck: 499980

        Heap Blocks: lossy=2703

        ->  Bitmap Index Scan on ix_data_log_id_creation_date  (cost=0.00..12.01 rows=10009 width=0) (actual time=0.116..0.117 rows=27030 loops=1)

              Index Cond: (id = 1)

  ->  Materialize  (cost=12.02..2840.23 rows=20 width=12) (actual time=0.006..4.264 rows=4 loops=20)

        ->  Bitmap Heap Scan on data_log ll  (cost=12.02..2840.13 rows=20 width=12) (actual time=0.106..85.205 rows=20 loops=1)

              Recheck Cond: (id = 1)

              Rows Removed by Index Recheck: 499980

              Heap Blocks: lossy=2703

              ->  Bitmap Index Scan on ix_data_log_id_creation_date  (cost=0.00..12.01 rows=10009 width=0) (actual time=0.098..0.099 rows=27030 loops=1)

                    Index Cond: (id = 1)

Planning Time: 0.433 ms

Execution Time: 152.284 ms

EXPLAIN