test-normalized.sql

-- portnov@localhost:5432/portnov
-- 15 сент. 2023 г. 10:16:11


create table merchant (
	id int generated always as identity primary key,
	name text not null,
	mcc text not null,
	notes text
);

create index idx_merchant on merchant (name);

create table provider (
	id int generated always as identity primary key,
	name text not null,
	notes text
);

create index idx_provider on provider (name);

create table customer (
	id int generated always as identity primary key,
	first_name text,
	last_name text,
	notes text
);

create index idx_customer on customer (last_name, first_name);

create table tst_operation (
	id int8 generated always as identity primary key,
	date datetime not null,
	merchant_id int references merchant (id),
	provider_id int references provider (id),
	customer_id int references customer (id),
	amt numeric not null
);

create index idx_tst_operation_merchant on tst_operation (merchant_id);
create index idx_tst_operation_provider on tst_operation (provider_id);
create index idx_tst_operation_customer on tst_operation (customer_id);

with p (id) as (
	insert into provider (name)
	values ('TTK'), ('BEELINE'), ('MTS')
	returning id
),
pid as (
	select array_agg(id) as provider_ids
	from p
),
m (id) as (
	insert into merchant (name, mcc)
	values ('PYATEROCHKA', '1234'),
		   ('MAGNIT', '2345'),
		   ('LENTA', '3211'),
		   ('GLORIA', '3333'),
		   ('PEREKRESTOK', '3211'),
		   ('ASHAN', '3333'),
		   ('AMAZON', '1111')
	returning id
),
mid as (
	select array_agg(id) as merchant_ids
	from m
),
c (id) as (
	insert into customer (first_name, last_name)
	select 'Name_' || (i % 10), 'Surname_' || floor(i / 10)
	from generate_series(1, 1000) g (i)
	returning customer.id
),
cid as (
	select array_agg(id) as customer_ids
	from c
)
insert into tst_operation (date, merchant_id, provider_id, customer_id, amt)
select day,
	merchant_ids[floor(random()*array_length(merchant_ids,1))::int + 1],
	provider_ids[floor(random()*array_length(provider_ids,1))::int + 1],
	customer_ids[floor(random()*array_length(customer_ids,1))::int + 1],
	round(random()*1000, 2)
from generate_series(1, 10000) ops (seq) -- 10k operations per day
cross join generate_series('2023-01-01', '2023-12-31', interval '1 day') days (day)
cross join mid
cross join pid
cross join cid;

select count(1) from tst_operation;

analyze customer;
analyze merchant;
analyze provider;
analyze tst_operation;

explain analyze
select count(*), sum(amt)
from tst_operation o
inner join merchant m on o.merchant_id = m.id
inner join provider p on o.provider_id = p.id
inner join customer c on o.customer_id = c.id
where m.name = 'MAGNIT'
  and p.name = 'TTK'
  and c.first_name = 'Name_1'
  and c.last_name = 'Surname_2';
 
/*
count|sum     |
-----+--------+
  191|97315.49|
 */

/*
QUERY PLAN                                                                                                                                                                  |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Aggregate  (cost=7191.49..7191.52 rows=1 width=40) (actual time=4.553..4.555 rows=1 loops=1)                                                                                |
  ->  Hash Join  (cost=3.49..7190.62 rows=174 width=6) (actual time=0.049..4.509 rows=191 loops=1)                                                                          |
        Hash Cond: (o.merchant_id = m.id)                                                                                                                                   |
        ->  Hash Join  (cost=1.97..7183.20 rows=1217 width=10) (actual time=0.038..4.379 rows=1242 loops=1)                                                                 |
              Hash Cond: (o.provider_id = p.id)                                                                                                                             |
              ->  Nested Loop  (cost=0.71..7153.43 rows=3650 width=14) (actual time=0.023..3.833 rows=3604 loops=1)                                                         |
                    ->  Index Scan using idx_customer on customer c  (cost=0.28..3.31 rows=2 width=4) (actual time=0.010..0.012 rows=2 loops=1)                             |
                          Index Cond: ((last_name = 'Surname_2'::text) AND (first_name = 'Name_1'::text))                                                                   |
                    ->  Index Scan using idx_tst_operation_customer on tst_operation o  (cost=0.43..3465.56 rows=3650 width=18) (actual time=0.005..1.670 rows=1802 loops=2)|
                          Index Cond: (customer_id = c.id)                                                                                                                  |
              ->  Hash  (cost=1.20..1.20 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)                                                                          |
                    Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                            |
                    ->  Seq Scan on provider p  (cost=0.00..1.20 rows=2 width=4) (actual time=0.002..0.003 rows=2 loops=1)                                                  |
                          Filter: (name = 'TTK'::text)                                                                                                                      |
                          Rows Removed by Filter: 4                                                                                                                         |
        ->  Hash  (cost=1.46..1.46 rows=2 width=4) (actual time=0.008..0.008 rows=2 loops=1)                                                                                |
              Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                                  |
              ->  Seq Scan on merchant m  (cost=0.00..1.46 rows=2 width=4) (actual time=0.005..0.007 rows=2 loops=1)                                                        |
                    Filter: (name = 'MAGNIT'::text)                                                                                                                         |
                    Rows Removed by Filter: 12                                                                                                                              |
Planning Time: 0.359 ms                                                                                                                                                     |
Execution Time: 4.603 ms                                                                                                                                                    |
 */