Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Секционирование по постфиксу
CREATE TABLE measurement ( 
city_id int not null, 
logdate date not null, 
PRIMARY KEY (city_id, logdate)
) PARTITION BY RANGE (logdate); 
CREATE TABLE measurement_y2006m02 PARTITION OF measurement 
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); 
CREATE TABLE measurement_y2006m03 PARTITION OF measurement 
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
INSERT INTO measurement 
SELECT RANDOM(1, 100), generate_series 
FROM generate_series ('2006-02-10'::TIMESTAMP, '2006-03-20', '1 day');
EXPLAIN ANALYZE 
SELECT city_id
FROM measurement
WHERE logdate BETWEEN '2006-02-15'::TIMESTAMP AND '2006-02-25'::TIMESTAMP;

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 39

QUERY PLAN

Bitmap Heap Scan on measurement_y2006m02 measurement  (cost=30.76..41.46 rows=11 width=4) (actual time=0.027..0.029 rows=11 loops=1)

  Recheck Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

  Heap Blocks: exact=1

  ->  Bitmap Index Scan on measurement_y2006m02_pkey  (cost=0.00..30.76 rows=11 width=0) (actual time=0.007..0.008 rows=11 loops=1)

        Index Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

Planning Time: 1.604 ms

Execution Time: 0.060 ms

EXPLAIN

-- Секционирование по префиксу
DROP TABLE measurement;
CREATE TABLE measurement ( 
city_id int not null, 
logdate date not null, 
PRIMARY KEY (logdate, city_id)
) PARTITION BY RANGE (logdate); 
CREATE TABLE measurement_y2006m02 PARTITION OF measurement 
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); 
CREATE TABLE measurement_y2006m03 PARTITION OF measurement 
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
INSERT INTO measurement 
SELECT RANDOM(1, 100), generate_series 
FROM generate_series ('2006-02-10'::TIMESTAMP, '2006-03-20', '1 day');
EXPLAIN ANALYZE 
SELECT city_id
FROM measurement
WHERE logdate BETWEEN '2006-02-15'::TIMESTAMP AND '2006-02-25'::TIMESTAMP;

DROP TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 39

QUERY PLAN

Bitmap Heap Scan on measurement_y2006m02 measurement  (cost=4.27..14.97 rows=11 width=4) (actual time=0.027..0.029 rows=11 loops=1)

  Recheck Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

  Heap Blocks: exact=1

  ->  Bitmap Index Scan on measurement_y2006m02_pkey  (cost=0.00..4.27 rows=11 width=0) (actual time=0.006..0.006 rows=11 loops=1)

        Index Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

Planning Time: 0.081 ms

Execution Time: 0.070 ms

EXPLAIN

-- Отбор по постфиксу
DROP TABLE measurement;
CREATE TABLE measurement ( 
city_id int not null, 
logdate date not null, 
PRIMARY KEY (city_id, logdate)
);
INSERT INTO measurement 
SELECT RANDOM(1, 100), generate_series 
FROM generate_series ('2006-02-10'::TIMESTAMP, '2006-03-20', '1 day');
EXPLAIN ANALYZE 
SELECT city_id
FROM measurement
WHERE logdate BETWEEN '2006-02-15'::TIMESTAMP AND '2006-02-25'::TIMESTAMP;

DROP TABLE

CREATE TABLE

INSERT 0 39

QUERY PLAN

Bitmap Heap Scan on measurement  (cost=30.76..41.46 rows=11 width=4) (actual time=0.011..0.012 rows=11 loops=1)

  Recheck Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

  Heap Blocks: exact=1

  ->  Bitmap Index Scan on measurement_pkey  (cost=0.00..30.76 rows=11 width=0) (actual time=0.005..0.005 rows=11 loops=1)

        Index Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

Planning Time: 0.038 ms

Execution Time: 0.022 ms

EXPLAIN

-- Отбор по префиксу
DROP TABLE measurement;
CREATE TABLE measurement ( 
city_id int not null, 
logdate date not null, 
PRIMARY KEY (logdate, city_id)
);
INSERT INTO measurement 
SELECT RANDOM(1, 100), generate_series 
FROM generate_series ('2006-02-10'::TIMESTAMP, '2006-03-20', '1 day');
EXPLAIN ANALYZE 
SELECT city_id
FROM measurement
WHERE logdate BETWEEN '2006-02-15'::TIMESTAMP AND '2006-02-25'::TIMESTAMP;

DROP TABLE

CREATE TABLE

INSERT 0 39

QUERY PLAN

Bitmap Heap Scan on measurement  (cost=4.27..14.97 rows=11 width=4) (actual time=0.010..0.011 rows=11 loops=1)

  Recheck Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

  Heap Blocks: exact=1

  ->  Bitmap Index Scan on measurement_pkey  (cost=0.00..4.27 rows=11 width=0) (actual time=0.005..0.005 rows=11 loops=1)

        Index Cond: ((logdate >= '2006-02-15 00:00:00'::timestamp without time zone) AND (logdate <= '2006-02-25 00:00:00'::timestamp without time zone))

Planning Time: 0.038 ms

Execution Time: 0.021 ms

EXPLAIN