Presto Overview, Architecture, and Query Optimization Techniques

Presto is an open‑source Massive Parallel Processing (MPP) SQL engine originally developed by Facebook, designed for fast, real‑time data analysis without storing data itself, using a Connector SPI to access various data sources such as Hive, MySQL, Kudu, and Kafka.

The engine follows a master‑slave architecture consisting of a single Coordinator that parses SQL, generates execution plans, and distributes tasks to multiple Worker nodes that perform the actual query execution.

Presto provides built‑in connectors for many sources and allows custom connectors to query bespoke data stores; for example, a Hive connector requires a Hive MetaStore service to supply metadata while Workers read raw data from HDFS.

Key factors enabling low‑latency queries include fully in‑memory parallel computation, pipelining, localized processing, dynamic compilation of execution plans, careful memory and data‑structure usage, GC control, and the intentional omission of fault‑tolerance mechanisms.

Storage optimization recommendations cover proper partitioning, using columnar ORC files (which Presto reads more efficiently than Parquet), applying Snappy compression to reduce I/O, and pre‑sorting data to allow ORC to skip unnecessary reads.

Query optimization tips include selecting only required columns (avoiding *), adding partition columns to filter predicates, ordering GROUP BY fields by descending distinct‑value count, applying LIMIT with ORDER BY for top‑N queries, replacing multiple LIKE clauses with regexp_like, and using RANK() instead of row_number() for better performance.

<span>[GOOD] SELECT GROUP BY uid, gender</span></code><code><span>[BAD] SELECT GROUP BY gender, uid</span>

Join optimization advises placing the larger table on the left side of the join (default broadcast join), enabling distributed joins (hash joins) via the distributed-joins-enabled setting and session option, and using SET SESSION distributed_join = 'true' before queries. set session distributed_join = 'true'; When both tables are large, distributed joins partition both sides by hash of the join key, reducing memory pressure at the cost of increased network traffic.

Additional join improvements include replacing OR conditions with UNION queries, using WITH common table expressions to consolidate sub‑queries, preferring UNION ALL over UNION when deduplication is unnecessary, and structuring SQL as shown below:

WITH tmp AS (SELECT DISTINCT a1, a2 FROM t2)
SELECT ... FROM t1 JOIN tmp ON t1.a1 = tmp.a1
UNION ALL
SELECT ... FROM t1 JOIN tmp ON t1.a2 = tmp.a2;