Also the article says these tests were done on postgres 12.6
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
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.
My intuition would have been to tackle this in 3 steps with a temp table (create temp table with employee markouts, append customer meals, select ... order by). I can see the overheads of my solution but I’m wondering if i could beat the Union all approach somehow. My temp table wouldn’t benefit from an index so I’m left wondering about parallelism.
If i remember correctly though, there’s no concurrency, and therefore no opportunity for parallelism for transactions in the same session.