Visit the post for more.
Overview
The article discusses enhancements made to the Presto ORC reader, focusing on performance improvements through features like columnar reads, predicate pushdown, and lazy reads. It highlights the significant speedups achieved in query performance and raw decoding, demonstrating the advantages of the new reader compared to the old Hive-based ORC reader.
What You'll Learn
1
How to leverage lazy reads in Presto for improved query performance
2
Why predicate pushdown is crucial for optimizing data queries
3
How to implement a new ORC reader for Presto that supports advanced features
Prerequisites & Requirements
- Understanding of Presto and ORC file formats
- Experience with SQL query optimization(optional)
Key Questions Answered
What improvements were made to the Presto ORC reader?
The Presto ORC reader was enhanced to support columnar reads, predicate pushdown, and lazy reads, resulting in significant performance gains. For instance, single-column reads showed speedups of 3.5x to 4x, while queries utilizing lazy reads and predicate pushdown achieved speedups of 4x and 30x, respectively.
How does the new Presto ORC reader compare to the old Hive-based ORC reader?
The new Presto ORC reader significantly outperforms the old Hive-based ORC reader, achieving 2-4x speedup in wall time and CPU time. This improvement is attributed to its support for advanced features like lazy reads and predicate pushdown, which were not available in the previous version.
What are the benefits of using lazy reads in Presto?
Lazy reads allow the query engine to inspect only the necessary columns to evaluate a query filter, thus saving CPU resources. This is especially beneficial for datasets with many distinct values, where traditional predicate pushdown may not be effective.
What performance metrics were observed with the new ORC reader?
Performance tests indicated that for single-column reads, the speedup in end-to-end query latency ranged from 3.5x to 4.5x. Additionally, the CPU time required for processing queries showed improvements of 4.5x to 6.5x for single-column reads, demonstrating the efficiency of the new reader.
Key Statistics & Figures
Speedup for single-column BIGINT or DOUBLE reads
3.5x
Observed during performance tests comparing the new Presto ORC reader to the old Hive-based ORC reader.
Speedup for single-column VARCHAR reads
7x
Demonstrated in the performance tests with the new Presto ORC reader.
Speedup for queries utilizing lazy reads
4x+
Achieved when lazy reads were effectively applied in query execution.
Speedup for queries utilizing predicate pushdown
30x+
Significant performance improvement noted when predicate pushdown was used in queries.
Technologies & Tools
Some links below are affiliate links. We may earn a commission if you make a purchase.
Backend
Presto
Used as the SQL engine optimized for low-latency interactive analysis.
Data Format
Orc
Columnar storage format used to improve data reading efficiency.
Data Format
Dwrf
A fork of ORC used at Facebook that supports lazy reads.
Key Actionable Insights
1Utilize the new features of the Presto ORC reader to enhance query performance significantly.By implementing lazy reads and predicate pushdown, you can optimize your SQL queries, especially for large datasets, leading to faster data retrieval and analysis.
2Consider transitioning from Hive-based ORC readers to the new Presto ORC reader for better performance.The new reader offers substantial speedups and supports advanced features that can greatly improve productivity for data analysts and engineers.
3Monitor the performance metrics of your queries to identify areas for optimization.Understanding how different features like lazy reads and predicate pushdown affect performance can help you tailor your queries for maximum efficiency.
Common Pitfalls
1
Overlooking the benefits of predicate pushdown and lazy reads can lead to suboptimal query performance.
Many users may not realize that without utilizing these features, they miss out on significant performance improvements, especially in large datasets.
2
Assuming that the old Hive-based ORC reader will suffice for all workloads.
This assumption can lead to slower query times and increased resource consumption, as the new Presto ORC reader offers substantial enhancements.
Related Concepts
Data Query Optimization
Columnar Storage Formats
Presto Performance Tuning