优化PostgreSQL用户注册多表批量插入查询的技术咨询

• 存在一个注册端点，用户提交邮箱和密码；
• 需要一次性创建4条数据行，同时生成多个UUID；
• 涉及的表结构如下：

authentication_types表 #

accounts表 #

users表 #

user_accounts表 #

verification_tokens表 #

用户注册时需执行以下操作：

• 生成UUID并插入accounts表；
• 生成UUID并插入users表；
• 生成UUID，结合accounts和users的插入ID插入user_accounts表；
• 生成UUID、token，结合accounts的ID插入verification_tokens表。

我设计了如下基于CTE的查询，先获取password对应的authentication_type_id，再在各步骤生成UUID：

WITH account_data(id, email, password, authentication_type_id) AS (
      VALUES( gen_random_uuid()
             ,:email
             ,:password
             ,(SELECT id 
               FROM authentication_types 
               WHERE name = :authenticationTypeName) ) )
,ins1(user_id) AS (
      INSERT INTO users(id, enabled) 
      VALUES( gen_random_uuid()
             ,true)
      RETURNING id AS user_id )
,ins2(account_id) AS (
      INSERT INTO accounts (id, email, password, authentication_type_id) 
      SELECT id
            ,email
            ,password
            ,authentication_type_id 
      FROM account_data 
      RETURNING id AS account_id )
,ins3 AS (
      INSERT INTO user_accounts (id, account_id, user_id) 
      VALUES( gen_random_uuid()
             ,(SELECT account_id 
               FROM ins2)
             ,(SELECT user_id 
               FROM ins1)       ) )
INSERT INTO verification_tokens (id, token, account_id) 
VALUES( gen_random_uuid()
       ,:token
       ,(SELECT account_id 
         FROM   ins2)     ) 
RETURNING (SELECT account_id FROM ins2) AS id

此外我还准备了含实际数据的示例查询、可视化查询分析器截图及执行计划截图，请问是否有进一步优化该查询的方法？

1. 提前生成所有UUID，简化CTE结构 #

可以一次性生成所有需要的UUID，避免在多个CTE中重复调用gen_random_uuid()，逻辑更紧凑，也能减少函数调用开销：

WITH pre_generated AS (
  SELECT
    gen_random_uuid() AS account_id,
    gen_random_uuid() AS user_id,
    gen_random_uuid() AS user_account_id,
    gen_random_uuid() AS verification_token_id,
    (SELECT id FROM authentication_types WHERE name = :authenticationTypeName) AS auth_type_id
),
ins_users AS (
  INSERT INTO users(id, enabled) 
  SELECT user_id, true FROM pre_generated
  RETURNING id
),
ins_accounts AS (
  INSERT INTO accounts(id, email, password, authentication_type_id)
  SELECT account_id, :email, :password, auth_type_id FROM pre_generated
  RETURNING id AS account_id
),
ins_user_accounts AS (
  INSERT INTO user_accounts(id, account_id, user_id)
  SELECT user_account_id, account_id, user_id FROM pre_generated
)
INSERT INTO verification_tokens(id, token, account_id)
SELECT verification_token_id, :token, account_id FROM pre_generated
RETURNING account_id AS id;

2. 避免独立子查询，直接关联CTE数据 #

原查询中ins3和最后的verification_tokens插入操作使用了(SELECT account_id FROM ins2)这类独立子查询，虽然结果正确，但后续逻辑扩展时可能存在隐患。上面的优化方案直接从预生成CTE中取对应ID，更高效也更清晰。

3. 固定认证类型可跳过表查询 #

如果password是唯一的认证类型，可以直接把对应的UUID（0aa4d9a9-e024-4792-bc41-36a4f3528d36）作为常量传入，省去每次查询authentication_types表的开销。当然如果后续有多种认证类型，这个方法不适用。

4. 优化UUID生成性能 #

如果注册请求量较大，gen_random_uuid()的性能可能成为瓶颈，可以考虑改用uuid_generate_v4()（需要先安装uuid-ossp扩展），两者功能一致，但部分场景下uuid-ossp的生成效率略高，可根据实际测试结果选择。

5. 确保外键列存在索引 #

PostgreSQL不会自动给外键列创建索引（仅当被引用列是主键时），所以需要手动给accounts.authentication_type_id、user_accounts.account_id、user_accounts.user_id、verification_tokens.account_id创建索引，避免插入时的全表扫描，提升写入性能。

内容的提问来源于stack exchange，提问作者PirateApp