Why Is 'Select Star' Slow in SQL Queries?

TL;DR

'Select star' can be slow due to several factors, including the inability to effectively use index-only scans, which forces extra lookups. Additionally, deserialization costs for converting raw bytes into usable data structures, as well as network transmission overhead, significantly impact performance. Large or non-inline columns may require additional IO operations, further exacerbating the delays.

Transcript

select star from table is a universal SQL syntax that will basically return all columns in the table that you're selecting it's been said that select star is slow and to avoid it like don't return on column because it is slow but I want to actually dive deep into the reasons of why select star is is slow some of these reasons you might already know... Read More

Key Insights

• 🫰 "Select Star" can be slow due to limitations of the index-only scan optimization, which bypasses when selecting all columns.
• 🍓 Deserialization of columns from raw bytes to proper data structures adds processing overhead and affects performance.
• 🐢 Non-inline columns, such as large strings or blobs, stored outside the page, require additional IO and CPU operations, resulting in slower performance.
• 🪜 Network transmission of the query result adds latency and bandwidth limitations, impacting the overall performance of "Select Star."

Questions & Answers

Q: Why is "Select Star" slow in SQL?

There are several reasons for the slow performance, including limitations of index-only scans, deserialization costs, storing non-inline columns, and network transmission overhead. These factors contribute to increased processing time and reduced efficiency.

Q: What are index-only scans?

Index-only scans occur when an index includes all the necessary columns for a query, allowing the database to retrieve the data directly from the index without accessing the table. However, when using "Select Star," these optimizations are bypassed, leading to a slower performance.

Q: How does deserialization impact the performance of "Select Star"?

Deserialization involves converting raw bytes from the database page into usable data structures. When selecting all columns, the database needs to deserialize each column, which adds overhead and can slow down the process, especially for large data sets.

Q: Why do non-inline columns affect the performance of "Select Star"?

Non-inline columns, such as large strings, blobs, or JSON documents, are stored outside the page and require additional operations to access. Retrieving and processing these columns adds extra IO and CPU overhead, contributing to the slow performance of "Select Star."

Q: How does network transmission impact the performance of "Select Star"?

When retrieving the query result, the data needs to be transmitted over the network from the database to the client application. This process incurs latency and bandwidth limitations, especially when transferring large response data, leading to slower performance.

Summary & Key Takeaways

• "Select Star" returns all columns in a table, but it can impact performance due to the complexity of the backend engineering stack and network aspects.

• Reasons for the slow performance include limitations of index-only scans, deserialization costs, storing non-inline columns, and network transmission overhead.