
ALT Unnesting Arbitrary Queries Thomas Neumann and Alfons Kemper Technische Universita ̈t Mu ̈nchen Munich, Germany [email protected], [email protected] Abstract: SQL-99 allows for nested subqueries at nearly all places within a query. From a user’s point of view, nested queries can greatly simplify the formulation of complex queries. However, nested queries that are correlated with the outer queries frequently lead to dependent joins with nested loops evaluations and thus poor perfor- mance. Existing systems therefore use a number of heuristics to unnest these queries, i.e., de-correlate them. These unnesting techniques can greatly speed up query processing, but are usually limited to certain classes of queries. To the best of our knowledge no existing system can de-correlate queries in the general case. We present a generic approach for unnesting arbitrary queries. As a result, the de-correlated queries allow for much simpler and much more efficient query evaluation.

ALT temuze: Back at my old job in ~2016, we built a cheap homegrown data warehouse via Postgres, SQLite and Lambda. Basically, it worked like this: - All of our data lived in compressed SQLite DBs on S3. - Upon receiving a query, Postgres would use a custom foreign data wrapper we built. - This FDW would forward the query to a web service. - This web service would start one lambda per SQLite file. Each lambda would fetch the file, query it, and return the result to the web service. - This web service would re-issue lambdas as needed and return the results to the FDW. - Postgres (hosted on a memory-optimized EC2 instance) would aggregate. It was straight magic. Separated compute + storage with basically zero cost and better performance than Redshift and Vertica. All of our data was time-series data, so it was extraordinarily easy to partition. Also, it was also considerably cheaper than Athena...














