Working with temporary tables

Improving Query Performance in PostgreSQL

Amy McCarty

Instructor

About temp(orary) tables

What?

Short-lived table

Why?

Transient storage
Database session
Multiple queries
User specific
Slow tables

How?

CREATE TEMP TABLE name AS

TEMP table structure

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 Holidays

holiday	holiday_type
Epiphany	religious
Thanksgiving	secular

Slow, large tables

Slow because many records

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

Slow, complicated views

Slow because view logic

Diagram showing that individual country tables feed the World Holidays view

Table Stats	World Holidays	USA Holidays
Type	view	temp_table
# Rows	591,444	25
Sources	195	1

Tables contain data
Views contain the directions to data

Joining many tables to one

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

Query planner (execution steps)

Multiple chefs around a big cooking pot

Statistics from pg_statistics
Runtime estimates

Let's practice!

Improving Query Performance in PostgreSQL