使用临时表

提升 PostgreSQL 查询性能

Amy McCarty

Instructor

关于临时表（temp）

是什么？

短期存在的表

为何使用？

临时存储
数据库会话内
供多条查询使用
用户特定
原表很慢

如何创建？

CREATE TEMP TABLE name AS

临时表结构

holiday	holiday_type	country_code
Epiphany	religious	CZE
Epiphany	religious	FRA
Epiphany	religious	USA
Thanksgiving	secular	USA

CREATE TEMP TABLE usa_holidays AS
  SELECT holiday, holiday_type
  FROM world_holidays
  WHERE country_code = 'USA';

美国节假日

holiday	holiday_type
Epiphany	religious
Thanksgiving	secular

庞大表导致变慢

记录多，查询慢

表统计	World Holidays	USA Holidays
类型	table	temp table
行数	591,444	25

复杂视图导致变慢

视图逻辑复杂，查询慢

图示：各国表汇入 World Holidays 视图

表统计	World Holidays	USA Holidays
类型	view	temp_table
行数	591,444	25
来源	195	1

表包含数据
视图包含取数指令

将多表连接到一张表

CREATE TEMP TABLE usa_holidays AS
  SELECT holiday, holiday_type
  FROM world_holidays
  WHERE country_code = 'USA';

WITH religious AS 
(   SELECT usa.holiday, r.initial_yr
     , r.celebration_dt
    FROM religious r
    INNER JOIN usa_holidays usa 
      USING (holiday)  )
, secular AS 
(   SELECT usa.holiday, s.initial_yr
     , s.celebration_dt
    FROM secular s
    INNER JOIN usa_holidays usa
      USING (holiday)  )
, ...

ANALYZE

1 CREATE TEMP TABLE usa_holidays AS
2 SELECT holiday, holiday_type
3 FROM world_holidays
4 WHERE country_code = 'USA';
5 
6 ANALYZE usa_holidays;
7 
8 SELECT * FROM usa_holidays

查询规划器（执行步骤）

多位厨师围着一口大锅

来自 pg_statistics 的统计信息
运行时估计

Passons à la pratique !

提升 PostgreSQL 查询性能