select version();
-- PostgreSQL 13.6 (Ubuntu 13.6-1.pgdg18.04+1)

create table docs_initial (
    doc_id integer not null,
    doc_date date not null,
    doc_number varchar(15) not null,
    doc_text text not null,
    constraint docs_initial_pk primary key (doc_id)
);

create unique index idx_doc_number_initial on docs_initial using btree (doc_number);

create index idx_doc_date_initial on docs_initial using btree (doc_date);

-- таблица с уникальными и часто используемыми полями
create table docs_partition_unique (
    doc_id integer not null,
    doc_date date not null,
    doc_number varchar(15) not null,
    constraint docs_partition_unique_pk primary key (doc_id) -- для FK из других таблиц базы
);

-- глобальный уникальный индекс по номеру документа
create unique index idx_doc_number_partition_unique on docs_partition_unique using btree (doc_number);

-- индекс по дате документа
create index idx_doc_date_partition_unique on docs_partition_unique using btree (doc_date);

-- уникальный индекс по id и дате документа для FK из партиционированной таблицы
create unique index idx_doc_id_and_date_partition_unique on docs_partition_unique using btree (doc_id, doc_date);

-- партиционированная по дате документа таблица
create table docs_partition (
    doc_partition_id integer not null,
    doc_partition_date date not null,
    doc_partition_text text not null,
    constraint docs_partition_fk foreign key (doc_partition_id, doc_partition_date) references docs_partition_unique(doc_id, doc_date)
) partition by range (doc_partition_date);

create index idx_doc_date_partition on docs_partition using btree (doc_partition_date);

-- партиции/секции
create table docs_partition_2000_and_earlier partition of docs_partition for values from ('-infinity'::date) to ('2001-01-01'::date);
create table docs_partition_2001 partition of docs_partition for values from ('2001-01-01'::date) to ('2002-01-01'::date);
-- ...
create table docs_partition_2021 partition of docs_partition for values from ('2021-01-01'::date) to ('2022-01-01'::date);
create table docs_partition_2022_and_later partition of docs_partition for values from ('2022-01-01'::date) to ('infinity'::date);

-- уникальный индекс по id для каждой секции
create unique index idx_doc_partition_id_2000_and_earlier on docs_partition_2000_and_earlier using btree (doc_partition_id);
create unique index idx_doc_partition_id_2001 on docs_partition_2001 using btree (doc_partition_id);
-- ...
create unique index idx_doc_partition_id_2021 on docs_partition_2021 using btree (doc_partition_id);
create unique index idx_doc_partition_id_2022_and_later on docs_partition_2022_and_later using btree (doc_partition_id);

-- 50 млн записей в исходную талицу
insert into docs_initial(doc_id, doc_date, doc_number, doc_text)
select 
  	gs.id,
  	make_date(2000 + (random()*22)::integer, 1 + (random()*11)::integer, 1 + (random()*27)::integer),
  	random_string(2) || '-' || gs.id::text, 
   	random_string((1 + random()*300)::integer)	
from generate_series(1, 50000000) as gs(id);

-- эти же 50 млн записей распределяем по новым таблицам
insert into docs_partition_unique (doc_id, doc_date, doc_number)
select doc_id, doc_date, doc_number from docs_initial;

insert into docs_partition (doc_partition_id, doc_partition_date, doc_partition_text)
select doc_id, doc_date, doc_text from docs_initial;

select 
    relname  as object_name,
    pg_size_pretty(pg_total_relation_size(pg_class.oid)) as total_relation_size, 
    pg_size_pretty(pg_relation_size(pg_class.oid)) as relation_size 
from pg_class
    left join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where 
    nspname not in ('pg_catalog', 'information_schema')
    and pg_total_relation_size(pg_class.oid) >= 50*1024
order by 
    pg_total_relation_size(pg_class.oid) desc 

docs_initial	14 GB	9871 MB
docs_partition_unique	8929 MB	2488 MB
idx_doc_number_initial	2562 MB	2562 MB
idx_doc_number_partition_unique	2561 MB	2561 MB
idx_doc_id_and_date_partition_unique	1775 MB	1775 MB
docs_partition_unique_pk	1775 MB	1775 MB
docs_initial_pk	1071 MB	1071 MB
docs_partition_2007	487 MB	423 MB
docs_partition_2002	487 MB	423 MB
docs_partition_2005	486 MB	423 MB
...
docs_partition_2003	485 MB	422 MB
idx_doc_date_partition_unique	329 MB	329 MB
idx_doc_date_initial	329 MB	329 MB
docs_partition_2022_and_later	244 MB	211 MB
docs_partition_2000_and_earlier	243 MB	211 MB
idx_doc_partition_id_2007	49 MB	49 MB
idx_doc_partition_id_2002	49 MB	49 MB
...
idx_doc_partition_id_2003	49 MB	49 MB
idx_doc_partition_id_2022_and_later	24 MB	24 MB
idx_doc_partition_id_2000_and_earlier	24 MB	24 MB
docs_partition_2007_doc_partition_date_idx	15 MB	15 MB
docs_partition_2013_doc_partition_date_idx	15 MB	15 MB

explain select * from docs_initial 
where 
    doc_date between '2015-09-01'::date and '2016-08-31'::date
    and substring(doc_number, 1, 1) in ('W')
    and length(doc_text) = 123;

Gather  (cost=25454.45..1239844.63 rows=56 width=172)
  Workers Planned: 2
  ->  Parallel Bitmap Heap Scan on docs_initial  (cost=24454.45..1238839.03 rows=23 width=172)
        Recheck Cond: ((doc_date >= '2015-09-01'::date) AND (doc_date <= '2016-08-31'::date))
        Filter: (("substring"((doc_number)::text, 1, 1) = 'W'::text) AND (length(doc_text) = 123))
        ->  Bitmap Index Scan on idx_doc_date_initial  (cost=0.00..24454.44 rows=2246425 width=0)
              Index Cond: ((doc_date >= '2015-09-01'::date) AND (doc_date <= '2016-08-31'::date))

explain select docs_partition.*, docs_partition_unique.doc_number   
from docs_partition_unique inner join docs_partition on doc_partition_id = doc_id and doc_partition_date =  doc_date
where 
    doc_partition_date between '2015-09-01'::date and '2016-08-31'::date
    and substring(doc_number, 1, 1) in ('W')
    and length(doc_partition_text) = 123;

Gather  (cost=1000.57..154504.99 rows=1 width=172)
  Workers Planned: 2
  ->  Nested Loop  (cost=0.56..153504.89 rows=1 width=172)
        ->  Parallel Append  (cost=0.00..140993.93 rows=4728 width=161)
              ->  Parallel Bitmap Heap Scan on docs_partition_2015 docs_partition_1  (cost=7870.43..67982.27 rows=1506 width=160)
                    Recheck Cond: ((doc_partition_date >= '2015-09-01'::date) AND (doc_partition_date <= '2016-08-31'::date))
                    Filter: (length(doc_partition_text) = 123)
                    ->  Bitmap Index Scan on docs_partition_2015_doc_partition_date_idx  (cost=0.00..7869.53 rows=722860 width=0)
                          Index Cond: ((doc_partition_date >= '2015-09-01'::date) AND (doc_partition_date <= '2016-08-31'::date))
              ->  Parallel Seq Scan on docs_partition_2016 docs_partition_2  (cost=0.00..72988.02 rows=3222 width=161)
                    Filter: ((doc_partition_date >= '2015-09-01'::date) AND (doc_partition_date <= '2016-08-31'::date) AND (length(doc_partition_text) = 123))
        ->  Index Scan using idx_doc_id_and_date_partition_unique on docs_partition_unique  (cost=0.56..2.65 rows=1 width=19)
              Index Cond: ((doc_id = docs_partition.doc_partition_id) AND (doc_date = docs_partition.doc_partition_date))
              Filter: ("substring"((doc_number)::text, 1, 1) = 'W'::text)

explain select * from docs_initial 
where 
	doc_date between now() - '50 days'::interval and now();

Index Scan using idx_doc_date_initial on docs_initial  (cost=0.57..196810.83 rows=197739 width=172)
  Index Cond: ((doc_date >= (now() - '50 days'::interval)) AND (doc_date <= now()))

select docs_partition.*, docs_partition_unique.doc_number  
from docs_partition_unique inner join docs_partition on doc_partition_id = doc_id and doc_partition_date =  doc_date
where 
	doc_partition_date between now() - '50 days'::interval and now()

Gather  (cost=1001.00..199767.29 rows=24 width=173)
  Workers Planned: 2
  ->  Nested Loop  (cost=1.00..198764.89 rows=10 width=173)
        ->  Parallel Append  (cost=0.44..31425.76 rows=75342 width=162)
              Subplans Removed: 22
              ->  Parallel Bitmap Heap Scan on docs_partition_2022_and_later docs_partition_1  (cost=2018.17..30992.88 rows=75320 width=162)
                    Recheck Cond: ((doc_partition_date >= (now() - '50 days'::interval)) AND (doc_partition_date <= now()))
                    ->  Bitmap Index Scan on docs_partition_2022_and_later_doc_partition_date_idx  (cost=0.00..1972.98 rows=180769 width=0)
                          Index Cond: ((doc_partition_date >= (now() - '50 days'::interval)) AND (doc_partition_date <= now()))
        ->  Index Scan using idx_doc_id_and_date_partition_unique on docs_partition_unique  (cost=0.56..2.22 rows=1 width=19)
              Index Cond: ((doc_id = docs_partition.doc_partition_id) AND (doc_date = docs_partition.doc_partition_date))