CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

create table test_btree
(
    key   varchar(32),
    value int
);
insert into test_btree
select md5(random()::text), (random() * 1000000)::int
from generate_series(1, 1000000);

create index test_btree_key on test_btree (key) include (value);

select sum(value)
from test_btree
where key in ('323816e2d4bb47d85d669b90089a2382', 'f3180734688c85d1cf5b173b261e9521');

create table test_hash
(
    key   text,
    value int
);
insert into test_hash
select md5(random()::text), (random() * 1000000)::int
from generate_series(1, 10000);

create index test_hash_key on test_hash using hash (key);

select key, value
from test_hash
where key = '70913971e3676ba202faba804a12f9bd';

select range, value
from test_gist
where range @> 400;

create table test_spgist
(
    key   point,
    value int
);
insert into test_spgist
select point(random() * 1000, random() * 1000), (random() * 10000)::int
from generate_series(1, 1000000);

create index test_spgist_key on test_spgist using spgist (key);

select key, value
from test_spgist
where key >> point(100, 100);

create table test_gin
(
    uuid uuid,
    tags integer[]
);
insert into test_gin
select gen_random_uuid(), array [(random() * 100)::int,(random() * 100)::int,(random() * 100)::int]
from generate_series(1, 1000);

create index test_gin_tags on test_gin using gin (tags);

select uuid, tags
from test_gin
where tags @> array [10, 2];

create table test_brin
(
    key   int,
    value int
);
insert into test_brin
select g, (random() * 1000000)::int
from generate_series(1, 1000000) g;

create index test_brin_key on test_brin (key) include (value);

select key, value
from test_brin
where key between 11100 and 11150;

Index Only Scan using test_brin_key on test_brin  (cost=0.42..5.42 rows=50 width=8) (actual time=0.014..0.018 rows=51 loops=1)
  Index Cond: ((key >= 11100) AND (key <= 11150))
  Heap Fetches: 0
Planning Time: 0.046 ms
Execution Time: 0.029 ms

create table test_bloom as
select md5(random()::text) as i1,
       md5(random()::text) as i2,
       md5(random()::text) as i3
from generate_series(1, 1000000);

create index test_bloom_all on test_bloom using bloom (i1, i2, i3);

select i1,i2,i3
from test_bloom
where i2 = '2efeb41703124b0c5a3ac7cfbc86029f';

Bitmap Heap Scan on test_bloom  (cost=15348.00..15352.01 rows=1 width=99) (actual time=5.933..8.660 rows=1 loops=1)
  Recheck Cond: (i2 = '2efeb41703124b0c5a3ac7cfbc86029f'::text)
  Rows Removed by Index Recheck: 4380
  Heap Blocks: exact=3890
  ->  Bitmap Index Scan on test_bloom_all  (cost=0.00..15348.00 rows=1 width=0) (actual time=5.615..5.616 rows=4381 loops=1)
        Index Cond: (i2 = '2efeb41703124b0c5a3ac7cfbc86029f'::text)
Planning Time: 0.039 ms
Execution Time: 8.678 ms

CREATE TABLE ip2location_db1_test1(
	ip_from bigint NOT NULL,
	ip_to bigint NOT NULL,
	country_code character(2) NOT NULL,
	country_name character varying(64) NOT NULL,
	CONSTRAINT ip2location_db1_pkey PRIMARY KEY (ip_from, ip_to)
);

\set ip random(0, 2147483647)
select *
from ip2location_db1_test1
where ip_from <= :ip
  and ip_to >= :ip;

create table ip2location_db1_test2
(
    ip_from      int         not null,
    ip_to        int         not null,
    country_code char(2)     not null,
    country_name varchar(64) not null
);
create index ip2location_db1_test2_pkey on ip2location_db1_test2 using btree (ip_from, ip_to);

\set ip random(0, 2147483647)
select *
from ip2location_db1_test2
where ip_from <= :ip - 2147483648
  and ip_to >= :ip - 2147483648;

create type iprange as range
(
    subtype=inet
);
create table ip2location_db1_test3
(
    ip_range     iprange     not null,
    country_code char(2)     not null,
    country_name varchar(64) not null
);
create index ip2location_db1_test3_key on ip2location_db1_test3 using gist (ip_range);

\set ip random(0, 2147483647)
select *
from ip2location_db1_test3
where ip_range @> '0.0.0.0'::inet + :ip;

create table ip2location_db1_test4
(
    ip_range     int8range             not null,
    country_code character(2)          not null,
    country_name character varying(64) not null
);
create index ip2location_db1_test4_range on ip2location_db1_test4 using gist (ip_range);

\set ip random(0, 2147483647)
select *
from ip2location_db1_test4
where ip_range @> :ip;

create table ip2location_db1_test5
(
    ip_range     int4range             not null,
    country_code character(2)          not null,
    country_name character varying(64) not null
);
create index ip2location_db1_test5_range on ip2location_db1_test5 using gist (ip_range);

\set ip random(0, 2147483647)
select *
from ip2location_db1_test5
where ip_range @> (:ip - 2147483648)::int4;

create table ip2location_db1_test6
(
    ip_range     box                   not null,
    country_code character(2)          not null,
    country_name character varying(64) not null
);
create index ip2location_db1_test6_range on ip2location_db1_test6 using gist (ip_range);

\set ip random(0, 2147483647)
select *
from ip2location_db1_test6
where ip_range @> box(point(:ip, :ip), point(:ip, :ip));

transaction type: ./test-sql6.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
duration: 30 s
number of transactions actually processed: 1218063
latency average = 0.099 ms
tps = 40600.765922 (including connections establishing)
tps = 40604.364144 (excluding connections establishing)

SELECT table_name                                               "表名",
       pg_size_pretty(pg_relation_size(table_name::text))       "数据占用空间",
       pg_size_pretty(pg_indexes_size(table_name::text))        "索引占用空间",
       pg_size_pretty(pg_total_relation_size(table_name::text)) "数据和索引占用空间"
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
  and table_name like 'ip2location_db1_%';

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

-- 员工薪水表
create table test_salary
(
    dep_name varchar(64), -- 部门名称
    emp_no   integer,     -- 职员编号
    salary   numeric      -- 薪水
);

-- 插入数据
insert into test_salary
values ('develop', 11, 5200),
       ('develop', 7, 4200),
       ('develop', 9, 4500),
       ('develop', 8, 6000),
       ('develop', 10, 5200),
       ('personnel', 5, 3500),
       ('personnel', 2, 3900),
       ('sales', 3, 4800),
       ('sales', 1, 5000),
       ('sales', 4, 4800);

select *, (avg(salary) over (partition by dep_name))::int dep_avg_salary
from test_salary;

select *, max(salary) over (partition by dep_name) - salary  diff_dep_max_salary
from test_salary;

select *, min(salary) over w - salary diff_higher_salary
from test_salary
    window w as (partition by dep_name order by salary desc groups unbounded preceding exclude group);

select rank() over (order by avg(salary) desc ), dep_name, avg(salary)::int dep_avg_salary
from test_salary
group by dep_name;

select *, avg(salary) over w adj_emp_avg_salary
from test_salary
    window w as (order by emp_no range between 1 preceding and 1 following exclude current row );

WITH RECURSIVE cte_name(
    CTE_query_definition -- 非递归项
    UNION [ALL]
    CTE_query definion   -- 递归项
) SELECT * FROM cte_name;

select sum(num)
from generate_series(1, 100) nums(num);

with recursive num_generate(num) as (
    values (1)
    union all
    select num + 1
    from num_generate
    where num < 100
)
select sum(num)
from num_generate;

-- 我们选择postgresql 13

-- 创建表
create table test_data
(
    category_id int, -- 类别ID
    log_id      uuid -- 日志ID
);

-- 为类别ID创建索引
create index idx_category on test_data using btree (category_id);

-- 插入模拟数据,
insert into test_data
select (random() * 100)::int, gen_random_uuid()
from generate_series(1, 1000000);

select distinct category_id
from test_data;

select category_id
from test_data
group by 1;

set max_parallel_workers_per_gather = 1

HashAggregate  (cost=18870.00..18871.00 rows=100 width=4) (actual time=158.472..158.480 rows=100 loops=1)
  Group Key: category_id
  Batches: 1  Memory Usage: 24kB
  ->  Seq Scan on test_data  (cost=0.00..16370.00 rows=1000000 width=4) (actual time=0.007..39.888 rows=1000000 loops=1)
Planning Time: 0.040 ms
Execution Time: 158.499 ms

with recursive rcs(category_id) as (
    select min(category_id)
    from test_data
    union all
    select (select min(test_data.category_id) from test_data where test_data.category_id > r1.category_id)
    from rcs r1
    where r1.category_id is not null
)
select count(*)
from rcs
where category_id is not null;

-- 创建表
create table test_data
(
    node_id       int, -- 节点ID
    child_node_id int  -- 该节点的子节点
);

-- 插入数据
insert into test_data
values (0, 1),
       (0, 2),

       (1, 10),
       (1, 11),
       (1, 12),
       (2, 20),
       (2, 21),

       (10, 101),
       (10, 102),
       (11, 110),
       (21, 210);

with recursive rsc(node_id, path) as (
    values (0, '0')
    union all
    select test_data.child_node_id, format('%s -> %s', r1.path, test_data.child_node_id)
    from rsc r1
             join test_data using (node_id)
)
select path
from rsc;