XAieQbgT

1DROP TABLE IF EXISTS b_orders CASCADE;
2CREATE TABLE b_orders (
3    id bigserial PRIMARY KEY,
4    number text NOT NULL UNIQUE,
5    payer_id bigint NULL
6);
7
8DROP TABLE IF EXISTS b_payers CASCADE;
9CREATE TABLE b_payers (
10    id bigserial,
11    name text NOT null
12) PARTITION BY RANGE (id);
13
14CREATE TABLE b_payers_1 PARTITION OF b_payers FOR VALUES FROM (1) TO (1000001);
15CREATE TABLE b_payers_2 PARTITION OF b_payers FOR VALUES FROM (1000001) TO (2000001);
16CREATE TABLE b_payers_3 PARTITION OF b_payers FOR VALUES FROM (2000001) TO (3000001);
17CREATE TABLE b_payers_4 PARTITION OF b_payers FOR VALUES FROM (3000001) TO (4000001);
18CREATE TABLE b_payers_5 PARTITION OF b_payers FOR VALUES FROM (4000001) TO (5000001);
19CREATE TABLE b_payers_6 PARTITION OF b_payers FOR VALUES FROM (5000001) TO (6000001);
20CREATE TABLE b_payers_7 PARTITION OF b_payers FOR VALUES FROM (6000001) TO (7000001);
21CREATE TABLE b_payers_8 PARTITION OF b_payers FOR VALUES FROM (7000001) TO (8000001);
22CREATE TABLE b_payers_9 PARTITION OF b_payers FOR VALUES FROM (8000001) TO (9000001);
23CREATE TABLE b_payers_10 PARTITION OF b_payers FOR VALUES FROM (9000001) TO (10000001);
24
25
26ALTER TABLE b_payers_1 ADD CONSTRAINT b_payers_1_pk PRIMARY KEY (id);
27ALTER TABLE b_payers_2 ADD CONSTRAINT b_payers_2_pk PRIMARY KEY (id);
28ALTER TABLE b_payers_3 ADD CONSTRAINT b_payers_3_pk PRIMARY KEY (id);
29ALTER TABLE b_payers_4 ADD CONSTRAINT b_payers_4_pk PRIMARY KEY (id);
30ALTER TABLE b_payers_5 ADD CONSTRAINT b_payers_5_pk PRIMARY KEY (id);
31ALTER TABLE b_payers_6 ADD CONSTRAINT b_payers_6_pk PRIMARY KEY (id);
32ALTER TABLE b_payers_7 ADD CONSTRAINT b_payers_7_pk PRIMARY KEY (id);
33ALTER TABLE b_payers_8 ADD CONSTRAINT b_payers_8_pk PRIMARY KEY (id);
34ALTER TABLE b_payers_9 ADD CONSTRAINT b_payers_9_pk PRIMARY KEY (id);
35ALTER TABLE b_payers_10 ADD CONSTRAINT b_payers_10_pk PRIMARY KEY (id);
36
37
38insert into b_payers(name) select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', trunc(random() * 62)::integer + 1, (random() * 50)::integer + 1) as name FROM generate_series(1, 10000000);
39insert into b_orders (number, payer_id) select generate_series(10000000, 19999999)::text as number, generate_series(10000000, 1, -1) as payer_id;
40
41
42EXPLAIN analyze
43SELECT
44  *
45FROM
46  b_orders o
47JOIN b_payers p ON p.id = o.payer_id
48WHERE
49  o."number" = '15465784';