sudo apt-get install postgresql

export PATH=/usr/local/pgql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

export PGDATA=/home/osdba/pgdata
initdb

cd postgres-9.2.3/contrib
make
sudo make install

pg_ctl start -D $PGDATA
pg_ctl stop -D $PGDATA [-m smart|fast|immediate]

select * from student from no = 1 union select * from student_bak where no = 1;
select * from student from no = 1 union all select * from student_bak where no = 1;

select E'\\134'::bytea;

create type week as enum('Sun','Mon','Tues');
create table duty( person text, weekday week);
insert into duty values('张三','Sun');

create type person as (
    name text,
    age integer,
    sex boolean
);
create table author(
    id int,
    person_info person,
    book text,
)

insert into author values(1, '("张三"，29，TRUE)','张三的自传');
insert into author values(2, ROW("张三"，29，TRUE),'张三的自传');
select (person_info).name from author;
update author set person_info = ROW("张三",29,TRUE) where id = 1;
update author set person_info.age = (person_info).age+1 where id = 2;

create index idx_name on table_name using gin(index_col [jsonb_ops]) 
create index idx_name on table_name using gin(index_col jsonb_path_ops) 

create index idx_name on table_name using btree(index_col->'name')

//原始的没有RANGE类型时需要建立的表
create table ipdb1(
    ip_begin inet,
    ip_end inet,
    area text,
    sp text
);
//有RANGE类型时能以以下的方式建表
create type inetrange as RANGE(subtype = inet)
create table ipdb2(
    ip_range inetrange,
    area text,
    sp text
);

//从ipdb1中倒入数据到ipdb2中
insert into ipdb2 select ('['|| ip_begin || ',' || ip_end || ']')::inetrange,area,sp from ipdb1;

create index idx1 on ipdb1(ip_begin);
create index idx2 on ipdb2(ip_end);
select * from ipdb1 where ip_begin <= '115.195.180.105'::inet and '115.195.180.105'::inet <= 'end';

create index idx3 on ipdb2 using gist(ip_range);
select * from ipdb2 where ip_range @> '115.195.180.105'::inet

select * from ipdb2 where ip_range && '[115.195.180.105,115.195.180.170]'::inetrange

create table ipdb2(
    ip_range inetrange,
    area text,
    sp text,
    exclude using gist(ip_range with &&)
);

create table testtab04(
    id int,
    col1 int[],
    col2 int[10],
    col3 text[][],
    col4 text[]
);

create table testtab05(
    id int,
    col1 int[],
);
insert into testtab05 values(1,'{1,2,3}');
insert into testtab05 values(1,ARRAY[1,2,3]);

//获取指定下标的元素
select id,col1[1] from testtab05;

//获取一个切片的元素
select id,col1[1:2] from testtab05;

//当col1是个二维数组时，1:1代表获取第一行的所有元素
select id,col1[1:1] from testtab05;

//以下两个sql等效
select id,col1[3][1:2] from testtab05;
select id,col1[1:3][1:2] from testtab05;

update testtab09 set col1[2][1] = 1000 where id = 1;

ARRAY[1,2,3] @> ARRAY[1,2]
ARRAY[[1,2,3]] @> ARRAY[1,2]

SHOW LC_CTYPE;
show LC_COLLATE;

CREATE TABLE test1 (
 name1 varchar(64) COLLATE "C",
 name2 varchar(64) COLLATE "zh_CN.utf8"
);

insert into test1(name1,name2)values
('黑熊','黑熊'),
('紫色','紫色');

select name1 from test1 order by name1 asc;
select name2 from test1 order by name2 asc;

alter table test1 alter name1 type varchar(64) COLLATE "zh_CN.utf8";

CREATE TABLE test1 (
 name varchar(64) COLLATE "C"
);

insert into test1(name)values
('黑熊'),
('紫色');

select name from test1 order by name asc;
select name from test1 order by name  collate "zh_CN.utf8" asc;

create index name_zh_cn on test1( name collate "zh_CN.utf8");

select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;

CREATE COLLATION name (
    [ LOCALE = locale, ]
    [ LC_COLLATE = lc_collate, ]
    [ LC_CTYPE = lc_ctype ]
)
DROP COLLATION [ IF EXISTS ] name [ CASCADE | RESTRICT ]

CREATE COLLATION "zh_CN.utf8" (
    LC_COLLATE = 'zh_CN.utf8',
    LC_CTYPE = 'zh_CN.utf8'
);

CREATE COLLATION pg_catalog."zh_CN.utf8" (
    LC_COLLATE = 'zh_CN.utf8',
    LC_CTYPE = 'zh_CN.utf8'
);

CREATE COLLATION pg_catalog."zh_CN.utf8" (
    LOCALE = 'zh_CN.utf8',
);

#!/bin/sh
INIT_WINDOWS_SQL="CREATE COLLATION pg_catalog.\"zh_CN.utf8\" (LC_COLLATE = 'zh_CN.utf8',LC_CTYPE = 'zh_CN.utf8');"
PGPASSWORD=123 psql -Upostgres -c "${INIT_SCHEMA_SQL}" database 

create database name [[with] option [...]]

alter database testdb01 connection limit 10;
alter database testdb02 rename to mydb1;
alter database testdb03 tablespace 'xxxx';
drop database testdb01;

create schema name [AUTHORIZATION username];
drop schema name;

set search_path to name 

ALTER SCHEMA name1 RENAME TO name2;  

select distinct(schemaname) from pg_tables;

create table table_name(
    col1 data_type1,
    col2 data_type2,
);

create table table_name2(like table_name);

alter table blog alter content set storage external

//会话级别的临时表
create temporary table tmp_t1(
    id int primary key,
    note text,
);

//事务级别的临时表
create temporary table tmp_t1(
    id int primary key,
    note text,
)on commit delete rows;

//会话级别的临时表
create temporary table tmp_t1(
    id int default 15,
    id2 int default nextval('id2_serial');
    note timestamp default now(),
);

create table books(
    book_no integer not null,
    price numeric check( price > 0 ),
    discounted_price numeric check( discounted_price > 0 ),
    check(price > discounted_priced)
);

create table persons(
    name text,
    age int,
    sex boolean
);
create table students(
    class_no int
)INHERITS(persons);

//查询所有的persons，包括派生表插入的数据
select * from persons;

//查询基表自身的persons
select * from only persons;

create table sales_detail(
    product_id int not null,
    price numeric(12,2),
    sale_date date not null,
    buyer varchar(40),
);
create table sales_detail_y2014m01(
    check(sale_date >= DATE '2014-01-01' and sale_date < DATE '2014-02-01')
)INHERITS(sales_detail);
create table sales_detail_y2014m02(
    check(sale_date >= DATE '2014-02-01' and sale_date < DATE '2014-03-01')
)INHERITS(sales_detail);

set constarint_exclusion = partition;
select count(*) from sales_detail where sale_date >= DATE '2014-01-01' and sale_date < DATE '2014-02-01';

create or replace function student_delete_trigger()
return trigger as $$
BEGIN
    delete from score where student_no = OLD.student_no;
    return OLD;
END;
$$
LANGUAGE plpgsql;

create trigger delete_student_trigger after delete on student for each row execute procedure student_delete_trigger(); 

create dataspace tbs_data location '/data/pgdata';
alter database db01 set tablespace tbs_data;
alter table test_01 set tablespace tbs_data;

create table users(
    id int,
    user_name varchar(40),
    password varchar(256),
    user_email text
);
create view vm_users as select id,user_name,user_email from users;

GRANT some_privileges on database_object_type object_name to role_name;
GRANT select on ALL TABLES in SCHEMA public TO user1;

begin;
insert into testtab01 values(1);
create table testtab02(
    id int,
    name text,
);
commit;

begin;

--位置A--
insert into t_user(id,name) values(1,'fish1');
insert into t_user(id,name) values(2,'fish2'); 
insert into t_user(id,name) values(3,'fish3'); 

--位置B--
游标 = select * from t_user;

--位置C--
insert into t_user(id,name) values(4,'fish4');
delete from t_user where id = 3; 

--位置D--
for 游标数据{
    显示出来 
}
end

lock table name in lockmode [NOWAIT]

select * from t_tenant where tenantId = 10001 for share;
insert into t_user  values(tenantId,userId) values (10001,10002);

select * from t_depeartment where departmentId = 10001 for key share;
insert into t_employee values(departmentId,employeeId) values (10001,10002);

local <dbname> <user> <auth-method> [auth-option]
host <dbname> <user> <ip/masklen> <auth-method> [auth-option]
hostssl <dbname> <user> <ip/masklen> <auth-method> [auth-option]
hostnossl <dbname> <user> <ip/masklen> <auth-method> [auth-option]

#使用特定的账号密码备份远程的数据库
pg_dump -h 192.168.122.1 -Uosba osba > osba.sql

#使用compress格式备份远程的数据库
pg_dump -Fc -h 192.168.122.1 -Uosba osba > osba.dump

#使用plain格式恢复数据库到postgres数据库
pg_restore -h 192.168.122.1 -Uosba -d postgres osba.sql

#使用compress格式恢复数据库到postgres数据库
pg_restore -h 192.168.122.1 -Uosba -C -d postgres osba.dump

env "PGPASSWORD=123" pg_dump -h localhost -d database -U user --schema=schema -f file.sql --encoding UTF-8 --no-owner

env "PGPASSWORD=123" psql -h localhost -p 5432 -U user -d database -f file.sql

INIT_SCHEMA_SQL="drop schema if exists ${SCHEMA} cascade;create schema ${SCHEMA};GRANT ALL ON SCHEMA ${SCHEMA} TO postgres;GRANT ALL ON SCHEMA ${SCHEMA} TO public;"
PGPASSWORD=123 PGCLIENTENCODING=UTF-8 psql --single-transaction --set ON_ERROR_STOP=on -U user -c "${INIT_SCHEMA_SQL}" -d database 

#!/bin/bash

root_directory="data"  # 替换为您的根目录路径
output_file="combined.sql"  # 替换为输出文件的路径

queue=("$root_directory")

echo "" > "$output_file"
while [ ${#queue[@]} -gt 0 ]; do
    current_dir="${queue[0]}"
    queue=("${queue[@]:1}")

    for file in "$current_dir"/*; do
        if [ -f "$file" ] && [[ "$file" == *.sql ]]; then
            cat "${file}" >>  "$output_file"
            echo "" >> "$output_file"
        elif [ -d "$file" ]; then
            queue+=("$file")
        fi
    done
done

echo "SQL files merged into $output_file"

CREATE SCHEMA  "t10001_0"

explain select * from testtab1;

explain (analyze true,costs true, buffers true) select * from testtab1;

#普通情况下，获取16628的pg_class的名字
select relname from pg_class where oid = 16628;

#可以快捷地直接转换
select 16628::regclass;

#普通情况下，获取名字为t的pg_class的主键oid
select oid from pg_class where relname = 't';

#反过来，也可以可以快捷地直接转换
select 't'::regclass;

(10,2)

create view myview as select * from mytab;

#相当于
create table myview (same column list as mytab );
create rule "_RETURN" as on select to myview do instead select * from mytab;

create rule name as event to table_name [where condition] do [instead | also ] {NOTHING | command | command}

create index mytest_lower_note_index on mytest (lower(note));

select * from mytest where lower(note) == 'abc';

create index order_unbilled_index on orders(order_nr) where billed is not true;

fish-# explain (analyze,verbose) select title from t_recipe where title like '%蛋糕%';
------------------------------------------------------
 Bitmap Heap Scan on public.t_recipe  (cost=783.15..2600.39 rows=84019 width=26) (actual time=14.032..30.502 rows=84000 loops=1)
   Output: title
   Recheck Cond: (t_recipe.title ~~ '%蛋糕%'::text)
   Heap Blocks: exact=767
   ->;  Bitmap Index Scan on t_recipe_title  (cost=0.00..762.15 rows=84019 width=0) (actual time=13.912..13.912 rows=84000 loops=1)
         Index Cond: (t_recipe.title ~~ '%蛋糕%'::text)
 Planning Time: 0.208 ms
 Execution Time: 35.155 ms
(8 rows)

fish-# explain (analyze,verbose) select * from t_recipe where title like '%蛋糕%' limit 1000;
------------------------------------------------------------
 Limit  (cost=783.15..804.78 rows=1000 width=30) (actual time=13.195..13.450 rows=1000 loops=1)
   Output: contentid, title
   ->;  Bitmap Heap Scan on public.t_recipe  (cost=783.15..2600.39 rows=84019 width=30) (actual time=13.194..13.342 rows=1000 loops=1)
         Output: contentid, title
         Recheck Cond: (t_recipe.title ~~ '%蛋糕%'::text)
         Heap Blocks: exact=10
         ->;  Bitmap Index Scan on t_recipe_title  (cost=0.00..762.15 rows=84019 width=0) (actual time=13.063..13.064 rows=84000 loops=1)
               Index Cond: (t_recipe.title ~~ '%蛋糕%'::text)
 Planning Time: 0.148 ms
 Execution Time: 13.531 ms

fish=# explain (analyze,verbose) select * from t_recipe where title like '%蛋糕%' order by contentid  asc limit 1000;------------------------------------------------------------------
 Limit  (cost=7207.06..7209.56 rows=1000 width=30) (actual time=37.443..37.630 rows=1000 loops=1)
   Output: contentid, title
   ->;  Sort  (cost=7207.06..7417.11 rows=84019 width=30) (actual time=37.442..37.523 rows=1000 loops=1)
         Output: contentid, title
         Sort Key: t_recipe.contentid
         Sort Method: top-N heapsort  Memory: 135kB
         ->;  Bitmap Heap Scan on public.t_recipe  (cost=783.15..2600.39 rows=84019 width=30) (actual time=13.139..25.023 rows=84000 loops=1)
               Output: contentid, title
               Recheck Cond: (t_recipe.title ~~ '%蛋糕%'::text)
               Heap Blocks: exact=767
               ->;  Bitmap Index Scan on t_recipe_title  (cost=0.00..762.15 rows=84019 width=0) (actual time=13.030..13.030 rows=84000 loops=1)
                     Index Cond: (t_recipe.title ~~ '%蛋糕%'::text)
 Planning Time: 0.123 ms
 Execution Time: 37.718 ms
(14 rows)

create index rum_idx on t_recipe using rum (title_tsvector rum_tsvector_ops);
drop index rum_idx;

create index rum_idx on t_recipe using rum (title_tsvector rum_tsvector_addon_ops,t)WITH (attach = 't', to = 'title_tsvector');
drop index rum_idx;

select contentid,title from t_recipe where title_tsvector @@ string_to_tsquery('蛋糕') order by t <;=>; '2020-01-01 00:00:00' LIMIT 5;

#bigm的操作符
create index idx_title on t_recipe using gin(title gin_bigm_ops);

#trgm的操作符
create index idx_title on t_recipe using gin(title gin_trgm_ops);

# 按照english的配置对句子分词和归一化
select to_tsvector('english','Mary fought against the dogs alone');

# 按照english的配置对query句子拆分，两个单词同时存在
select to_tsquery('english','fight & dog');

# 按照english的配置对query句子拆分，两个单词任意存在
select to_tsquery('english','fight | dog');

# 按照english的配置对query句子拆分，两个单词相邻存在
select to_tsquery('english','fight <;->; dog');

#默认使用&连接
select plainto_tsquery('english','fight dog');

#默认使用<;->;连接
select phraseto_tsquery('english','fight dog');

# 检查句子和查询条件的匹配性
select to_tsvector('english','Mary fought against the dogs alone') @@ to_tsquery('english','fight&dog');

select to_tsvector('zhparser','保障房资金压力')

#建立gin上面的函数索引
CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector('zhparser', body));

#建立物理列上的索引，并且加入触发器自动更新
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col = to_tsvector('zhparser', body);

CREATE INDEX textsearch_idx ON pgweb USING GIN(textsearchable_index_col);

#将body列（或者多列组合）进行zhparser分词后存入textsearchable_index_col列中
CREATE TRIGGER tsvectorupdate
BEFORE UPDATE OR INSERT ON pgweb
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('textsearchable_index_col', 'zhparser', 'body');

ln -s /usr/local/mysql/lib/libmysqlclient.dylib /usr/lib/libmysqlclient.dylib

git clone https://github.com/EnterpriseDB/mysql_fdw.git
make USE_PGXS=1
make USE_PGXS=1 install

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR fish
SERVER mysql_server
OPTIONS (username 'root', password '1');

CREATE FOREIGN TABLE t_user(
    userId int,
    name varchar(32),
    age int,
    money numeric(14,2),
    loginTime timestamp,
    createTime timestamp,
    modifyTime timestamp
)
SERVER mysql_server
OPTIONS (dbname 'Test', table_name 't_user');

insert into t_user(name,age,money,loginTime,createTime,modifyTime) values('fish',11,'12.0',now(),now(),now());

select * from t_user;

explain select * from t_user;

#全表映射
CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR postgres
SERVER mysql_server
OPTIONS (username 'root', password '1');

create schema mysql_yinghao;

import FOREIGN SCHEMA Yinghao from server mysql_server into mysql_yinghao;

#逐个复制远程表到本地表
create table t_user (like mysql_yinghao.t_user);
insert into t_user select * from   mysql_yinghao.t_user;

blockdev --getra /dev/sdf
blockdev --settra 4096 /dev/sdf

#查看虚拟内存的置换比例
cat /proc/sys/vm/swappiness

#打开文件，修改置换比例为0，即尽可能使用物理内存，不用swap空间
vim /etc/sysctl.conf
vm.swappiness = 0

#生效
sysctl -p

# 查看物理和Swap空间的大小
free -m

# malloc的分配大小策略
vm.overcommit_memory = 2
vm.overcommit_ratio = N

vm.dirty_background_ratio = 5
vm.dirty_ratio = 10

cat /proc/meminfo | grep PageTables

# 可以设置为on，但是大页启动失败时，pg启动也会失败
huge_pages = try

# 设置操作系统的大页内存
vim /etc/sysctl.conf
vm.nr_hugpages = 10240
sysctl -p

cat /proc/sys/vm/nr_hugpages

drop schema public cascade;
create schema public;
drop  user t1;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;


set search_path to public;

create table config(
    id bigint not null,
    name varchar(64) not null,
    value varchar(64) not null,
    primary key(id)
);

insert into config(id,name,value)values
(1,'maxOperator','13'),
(2,'maxDiskSpace','1GB');

create table operator(
    id bigint not null,
    tenant_id bigint not null,
    name varchar(64) not null,
    primary key(id)
);

alter table operator add constraint name_unique unique(name,tenant_id) deferrable initially deferred ;

insert into operator(id,tenant_id,name)values
(1,10001,'fish'),
(2,10001,'fish2'),
(3,10002,'fish'),
(4,10002,'fish2');

/*user表启用row level*/
alter table operator enable row level security;

/*DROP POLICY policy_name ON table_name;*/
CREATE POLICY tenant_isolation_policy ON operator
for all
USING (tenant_id = current_setting('app.tenant_id')::int);

ALTER TABLE operator ALTER COLUMN tenant_id SET DEFAULT current_setting('app.tenant_id')::int;

/*查看policy*/
SELECT * FROM pg_policies;

/* create user*/
create user t1 with password '123';
GRANT ALL ON SCHEMA public TO t1; 
GRANT ALL on ALL TABLES in SCHEMA public to t1;

/*测试*/
SET app.tenant_id = 10001;
show app.tenant_id;
select * from operator;

insert into operator(id,name)values
(6,'fish6');

/*dupliate name fish*/
insert into operator(id,name)values
(7,'fish');

update operator set name = 'fish22' where id = 2;

delete from operator where id = 1;

select * from operator;

select * from config;

update config set value = '10GB' where name = 'maxDiskSpace';

/*测试2*/
SET app.tenant_id = 10002;

/*can not delete data of other tenant*/
delete from operator where id = 2;

/*can not update data of other tenant*/
update operator set name = 'fish222' where id = 2;

/*can add date with the same name of other tenant*/
insert into operator(id,name)values
(8,'fish6');

select * from operator;

select * from config;

drop schema public cascade;
create schema public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
set search_path to public;

/*https://zhuanlan.zhihu.com/p/690917866*/

create table student(
    id bigint not null,
    tenant_id integer not null,
    name varchar(100) not null,
    /*must have id and partition id*/
    primary key(tenant_id,id)
) partition by range(tenant_id);

CREATE TABLE student_10 PARTITION OF student
    FOR VALUES FROM (0) TO (10);

CREATE TABLE student_20 PARTITION OF student
    FOR VALUES FROM (10) TO (20);

/*default partition*/
CREATE TABLE student_default PARTITION OF student default;

CREATE INDEX ON student (name);

insert into student(id,tenant_id,name)values
(1,1,'fish1'),
(2,1,'fish2'),
(3,2,'fish3'),
(4,2,'fish4'),
(5,11,'fish5'),
(6,11,'fish6'),
(7,12,'fish7'),
(8,12,'fish8'),
(9,103,'fish9'),
(10,103,'fish10');

select * from student;

select * from student_10;

select * from student_20;

select * from student_default;

/* add column*/
alter table student add column age integer not null default 0;
CREATE UNIQUE INDEX student_name ON student(tenant_id,name);

/* duplicate name*/
insert into student(id,tenant_id,name,age)values
(7,1,'fish1',10);

/* global index */
create index student_name2 on student(name);

/* change to only 2 partition*/
ALTER TABLE student DETACH PARTITION  student_10;
ALTER TABLE student DETACH PARTITION  student_20;

CREATE TABLE student_1 PARTITION OF student
    FOR VALUES FROM (0) TO (2);
CREATE TABLE student_2 PARTITION OF student
    FOR VALUES FROM (2) TO (20);

INSERT INTO student SELECT * FROM student_10;
INSERT INTO student SELECT * FROM student_20;
drop table student_10;
drop table student_20;

select * from student;

\d+ student;

/*https://zhuanlan.zhihu.com/p/117275290*/
/*https://developer.aliyun.com/article/590356*/
show enable_partition_pruning ;

drop schema public cascade;
create schema public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
set search_path to public;

CREATE TABLE student (
  id BIGINT NOT NULL,
  tenant_id INTEGER NOT NULL,
  age integer not null,
  name VARCHAR(100) NOT NULL,
  money integer not null,
  PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);

CREATE OR REPLACE FUNCTION create_tenant_partitions(p_table_name TEXT, hash_count INTEGER)
RETURNS VOID AS $$
DECLARE
    v_partition_name TEXT;
    v_sql TEXT;
BEGIN
    FOR i IN 0..(hash_count-1) LOOP
        v_partition_name := format('%s_%s', p_table_name, i);

        v_sql := format('CREATE TABLE %s PARTITION OF %s FOR VALUES WITH (MODULUS %s, REMAINDER %s);', 
            v_partition_name, 
            p_table_name, 
            hash_count, 
            i);

        EXECUTE v_sql;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

select create_tenant_partitions('student',10);

INSERT INTO student (id, tenant_id,age,money, name)
SELECT
  g.id,
  (g.id % 1001) AS tenant_id, -- 确保 tenant_id 在 0 到 1000 之间
  g.id % 120,
  g.id % 1000,
  'Student' || (g.id % 1000) AS name -- 随机生成名字
FROM generate_series(1, 100000) AS g(id);

-- 情况1：
-- 分区索引，有分区键。没有global关键字，每个分区上都有一个对应的索引匹配
create index student_age on student(tenant_id,age) ;

-- 无分区键的时候，会对每个子表进行seq筛选
explain select * from student where age = 10;

-- 有分区键的时候，直接走单个索引,student_7_tenant_id_age_idx
explain select * from student where age = 10  and tenant_id = 20;

-- 情况2：
-- 分区索引，没有分区键，没有global关键字，每个分区上都有一个对应的索引匹配
create index student_money on student(money) ;

-- 无分区键的时候，会对所有的索引进行Bitmap Index Scan
explain select * from student where money = 20;

-- 有分区键的时候，直接走单个索引,student_7_money_idx
explain select * from student where money = 30  and tenant_id = 20;

-- 情况3：
-- Postgresql上不支持使用global index，索引都是与每个表对应的。以下操作会报错
-- 错误:  unique constraint on partitioned table must include all partitioning columns
-- 描述:  表"student"上的约束UNIQUE缺少列"tenant_id"，该列是分区键的一部分.
create unique index student_name on student(name);

drop schema public cascade;
create schema public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
set search_path to public;

CREATE TABLE student (
  id BIGINT NOT NULL,
  tenant_id INTEGER NOT NULL,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);

CREATE OR REPLACE FUNCTION create_tenant_partitions(p_table_name TEXT, hash_count INTEGER)
RETURNS VOID AS $$
DECLARE
    v_partition_name TEXT;
    v_sql TEXT;
BEGIN
    FOR i IN 0..(hash_count-1) LOOP
        v_partition_name := format('%s_%s', p_table_name, i);

        v_sql := format('CREATE TABLE %s PARTITION OF %s FOR VALUES WITH (MODULUS %s, REMAINDER %s);', 
            v_partition_name, 
            p_table_name, 
            hash_count, 
            i);

        EXECUTE v_sql;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

select create_tenant_partitions('student',10);

-- 情况4：
-- 使用trigger来模拟实现global index
create table student_name(
  id BIGINT not null,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY(id)
);
ALTER TABLE student_name add constraint student_name_unique unique(name)  DEFERRABLE INITIALLY DEFERRED;

create or replace function student_unique_name()
returns trigger as $$
declare
 rows smallint;
begin
    -- del old data
    if tg_op in ('DELETE', 'UPDATE') then
        delete from student_name 
        where id = old.id and name=old.name ;

        get diagnostics rows = row_count;
        if rows != 1 then 
            raise '% affected % rows (expected: 1)',tg_op, rows;
        end if;
    end if;
    

    -- add new data
    if tg_op in ('INSERT', 'UPDATE') then
        insert into student_name (id,name) 
        values (new.id, new.name);

        get diagnostics rows = row_count;
        if rows != 1 then 
            raise '% affected % rows (expected: 1)',tg_op, rows;
        end if;
    end if;

    return new;
end;
$$ language plpgsql;

create trigger student_unique_name
after delete or insert or update of id, name
on student for each row
execute function student_unique_name();

-- 初始数据
begin transaction;
delete from student_name;
insert into student_name select id, name from student;
end;

-- 测试数据
begin transaction;
insert into student(id,tenant_id,name)values
(1,10001,'fish'),
(2,10002,'cat'),
(3,10003,'dog');

select * from student_name;

update student set name = 'cat2' where id = 2;

update student set name = 'fish1',id = 4 where id = 1;

delete from student where id = 3;

select * from student_name;
end;

-- 测试冲突
begin transaction;

delete from student;

insert into student(id,tenant_id,name)values
(1,10001,'fish'),
(2,10002,'cat'),
(3,10003,'dog');

update student set name = 'fish' where id = 2;

select 'try commit!';
end;

set show enable_partition_pruning = on;
show enable_partition_pruning ;

set show enable_partition_pruning = off;
show enable_partition_pruning ;

 explain analyse select   uorder_item.id as "id" , uorder.id as "orderId" , uorder.number as "orderNumber" , uorder.parent_source_order_info as "parentSourceOrderInfo" , uorder.undone_execute_amount as "orderUndoneExecuteAmount" , uorder.has_stock_execute as "orderHasStockExecute" , uorder.contact_id as "orderContactId" , uorder.in_store_id as "orderInStoreId" , uorder.out_store_id as "orderOutStoreId" , uorder.manage_employee_id as "orderManageEmployeeId" , uorder_item.order_date as "orderDate" , uorder.total_amount as "orderTotalAmount" , uorder.total as "orderTotal" , uorder.remark as "orderRemark" , uorder.summary_remark as "orderSummaryRemark" , uorder.draw_employee_id as "orderDrawEmployeeId" , uorder.draw_time as "orderDrawTime" , uorder.entry_account_employee_id as "orderEntryAccountEmployeeId" , uorder.entry_account_time as "orderEntryAccountTime" , uorder.attachment_count as "attachmentCount" , uorder.attachment_size as "attachmentSize" , uorder.type as "orderType" , uorder.custom_field1 as "orderCustomField1" , uorder.custom_field2 as "orderCustomField2" , uorder.custom_field3 as "orderCustomField3" , uorder.custom_field4 as "orderCustomField4" , uorder.custom_field5 as "orderCustomField5" , uorder.custom_field6 as "orderCustomField6" , uorder.custom_field7 as "orderCustomField7" , uorder.custom_field8 as "orderCustomField8" , uorder_item.order_state as "orderState" , uorder_item.order_red_state as "orderRedState" , uorder_item.item_id as "itemId" , item.number as "itemNumber" , item.name as "itemName" , item.image1 as "image1" , uorder_item.unit_id as "unitId" , uorder_item.store_id as "itemStoreId" , uorder_item.amount as "amount" , uorder_item.done_execute_amount as "doneExecuteAmount" , uorder_item.undone_execute_amount as "undoneExecuteAmount" , item.common_unit_name as "commonUnitName" , round(uorder_item.basic_amount*uorder_item.order_direction/item.common_unit_convert,2) as "commonAmount" , uorder_item.basic_amount as "basicAmount" , uorder_item.cost_price as "costPrice" , uorder_item.price as "price" , uorder_item.discount_price as "discountPrice" , uorder_item.pre_tax_price as "preTaxPrice" , uorder_item.tax as "tax" , uorder_item.total*uorder_item.order_direction as "total" , uorder_item.pre_tax_total*uorder_item.order_direction as "preTaxTotal" , uorder_item.discount_total*uorder_item.order_direction as "discountTotal" , uorder_item.cost_total*uorder_item.order_direction as "costTotal" , uorder_item.profit_total as "profitTotal" , uorder_item.profit_rate as "profitRate" , uorder_item.remark as "remark" , uorder_item.custom_field1 as "customField1" , uorder_item.custom_field2 as "customField2" , uorder_item.custom_field3 as "customField3" , uorder_item.custom_field4 as "customField4" , uorder_item.custom_field5 as "customField5" , uorder_item.custom_field6 as "customField6" , uorder_item.custom_field7 as "customField7" , uorder_item.custom_field8 as "customField8" , uorder_item.stock_location as "stockLocation" , uorder_item.items_order as "itemsOrder" from unified_order_item as uorder_item left join unified_order as uorder on uorder_item.order_id = uorder.id left join item on uorder_item.item_id = item.id where uorder_item.order_date >;= '2024-05-01' and uorder_item.order_date <;= '2024-05-31' and uorder_item.order_type in  ('SALES_OUT','SALES_RETURN') and uorder_item.order_state = 'ENTRY_ACCOUNT' and uorder_item.order_red_state = 'NORMAL' and uorder_item.order_stock_type = uorder.item_stock_type  and uorder_item.basic_amount != 0 order by uorder_item.order_date asc ,uorder.id asc ,uorder_item.items_order asc limit 50 offset 0;

explain analyse select   count(*) as "count" , count(distinct uorder.id) as "orderCount" , count(distinct uorder.id) filter (where (uorder.type = 'SALES_RETURN' or uorder.type = 'PURCHASE_RETURN') and uorder.state != 'DRAFT') as "returnOrderCount" , count(distinct uorder.id) filter (where (uorder.type != 'SALES_RETURN' and uorder.type != 'PURCHASE_RETURN') and uorder.state != 'DRAFT') as "normalOrderCount" , count(distinct uorder.id) filter (where uorder.state = 'DRAFT') as "draftOrderCount" , coalesce(sum(uorder_item.amount),0) as "amount" , coalesce(sum(uorder_item.done_execute_amount),0) as "doneExecuteAmount" , coalesce(sum(uorder_item.undone_execute_amount),0) as "undoneExecuteAmount" , coalesce(sum(uorder_item.basic_amount*uorder_item.order_direction/item.common_unit_convert),0) as "commonAmount" , coalesce(sum(uorder_item.total*uorder_item.order_direction),0) as "total" , coalesce(sum(uorder_item.pre_tax_total*uorder_item.order_direction),0) as "preTaxTotal" , coalesce(sum(uorder_item.discount_total*uorder_item.order_direction),0) as "discountTotal" , coalesce(sum(uorder_item.fee_total*uorder_item.order_direction),0) as "feeTotal" , coalesce(sum(uorder_item.cost_total*uorder_item.order_direction),0) as "costTotal" , coalesce(sum(uorder_item.profit_total),0) as "profitTotal" from unified_order_item as uorder_item left join unified_order as uorder on uorder_item.order_id = uorder.id left join item on uorder_item.item_id = item.id where uorder_item.order_date >;= '2024-05-01' and uorder_item.order_date <;= '2024-05-31' and uorder_item.order_type in  ('SALES_OUT','SALES_RETURN') and uorder_item.order_state = 'ENTRY_ACCOUNT' and uorder_item.order_red_state = 'NORMAL' and uorder_item.order_stock_type = uorder.item_stock_type  and uorder_item.basic_amount != 0;

 Limit  (cost=44.86..2231.09 rows=50 width=567) (actual time=0.813..0.817 rows=50 loops=1)
   ->;  Incremental Sort  (cost=44.86..12856.21 rows=293 width=567) (actual time=0.813..0.814 rows=50 loops=1)
         Sort Key: uorder_item.order_date, uorder.id, uorder_item.items_order
         Presorted Key: uorder_item.order_date
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 58kB  Peak Memory: 58kB
         Pre-sorted Groups: 1  Sort Method: quicksort  Average Memory: 70kB  Peak Memory: 70kB
         ->;  Nested Loop Left Join  (cost=1.02..12843.03 rows=293 width=567) (actual time=0.055..0.522 rows=90 loops=1)
               ->;  Nested Loop  (cost=0.73..12489.13 rows=293 width=396) (actual time=0.043..0.283 rows=90 loops=1)
                     ->;  Index Scan using unified_order_item_tenant_id_order_date_idx on unified_order_item uorder_item  (cost=0.30..6409.22 rows=1014 width=135) (actual time=0.033..0.105 rows=90 loops=1)
                           Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date))
                           Filter: (((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text) AND (basic_amount <;>; '0'::numeric))
                           Rows Removed by Filter: 49
                     ->;  Index Scan using unified_order_pkey1 on unified_order uorder  (cost=0.42..5.99 rows=1 width=269) (actual time=0.001..0.001 rows=1 loops=90)
                           Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.order_id))
                           Filter: (uorder_item.order_stock_type = item_stock_type)
               ->;  Index Scan using item_pkey1 on item  (cost=0.29..1.18 rows=1 width=45) (actual time=0.001..0.001 rows=1 loops=90)
                     Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.item_id))
 Planning Time: 0.454 ms
 Execution Time: 0.902 ms
(19 行记录)

 Aggregate  (cost=29.78..29.79 rows=1 width=360) (actual time=13.647..13.648 rows=1 loops=1)
   ->;  Nested Loop Left Join  (cost=1.14..29.67 rows=1 width=258) (actual time=0.045..11.714 rows=1710 loops=1)
         ->;  Nested Loop  (cost=0.85..21.35 rows=1 width=257) (actual time=0.039..9.677 rows=1710 loops=1)
               ->;  Index Scan using unified_order_item_tenant_id_order_type_order_state_order_r_idx on unified_order_item uorder_item  (cost=0.43..12.89 rows=1 width=232) (actual time=0.029..6.663 rows=1710 loops=1)
                     Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND ((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text))
                     Filter: ((order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date) AND (basic_amount <;>; '0'::numeric))
                     Rows Removed by Filter: 25109
               ->;  Index Scan using unified_order_pkey1 on unified_order uorder  (cost=0.42..8.45 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=1710)
                     Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.order_id))
                     Filter: (uorder_item.order_stock_type = item_stock_type)
         ->;  Index Scan using item_pkey1 on item  (cost=0.29..8.31 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=1710)
               Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.item_id))
 Planning Time: 0.335 ms
 Execution Time: 13.747 ms
(14 行记录)

 Limit  (cost=79.28..79.28 rows=1 width=11696) (actual time=15.279..15.283 rows=50 loops=1)
   ->;  Sort  (cost=79.28..79.28 rows=1 width=11696) (actual time=15.278..15.280 rows=50 loops=1)
         Sort Key: uorder_item.order_date, uorder.id, uorder_item.items_order
         Sort Method: top-N heapsort  Memory: 58kB
         ->;  Nested Loop Left Join  (cost=1.01..79.27 rows=1 width=11696) (actual time=0.039..14.139 rows=1710 loops=1)
               ->;  Nested Loop  (cost=0.72..62.59 rows=1 width=11042) (actual time=0.029..10.342 rows=1710 loops=1)
                     ->;  Append  (cost=0.42..21.23 rows=2 width=5444) (actual time=0.021..7.164 rows=1710 loops=1)
                           Subplans Removed: 1
                           ->;  Index Scan using unified_order_item_10001_tenant_id_order_type_order_state_o_idx on unified_order_item_10001 uorder_item_1  (cost=0.42..12.89 rows=1 width=5444) (actual time=0.021..7.097 rows=1710 loops=1)
                                 Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND ((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text))
                                 Filter: ((order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date) AND (basic_amount <;>; '0'::numeric))
                                 Rows Removed by Filter: 25109
                     ->;  Append  (cost=0.30..20.66 rows=2 width=5606) (actual time=0.001..0.002 rows=1 loops=1710)
                           Subplans Removed: 1
                           ->;  Index Scan using unified_order_10001_pkey on unified_order_10001 uorder_1  (cost=0.30..8.32 rows=1 width=5606) (actual time=0.001..0.001 rows=1 loops=1710)
                                 Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.order_id))                                 Filter: (uorder_item.order_stock_type = item_stock_type)
               ->;  Append  (cost=0.29..16.63 rows=2 width=610) (actual time=0.001..0.001 rows=1 loops=1710)
                     Subplans Removed: 1
                     ->;  Index Scan using item_10001_pkey on item_10001 item_1  (cost=0.29..8.31 rows=1 width=610) (actual time=0.001..0.001 rows=1 loops=1710)
                           Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.item_id))
 Planning Time: 0.775 ms
 Execution Time: 15.376 ms
(23 行记录)

 Aggregate  (cost=79.35..79.36 rows=1 width=360) (actual time=34.359..34.360 rows=1 loops=1)
   ->;  Nested Loop Left Join  (cost=1.01..79.24 rows=1 width=540) (actual time=0.137..32.492 rows=1710 loops=1)
         ->;  Nested Loop  (cost=0.72..62.59 rows=1 width=522) (actual time=0.103..29.439 rows=1710 loops=1)
               ->;  Append  (cost=0.42..21.23 rows=2 width=232) (actual time=0.078..25.695 rows=1710 loops=1)
                     Subplans Removed: 1
                     ->;  Index Scan using unified_order_item_10001_tenant_id_order_type_order_state_o_idx on unified_order_item_10001 uorder_item_1  (cost=0.42..12.89 rows=1 width=232) (actual time=0.078..25.609 rows=1710 loops=1)
                           Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND ((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text))
                           Filter: ((order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date) AND (basic_amount <;>; '0'::numeric))
                           Rows Removed by Filter: 25109
               ->;  Append  (cost=0.30..20.66 rows=2 width=298) (actual time=0.002..0.002 rows=1 loops=1710)
                     Subplans Removed: 1
                     ->;  Index Scan using unified_order_10001_pkey on unified_order_10001 uorder_1  (cost=0.30..8.32 rows=1 width=298) (actual time=0.002..0.002 rows=1 loops=1710)
                           Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.order_id))
                           Filter: (uorder_item.order_stock_type = item_stock_type)
         ->;  Append  (cost=0.29..16.63 rows=2 width=26) (actual time=0.002..0.002 rows=1 loops=1710)
               Subplans Removed: 1
               ->;  Index Scan using item_10001_pkey on item_10001 item_1  (cost=0.29..8.31 rows=1 width=26) (actual time=0.001..0.001 rows=1 loops=1710)
                     Index Cond: ((tenant_id = (current_setting('app.tenant_id'::text))::integer) AND (id = uorder_item.item_id))
 Planning Time: 1.489 ms
 Execution Time: 34.503 ms
(20 行记录)

 Limit  (cost=4370.31..4370.44 rows=50 width=572) (actual time=7.404..7.408 rows=50 loops=1)
   ->;  Sort  (cost=4370.31..4370.58 rows=105 width=572) (actual time=7.403..7.405 rows=50 loops=1)
         Sort Key: uorder_item.order_date, uorder.id, uorder_item.items_order
         Sort Method: top-N heapsort  Memory: 58kB
         ->;  Nested Loop Left Join  (cost=296.46..4366.83 rows=105 width=572) (actual time=0.934..6.342 rows=1710 loops=1)
               ->;  Nested Loop  (cost=296.18..4235.80 rows=105 width=396) (actual time=0.922..3.823 rows=1710 loops=1)
                     ->;  Bitmap Heap Scan on unified_order_item uorder_item  (cost=295.89..1743.16 rows=452 width=133) (actual time=0.912..1.344 rows=1710 loops=1)
                           Recheck Cond: ((order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date) AND ((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text))
                           Filter: (basic_amount <;>; '0'::numeric)
                           Heap Blocks: exact=378
                           ->;  BitmapAnd  (cost=295.89..295.89 rows=471 width=0) (actual time=0.888..0.889 rows=0 loops=1)
                                 ->;  Bitmap Index Scan on unified_order_item_order_date_idx  (cost=0.00..55.90 rows=3961 width=0) (actual time=0.100..0.100 rows=3900 loops=1)
                                       Index Cond: ((order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date))
                                 ->;  Bitmap Index Scan on unified_order_item_order_type_order_state_order_red_state_idx  (cost=0.00..239.51 rows=14614 width=0) (actual time=0.736..0.736 rows=26819 loops=1)
                                       Index Cond: (((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text))
                     ->;  Index Scan using unified_order_pkey on unified_order uorder  (cost=0.29..5.50 rows=1 width=271) (actual time=0.001..0.001 rows=1 loops=1710)
                           Index Cond: (id = uorder_item.order_id)
                           Filter: (uorder_item.order_stock_type = item_stock_type)
               ->;  Index Scan using item_pkey on item  (cost=0.28..1.22 rows=1 width=49) (actual time=0.001..0.001 rows=1 loops=1710)
                     Index Cond: (id = uorder_item.item_id)
 Planning Time: 0.425 ms
 Execution Time: 7.494 ms

 Aggregate  (cost=4346.71..4346.72 rows=1 width=360) (actual time=6.480..6.482 rows=1 loops=1)
   ->;  Nested Loop Left Join  (cost=296.47..4337.50 rows=105 width=87) (actual time=0.934..4.695 rows=1710 loops=1)
         ->;  Nested Loop  (cost=296.19..4209.62 rows=105 width=86) (actual time=0.928..3.289 rows=1710 loops=1)
               ->;  Bitmap Heap Scan on unified_order_item uorder_item  (cost=295.89..1743.16 rows=452 width=61) (actual time=0.916..1.381 rows=1710 loops=1)
                     Recheck Cond: ((order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date) AND ((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text))
                     Filter: (basic_amount <;>; '0'::numeric)
                     Heap Blocks: exact=378
                     ->;  BitmapAnd  (cost=295.89..295.89 rows=471 width=0) (actual time=0.892..0.893 rows=0 loops=1)
                           ->;  Bitmap Index Scan on unified_order_item_order_date_idx  (cost=0.00..55.90 rows=3961 width=0) (actual time=0.099..0.099 rows=3900 loops=1)
                                 Index Cond: ((order_date >;= '2024-05-01'::date) AND (order_date <;= '2024-05-31'::date))
                           ->;  Bitmap Index Scan on unified_order_item_order_type_order_state_order_red_state_idx  (cost=0.00..239.51 rows=14614 width=0) (actual time=0.747..0.747 rows=26819 loops=1)
                                 Index Cond: (((order_type)::text = ANY ('{SALES_OUT,SALES_RETURN}'::text[])) AND ((order_state)::text = 'ENTRY_ACCOUNT'::text) AND ((order_red_state)::text = 'NORMAL'::text))
               ->;  Memoize  (cost=0.30..5.51 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=1710)
                     Cache Key: uorder_item.order_id, uorder_item.order_stock_type
                     Cache Mode: logical
                     Hits: 1137  Misses: 573  Evictions: 0  Overflows: 0  Memory Usage: 74kB
                     ->;  Index Scan using unified_order_pkey on unified_order uorder  (cost=0.29..5.50 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=573)
                           Index Cond: (id = uorder_item.order_id)
                           Filter: (uorder_item.order_stock_type = item_stock_type)
         ->;  Index Scan using item_pkey on item  (cost=0.28..1.22 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=1710)
               Index Cond: (id = uorder_item.item_id)
 Planning Time: 0.411 ms
 Execution Time: 6.615 ms
(23 行记录)