Bekerja dengan tabel sementara

Meningkatkan Performa Kueri di PostgreSQL

Amy McCarty

Instructor

Tentang tabel sementara (temp)

Apa?

Tabel berumur pendek

Mengapa?

Penyimpanan sementara
Sesi database
Beberapa kueri
Spesifik pengguna
Tabel lambat

Bagaimana?

CREATE TEMP TABLE name AS

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

Hari Libur AS

holiday	holiday_type
Epiphany	religious
Thanksgiving	secular

Tabel besar yang lambat

Lambat karena banyak rekaman

Statistik Tabel	World Holidays	USA Holidays
Tipe	table	temp table
# Baris	591,444	25

View rumit yang lambat

Lambat karena logika view

Diagram yang menunjukkan tabel tiap negara memberi masukan ke view World Holidays

Statistik Tabel	World Holidays	USA Holidays
Tipe	view	temp_table
# Baris	591,444	25
Sumber	195	1

Tabel menyimpan data
View berisi petunjuk ke data

Menggabungkan banyak tabel ke satu

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

Perencana kueri (langkah eksekusi)

Beberapa koki mengelilingi panci besar

Statistik dari pg_statistics
Perkiraan waktu jalan

Ayo berlatih!

Meningkatkan Performa Kueri di PostgreSQL