Comment by CraigJPerry | Hacker News Reader

Comment by CraigJPerry | Hacker News Reader

Is there any way to exploit parallelism in the customer vs employee queries then merge the results?

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.

MS SQL will do most of the work in parallel, but you can control if that is what you want.

I'm pretty sure later versions of PG can indeed execute both in parallel.

Not sure about each side of the UNION, but postgres can do parallel index scans for even simple criteria: https://www.postgresql.org/docs/10/parallel-plans.html#PARAL...

Also the article says these tests were done on postgres 12.6

Temp table as in using WITH?

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