PostgreSQL笔记

数据库的三种连接算法

1、Nested Loop Join
2层循环连接，外层循环逐行检索内层循环的每一行。最基础的连接方式，表比较小且被驱动表有索引的情况下，效率很高。无索引or表较大时性能急剧下降。
2、Hash Join
分2个阶段：

• 构建：选取表较小者基于连接字段在内存中构建一个哈希表
• 探测：遍历较大表(探测表)，对其每行的连接字段应用同样的哈希函数，快速定位到哈希表中对应位置，找到匹配的行。

特征：适合大表等值连接、无需索引，但需要额外内存来建构哈希表
3、Sort Megre Join
分2个阶段：

• 排序：将2个表各自按照连接字段进行排序
• 合并：像合并2个有序链表一样，同时遍历2个已排序的结果集，一次性找到所有匹配行

特点：当连接条件是非等值连接时，或已预先排序，可能比Hash Join更有效。排序本身可能需要消耗大量资源。

数据类型

• 布尔类型：支持标准boolean类型.

• * 值可以是true/false、true/false、yes/no、t/f、0/1。
  

  • 逻辑操作符：AND、OR、NOT
• 数值类型

• * smallint 2字节
  

  • int 4字节
  • bigint 8字节
  • real 4字节浮点，不精确、变精度
  • doulbe precision 8字节浮点数，不精确、变精度
  • money 8字节，货币类型，保证精度，不通国家精度不一致
• 字符类型

• * varchar （character varying）变长，最大1G，存储空间：4 + 实际字符串长度
  

  • char (character) 定长，不足则补空白，最大1G，存储空间也是4 + n
  • text 变长，无限制长度。
• 二进制类型：bytea类型，适合存储原始字节数据，例如图片
• 日期与时间类型

• * date：日期，4字节
  

  • time：时间，8字节，带时区12字节
  • timestamp：带不带时区都8字节。以2000年1月1日0点之前或之后的秒数存储。
  • interval ：12字节，时间间隔，用于日期时间加减计算等。
  • 时区设置：select timestamp with time zone ‘2025-11-20 06:30:45 PST’
• 其他类型：枚举、几何、网络地址、数组、符合类型、xml、range、伪类等

(补充)JSON类型

• json：数据原封不动存放至数据库、使用时再解析。保留json串中key的顺序，重复key仅保留最后一个。
• jsonb：存储时将json转换为二进制格式，使用时无需解析，且支持建索引，使用性能更高。不保留多余空格、不保留key顺序和重复key

json类型操作
select ‘[1,2,3]’::json->2 out:3
select ‘{“a”:1,“b”:2}’::json ->>‘a’ out：1
select ‘{“a”:{“b”:{“c”:1}}}’::json #>‘{a,b}’ out：{“c”:1}
->和->>的区别：->返回原类型，->>返回转换后的text类型
jsonb ‘[1,2]’ = jsonb ‘[1,2]’ 检测json对象内容一致性
@> 左包含
@< 右包含
? 检测key是否存在
?| 检测数组中是否有任意字符串存在于json的key中
?& 检测数组中是否所有字符串存在于json的key中
jsonb索引
jsonb支持BTree和GIN索引，默认是GIN索引，因为BTree效率相对较低，不关心jsonb内部数据类型，仅简单按整个jsonb大小方式进行排序。

数据类型转换

、CAST()函数
select
 cast(
'5'
as
int
)
、::双冒号转换
select
'10'
::
int

常用函数

current_date
、
current_timestamp
当前日期、时间戳
now()
返回当前时间戳（带时区）
extract()、date_part() //二者等效
从日期/时间中抽取子域，返回double precision类型数据。可返回年份/季度/月份/第几个星期/第几天/时/分/秒等。
select extract(dom 
from
 now()) //获取当前时间是星期几
复合类型添加、新增用法
create
type
 zyl 
as
 ( // 创建复合类型
z 
int
,
y 
double
precision
,
l 
timestamp
)
//新增复合类型新增数据，可单独为某个字段赋值，未标注的默认为
NULL
insert
into
 zhouyinglin 
values
(
row
(
,
.00
,now()),
'zyl'
,now())
insert
into
 zhouyinglin (zhou.z,ying,lin) 
values
(
,
'zyl3'
,now())
//查询复合类型，使用括号隔开列名
select
 * 
from
 zhouyinglin 
where
 (zhou).z=
select
 * 
from
 zhouyinglin 
where
 (zhouyinglin.zhou).z=
//修改复合类型
update
 zhouyinglin 
set
 zhou=
row
(
,
.00
,now()) 
where
 (zhou).z=
update
 zhouyinglin 
set
 zhou.z=
.00
where
 (zhou).z=

执行计划

查看执行计划：explain analyze verbose select …

• explain：显示查询计划

• * 执行计划节点：计划由多个操作节点组成，例如Seq Scan、Index Scan、Hash Scan、Sort等。
  

  • 树形结构：节点以树的形式组织，数据从叶子结点流向根节点。
• analyze：实际执行该查询，收集每个步骤详细的运行时间、返回的行数等

• * Actual Time：该节点实际执行时间（毫秒）
  

  • Rows：该节点实际返回的行数
  • Loops：该节点被执行的次数（嵌套循环中可能会执行多次）
• verbose：输出修饰符，展示比explain、analyze更详细的信息

• * 输出列表：显示每个节点输出的列名
  

  • 模式限定：表名以schema.table的形式显示
  • 表达式计算：如果查询中有表达式，会显示详细求值信息

举例：
cost 后面包含2个数字，用…分开，309.00表示启动成本，即返回第一行需要的cost值，第二个数字表示返回所有数据的成本
rows=9102表示一共返回9120行数据
width=36表示每行平均宽度约36字节。这个数字乘以行数可以估算出总查询量的大小，即36x9102=327672字节≈327KB。
Seq Scan：全表扫描，将表中所有数据块从头到尾读一遍，再从其中找到符合条件的数据块
NestLoop Join：嵌套循环连接，最普通的连接方式。执行过程：确定一个驱动表和一个Inner Table，驱动表每行与Inner Table记录Join一个嵌套循环。注意，join顺序很重要，因此驱动表最好要比较小。（INNER JOIN下，驱动表由优化器判断谁是小表，LEFT JOIN时强制join左边的表为驱动表）
Bitmap Index Scan：扫描索引，将满足条件的行或块在内存中建一个位图，扫描完后根据位图的数据文件把相应的数据读出来，若走多个索引，则将多个位图做 AND或OR计算合并成一个，再找出数据。当执行计划的结果行数很多时可能会走这种扫描，例如in 、非等值查询等。
Hash Join：散列连接，在2表中较小表上建立散列表（前提是小表够放在内存中），然后扫描较大表并探测散列表，找出散列匹配的行。通过pg_relation_size()函数查看表大小
Filter：条件过滤，即where子句上加过滤条件，如果条件列有索引，可能会走索引而不走条件过滤。
Merge Join：合并连接，两表均有索引时(已排序)，合并连接效果可能优于散列连接。

explain select a.id,b.note 
from
 testtab01 a,testtab02 b where a.
id
=b.id;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
  Hash Join  (
cost
=309.00..701.57 
rows
=9102 
width
=36)
    Hash Cond: (b.id = a.id)
    ->  Seq Scan on testtab02 b  (
cost
=0.00..165.02 
rows
=9102 
width
=36)
    ->  Hash  (
cost
=184.00..184.00 
rows
=10000 
width
=4)
      ->  Seq Scan on testtab01 a  (
cost
=0.00..184.00 
rows
=10000 
width
=4)
(5 rows)

表逻辑结构

数据库服务>数据库>表/索引>行
模式：数据库的一个命名空间或目录，不同模式下可以有相同的表、函数等对象，而不会冲突。模式提出主要用于方便管理。在PG库中，不能同时访问不同数据库对象，但可以访问不同模式间的对象。类似Mysql中的不同的Database。类似Linkdo开发环境中，将linkdo和linkdoapi放在同一个库的不同模式下。
pg在新建一个新数据库时，会默认创建一个public模式登录数据库后，若没有指定，则默认访问public模式内的对象。
新建模式：create schema xxx
模式授权给用户：GRANT USAGE ON SCHEMA {schema_name} TO {username};

PG表的TOAST技术

TOAST技术主要用于存储大字段的值，由于PG页面大小固定且不允许行跨越多个页面，所以不能直接存储较大的值，转而压缩或者拆分到系统表，即TOAST表。只有变长的数据类型才支持TOAST，变长数据类型中，前4字节（即32bit）成为长度字，长度字的高2bit是标志位，后30bit是长度值，对应1GB。2bit标志位分别对应【压缩标记位】和【是否行外存储】，不论是否压缩、是否行外存储，长度字内的30bit都表示数据的实际尺寸，而不是压缩后的数据长度。

• 压缩标记位：设置后，使用前需要先解压缩
• 行外存储：设置后，30bit长度位后面只是一个指针

修改字段设置
ALTER TABLE blog ALTER content SET STORAGE EXTERNAL;
如果表里有任意字段是支持TOAST的，那么就会自动为该表创建一个关联的TOAST表，行位内容就保存在这个表中。

TOAST策略

• PLAIN：避免压缩、行外存储
• EXTENDED：允许压缩和行外存储，优先压缩、后行外存储
• EXTERNAL：允许行外存储、不允许压缩
• MAIN：允许压缩、不允许行外存储

PostgreSQL11之后，可通过参数toast_tuple_target 来控制TOAST触发时机，单行数据在INSERT或UPDATE时，如果超过指定数值，就会将该行数据改为TOAST存储。

alter 
table
 test01 
set (toast_tuple_terget=128)
; //
单位为字节

toast_tuple_terget属于单行优化存储。另外，还可以通过fillfactor、toast.filltactor来控制数据更新时数据块扩展时机。
fillfactor：当前表的填充因子
toast.fillfactor：当前表对应的toast表的填充因子
填充因子取值：10~100，表示数据块在当前页(page)INSERT多少之后就不再继续填充了，仅保留做UPDATE使用。例如设置60，则剩下40只当做UPDATE使用。

补充：PostgreSQL的UPDATE流程

PG在操作UPDATE时，原数据行并不会被覆盖，而是会插入一条新数据行，由于新数据行仍在当前页，因此可以通过Heap-Only Tuple在原行和新行之间建立一个链表，因此UPDATE后不需要重新建索引，索引仍会指向原行，并通过链表找到最新行。
问题：如果UPDATE插入到其他页，则无法使用HOT，这时需要更新表上全部索引，会产生较大的开销。因此，对于更新频繁的表，需要设置一个较小的fillfactor值。
而Linkdo最常更新的linkdo_task_instance表，通过查询系统目录信息并没有查到相关fillfactor配置，而是更加激进的配置：

SELECT
 reloptions 
AS
 table_options 
FROM
 pg_class 
WHERE
 relname = 
'linkdo_task_instance'
;
["autovacuum_vacuum_scale_factor=0.02","autocvacuum_analyze_scale_factor=0.01"]

• autovacuum_vacuum_scale_factor=0.02：当表中有 2% 的行被更新或删除时触发 vacuum（默认是 20%，即0.2）
• autovacuum_analyze_scale_factor=0.01：当表中有 1% 的行被更改时触发 analyze（默认是 10%。即0.1）

补充：vacuum和analyze

vacuum是PG库中的空间清理工，用于：清理死元祖、更新可见性地图等，analyze负责查询优化，主要用于收集表的行数、页数，每个列的数值分布、最常见值、更新系统目录。如果没有这些信息，优化器可能会选择很差的查询计划。
[数据变更] → [产生死元组] → [VACUUM清理] → [空间重用]
↓
[统计信息过时] → [ANALYZE更新] → [优化器获得新信息]
临时表
PG库中临时表有2种，一种是单纯的临时表，另一种是半持久化表。

• 临时表：

• * 会话级临时表：数据保存在整个会话中
  

  • 事务级临时表：数据保存在整个事务中
• UNLOGGED表：不产生WAL日志、数据库异常则数据丢失，正常重启不会丢失数据。在使用上与其他表没有差异

创建临时表如下4种写法效果一致

create TEMPORARY table tmp
_t1(
id
int
primary
key
, 
note
text
)
;
create TEMP table tmp
_t1(
id
int
primary
key
, 
notes
text
)
create GLOBAL TEMPORARY table tmp
_t1(
id
int
primary
key
, 
note
text
)
;
create LOCAL TEMPORARY table tmp
_t1(
id
int
primary
key
, 
note
text
)
;

创建unlogged表

CREATE
UNLOGGED
TABLE
 unlogged01(id 
int
primary key
, t 
text
);

约束

检查约束/非空约束/唯一约束/主键约束/外键约束

修改表

增删字段

alter
table
table_name
add
column
column_name
type
;
alter
table
table_name
drop
column
 column_nmae;

增删约束

alter
table
table_name
add
check
(age > 
)  //设置检查约束
alter
table
table_name
alter
column
column_name
set
not
null
 //设置非空约束
//删除约束前需要知道约束名称是什么
alter
table
table_name
drop
constraint
constraint_name
//非空约束没有名字，通过如下语法删除
ALTER
TABLE
 student 
ALTER
COLUMN
 student_name 
DROP
NOT
NULL
;

修改/删除默认值

// 修改只影响后续
INSERT
的默认值
alter
table
table_name
alter
column
column_name
set
default
;
// 删除默认值，即将默认值修改为
NULL
alter
table
table_name
alter
column
column_name
drop
default
;

修改字段数据类型

// 只有字段内所有项都可以隐式转换为新类型时，才能修改字段类型，无法隐式转换则会修改失败
alter
table
table_name
alter
column
column_name
type
text
;

字段/表重命名

alter
table
table_name
rename
column
 age 
to
 age_num; //重命名字段
alter
table
table_name
rename
to
 table_name2; //改表明

继承表

pg库的表是允许继承的，且允许多继承，继承表的特性如下：

• 子表插入/更新数据后对父表可见，父表插入/更新数据后对子表不可见
• 如果只想查询父表可使用关键字【only】
• 父表所有检查约束、非空约束都会自动继承给子表，其他约束不会继承
• 子表继承父表后，子表拥有所有父表字段总和，类型相同的字段会被融合，融合字段同样继承约束。

基于继承表的分区表

PG库在版本10之前均通过表继承实现分区表，而10之后提供了DDL语句创建声明式分区表，但原理仍然是表继承。表分区就是逻辑上把一个大表分割成物理上的几块，表继承的优势在于：

• 查询、删除更新等操作性能提升，避免全表扫描
• 访问较少的历史数据可以使用表空间技术移动到便宜的慢速存储介质上

创建分区表步骤
1、创建父表，配置好字段。父表不插入数据、不定义约束。
2、创建子表，继承父表字段且不新增字段，自定定义约束，通过关键字段来控制分区，并为关键字段创建索引。
3、定义一个触发器，将原本插入主表的数据重定向到合适的分区表
4、检查constraint_exclusion配置是打开的，打开后查询时如果条件与分区字段匹配，则只查匹配的分区。如果设置为off，则会扫描每张分区子表。
核心配置代码：

CREATE
TABLE
 orders_2023 (
CHECK
 (order_date >= 
'2023-01-01'
AND
 order_date < 
'2024-01-01'
)
) 
INHERITS
 (orders); //子表继承父表orders，并为order_date字段添加约束
// 为关键字段添加索引
CREATE
INDEX
 idx_orders_2023_date 
ON
 orders_2023 (order_date); 
// 触发器代码，配置路由分配
CREATE
OR REPLACE
FUNCTION
 orders_insert_trigger()
RETURNS
TRIGGER
AS
 $$
BEGIN
IF
NEW
.order_date >= 
'2023-01-01'
AND
NEW
.order_date < 
'2024-01-01'
THEN
INSERT
INTO
 orders_2023 
VALUES
 (
NEW
.*);  
-- 插入 2023 年分区
ELSIF
NEW
.order_date >= 
'2024-01-01'
AND
NEW
.order_date < 
'2025-01-01'
THEN
INSERT
INTO
 orders_2024 
VALUES
 (
NEW
.*);  
-- 插入 2024 年分区
ELSE
-- 无匹配分区时抛出异常（避免数据插入父表）
RAISE
EXCEPTION
'订单日期 % 无对应的分区表'
, 
NEW
.order_date;
END
IF
;
RETURN
NULL
;  
-- 父表不存储数据，返回 NULL 即可
END
;
$$
LANGUAGE
 plpgsql;
// 触发器关联关联
order
表，并设置触发逻辑
CREATE
TRIGGER
 trigger_orders_insert
BEFORE
INSERT
ON
 orders  
-- 插入前触发（拦截插入，重定向）
FOR
EACH
ROW
-- 每行数据都触发一次（行级触发器）
EXECUTE
FUNCTION
 orders_insert_trigger();

触发器

由事件自动触发执行的特殊存储过程，触发事件可以是对一个表进行DML操作，一般用于加强数据的完整性约束和业务规则上的约束。格式如下：

CREATE
 [ CONSTRAINT ] TRIGGER name 
{ BEFORE | AFTER | INSTEAD OF }
{ event [ OR ... ] }
ON
 table_name
  [ 
FROM
 referenced_table_name ]
{ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED }
 }
  [ 
FOR
 [ 
EACH
 ] 
{ ROW | STATEMENT }
 ]
  [ WHEN ( condition ) ]
  EXECUTE 
PROCEDURE
function_name
( arguments )

例如，在删除student表数据之后，将score表相关联的数据也删除：

// 触发器执行函数
CREATE
OR REPLACE
FUNCTION
 student_delete_trigger()
RETURNS
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 ();

• 语句级触发器：执行每个SQL语句时，只执行一次，实际操作0行的语句仍有可能被触发。

• 行级触发器：执行每行SQL语句都会执行一次，实际操作0行的语句不会被触发

  CREATE
  FUNCTION
   log_student_trigger ()
  RETURNS
  trigger
  AS
  $$
  BEGIN
      // 
  TG_OP
  是触发器函数中的特殊变量，表示DML操作
  INSERT
  INTO
   log_student 
  values
  (now(), 
  user
  , 
  TG_OP
  );
  RETURN
  NULL
  ;
  END
  ;
  $$
  LANGUAGE
   "plpgsql";

• BEFORE触发器：语句开始做任何事情之前被触发

• AFTER触发器：：语句结束时触发

before和after属于触发器的触发时机，可以用在行级，也可以用于语句级。如果既有行级、也有语句级，那么行级的after会早于语句级after执行。
删除触发器
删除一个触发器使用的语法是drop trigger xxx on table，当删除触发器时，触发器函数本身并不会被删除。完整语法如下：

DROP
TRIGGER
 [ 
IF
EXISTS
 ] 
name
ON
table
 [ 
CASCADE
 | 
RESTRICT
 ];

• IF EXESTS：如果要删除的触发器不存在，提出一个notice而不报错
• CASCADE：级联删除依赖此触发器的对象
• RESTRICT：默认值，有依赖对象就拒绝删除

表空间

PostgreSQL中的表空间，实际上是为表指定一个存储目录，在创建数据库、创建表、索引、唯一约束、主键约束的时候均可以指定表空间。

// 通过
create
tablespace
 xxx 
location
关键字实现新建表空间
CREATE
TABLESPACE
 tbs_data 
location
'/data/pgdata'
; 
//创建数据库时指定表空间，后续的表、索引等都会放这个目录下
create
database
 db01 
tablespace
 tbs_data;

视图

新增视图

CREATE
 [ 
OR
 REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]
AS
 query 
// 将原表中的字段名重命名
CREATE
 VIEW vw_users(no, name,email, mark)  
AS
SELECT
 id, user_name, user_email, user_mark 
FROM
 users
;
// 通过设置触发器来实现视图更新，每次INSERT、UPDATE、DELETE,都会触发insert到视图
CREATE
OR
 REPLACE 
FUNCTION
vw_users_insert_trigger
()
RETURNS
TRIGGER
AS
 $$
BEGIN
INSERT
INTO
users
VALUES
(
NEW
.id, 
NEW
.user_name,
'111111'
, 
NEW
.user_email, 
NEW
.user_mark)
;
  RETURN NULL
;
END
;
$$
LANGUAGE plpgsql
;
CREATE
OR
 REPLACE 
FUNCTION
vw_users_update_trigger
()
RETURNS
TRIGGER
AS
 $$
BEGIN
UPDATE
users
SET
user_email
 = 
NEW
.
user_email
WHERE
id
= 
NEW
.
id
;
  RETURN NULL
;
END
;
$$
LANGUAGE plpgsql
;
CREATE
OR
 REPLACE 
FUNCTION
vw_users_delete_trigger
()
RETURNS
TRIGGER
AS
 $$
BEGIN
DELETE
FROM
users
WHERE
id
= 
NEW
.
id
;
  RETURN NULL
;
END
;
$$
LANGUAGE plpgsql
;
CREATE
 TRIGGER insert_vw_users_trigger
  INSTEAD 
OF
 INSERT 
ON
 vw_users
FOR
EACH
 ROW EXECUTE 
PROCEDURE
vw_users_insert_trigger
()
;
CREATE
 TRIGGER update_vw_users_trigger
  INSTEAD 
OF
 UPDATE 
ON
 vw_users
FOR
EACH
 ROW EXECUTE 
PROCEDURE
vw_users_update_trigger
()
;
CREATE
 TRIGGER delete_vw_users_trigger
  INSTEAD 
OF
 DELETE 
ON
 vw_users
FOR
EACH
 ROW EXECUTE 
PROCEDURE
vw_users_delete_trigger
()
;

• TEMP/TEMPORARY：临时视图，session结束后该视图消失

索引

索引用于快速查询数据，它记录了表中的一列或者多列值与其物理位置之间的对应关系。

• 优点：加快对表中记录的查询和排序
• 缺点：额外增加数据库存储空间、插入修改数据时更新索引需要时间

索引种类

• Btree：最常用索引，也是默认索引类型。B表示平衡balanced，表示树的各个分叉的数据量大致相等。主要适用于处理等值查询和范围查询
• HASH：只能处理等值查询，当索引键是较长字符串时，索引占用空间较大，哈希索引占空间小。
• GiST、SP-GiST：通用搜索树，非单独一种索引，是一种架构，基于该架构可以实现不同的索引策略
• GIN：通用逆向索引，可以处理包含多个键的值，如数组，同样是一种架构。PG的全文检索用的就是GIN索引

新建索引

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

UBUQUE：唯一约束
CONCURRENTLY：并发创建，建索引时不锁表，使用在生产环境大表使用
USING method：可选索引算法，默认B树
column_name：索引键，可单列可多列
其他：用的频度较少
（补充）PG库的索引机制——BTree
BTree属于多叉平衡搜索树，每个节点可以拥有多个子节点，相比二叉树，这种结构减少了树的高度，从而减少I/O次数。

• 平衡：所有叶节点都在同一层
• 有序：节点内有序、任意元素的左子树都小于它，右子树都大于它
• 多路：对于m阶B树的接电：

• * 最多有m个分支、m-1个元素
  

  • 根节点最少有2个分支，1各元素、其他节点有m/2个分支，(m/2)-1个元素

在使用索引时，访问结点是在硬盘上进行的，而结点内的查找是在内存中进行的。
BTree的插入：
先找到插入的位置进行插入，如果没有上溢出（插入为止一定在叶节点），无需调整。否则中间元素(m/2)上移，两边分裂，直到没有上溢为止。

存储过程

语法

变量定义

• 基础变量类型：declare v_num int := 0
• 列类型变量：v_username user_info.username%TYPE
• 行变量类型：v_user user_info%ROWTYPE
• 动态记录类型：v_rec RECODE

变量赋值

• 直接赋值：v_num:=v_num+1
• 从查询结果赋值(单值)：select username into v_username from user_info where id=1
• 从查询结果赋值(整行)：select * into v_user from user_info where id=1

流程判断

//
if
-
else
 条件判断
IF
 v_age < 
THEN
    p_result := 
'未成年'
;
ELSIF
 v_age 
BETWEEN
AND
THEN
    p_result := 
'成年'
;
ELSE
    p_result := 
'老年'
;
END
IF
;
// 多分支
CASE
WHEN
 v_age < 
THEN
 p_result := 
'未成年'
;
WHEN
 v_age 
BETWEEN
AND
THEN
 p_result := 
'成年'
;
ELSE
 p_result := 
'老年'
;
END
CASE
;
// 
for
 循环
FOR
 v_user 
IN
SELECT
 * 
FROM
 user_info 
LOOP
-- 加工数据（示例：年龄+1）
    v_user.age := v_user.age + 
;
-- 返回单条结果
RETURN NEXT
 v_user;
END
LOOP
;
//
while
 循环
WHILE
 v_i <= 
LOOP
    v_sum := v_sum + v_i;
    v_i := v_i + 
;
END
LOOP
;

异常处理

略。

存储结构

• 逻辑结构：数据库簇>数据库>模式>表=视图=索引=函数
• 物理结构：使用环境变量PGDATA指向数据库目录的根目录，根目录初始化后，会生成一系列配置文件。

默认表空间下的存储：
在默认表空间的base目录下的很多子目录，子目录名称与数据库的oid一致。每个子目录下都存放着各自数据库的表、索引文件，每个表和索引都会分配一个文件号relfilennode，数据文件格式以relfilennode.顺序号命名，每个文件最大1GB，当表或索引内容大于1GB时，就会从1开始生成顺序号。

select
oid
,datname 
from
 pg_database;

自定义表空间下的存储：
当用户自己创建表空间并自定义目录时，会在表空间的根目录下生成带有“CatLog version”的子目录，完整格式为【PG_{pg库大版本号}_{Catalog version}】。在这个目录下，才有数据库oid那些子目录。

调优

逻辑结构优化
表优化

• 根据具体情况挑选合适的TOAST策略
• 调整表的fillfactor参数
• 使用临时表
• 使用分区表
• 使用表空间将不同的表分散放在不同存储介质下

索引优化

• 超过300行的表应该需要索引
• 经常与其他表进行连接的表，在连接字段上应该建索引
• 经常出现在where子句/group by/order by中的字段，应该建索引
• 查询很少的列不应该建索引
• 大字段不建议建索引，或建哈希索引，而不是普通索引
• 复合索引建立前应考虑清楚，这几个字段是否经常一起使用，是的话可以建，否则建议走单字段索引
• 不要在符合索引已建立的基础上，再次单独建单个字段的索引
• 符合索引字段个数应小于等于3个
• 频繁进行数据操作的表不要建立太多索引
• 删除无用的索引
• 索引创建时可以将索引放置在SSD盘的表空间，常规的表数据则放在机械硬盘

SQL优化

• 避免全表扫描和排序，考虑在where子句条件字段和排序字段中建立索引
• 范围查询较多时，通过CLUSTER table_name USING index_name 将存储物理排序与索引顺序一致，减少IO以提高查询效率
• 避免在where子句中对字段进行函数或表达式操作，这会导致走不到索引
• 用exists替代in查询
• 数值信息的字段不要涉及成字符型，且优先使用int和bigint，如果这2个类型范围内不能表示，再用numeic
• 避免使用select * 返回所有列
• 尽量多使用表的别名，并将别名前缀于每个列上
• 适当使用存储过程，减少客户端和数据库的交互次数
• COPY导出数据比INSERT和批量INSERT快得多
• 存储过程中减少使用循环