Comment by Albert_Camus | Hacker News Reader

Comment by Albert_Camus | Hacker News Reader

Parent Full thread

Author of the original article here. Temporary tables are different than using WITH (which are common table expressions, or CTEs). In many database engines, can make a temporary table that will persist for a single session. The syntax is the same as table creation, it just starts with CREATE TEMPORARY TABLE ....

More info in the PostgreSQL docs [1]:

> If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

[1]: https://www.postgresql.org/docs/13/sql-createtable.html

If this is a database for reporting, using a temporary table is probably fine and a union all wouldn’t concern me.

On Mysql, using a union all creates a temp table which can perform catastrophically under database load. I’ve seen a union all query with zero rows in the second half render a database server unresponsive when the database was under high load, causing a service disruption. We ended rewriting the union all query as two database fetches and have not seen a single problem in that area since.

I was shocked by this union all behavior, but it is apparently a well known thing on MySQL.

I can’t speak to Postgres behavior for this kind of query.

In the past we worked on a system that used MySQL 8. We used UNION (not UNION ALL, but I assume it doesn't matter) in several places, applying it to improve performance as we described in the article. There were definitely cases in the system where one side of the UNION would return zero rows, but we never ran into any of the types of issues you're describing.

Yeah, you _really_ have to watch out for this. I once spent months chasing down a serious but rare performance problem in a large-scale mysql 5.6 deployment, which was eventually root-caused to kernel slab reclaim pressure, caused by very high XFS metadata mutation rate, caused by MySQL creating an on-disk temporary ISAM file for every query with a union, which was most of the traffic.

Reply on news.ycombinator.com