使用暫存資料表

改進 PostgreSQL 的查詢效能

Amy McCarty

Instructor

關於 temp（temporary）資料表

是什麼？

壽命短的資料表

為什麼？

暫時儲存
資料庫工作階段
多個查詢
使用者特定
慢速資料表

怎麼做？

CREATE TEMP TABLE name AS

TEMP 資料表結構

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';

USA 假日

holiday	holiday_type
Epiphany	religious
Thanksgiving	secular

龐大且慢的資料表

因為筆數多而變慢

Table Stats	World Holidays	USA Holidays
Type	table	temp table
# Rows	591,444	25

複雜又慢的檢視

因為檢視邏輯而變慢

圖示：各國資料表匯入 World Holidays 檢視

Table Stats	World Holidays	USA Holidays
Type	view	temp_table
# Rows	591,444	25
Sources	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 的統計
執行時間估計

一起來練習吧！

改進 PostgreSQL 的查詢效能