Антипаттерн 'create temporary table'

Приложение строило отчеты по базе PostgreSQL. И забикс с графаной жаловались на необычно долгий вакуум и высокую нагрузку по записи (которой вроде как вообще не должно было быть).

Анализ запросов показал следующий сценарий - создается временная таблица, которая наполняется одним SELECT-ом, и используется далее ровно один раз:

  CREATE TEMPORARY TABLE some_table_tmp ON COMMIT DROP AS 
  	SELECT column2
	FROM table1
	WHERE column1 = <value>;

  ...

  SELECT column2, column3, MIN(date_change)
  FROM table2
  WHERE column2 IN (SELECT column2 FROM some_table_tmp)
  GROUP BY column2, column3;

Временная таблица сохранилась на диск.

А можно было без записи ?

Конечно, можно переписать на Common table expressions:

  WITH some_table_tmp AS ( 
  	SELECT column2
	FROM table1
	WHERE column1 = <value>
  )
  SELECT column2, column3, MIN(date_change)
  FROM table2
  WHERE column2 IN (SELECT column2 FROM some_table_tmp)
  GROUP BY column2, column3;

Вывод

временные таблицы - плохо
в простых случаях - легко переписать на CTE

Как можно найти такие запросы

Например, при помощи pg_stat_statements:

SELECT calls, round(total_time/calls) "average", round(total_exec_time), query 
FROM pg_stat_statements 
WHERE lower(query) like '%create temp%'
ORDER BY total_time desc