3 ms·
I'm not sure leaving out sort and dist keys is actually being fair to Redshift here. I get the argument that you're avoiding any "special tuning", but these ar
by robbles 9y ago
I'm not sure leaving out sort and dist keys is actually being fair to Redshift here.
I get the argument that you're avoiding any "special tuning", but these are basic features that the docs and most guides will tell you are not really optional. In my mind it's more like leaving out the primary key declaration on a table in Postgres or MySQL.
- georgewfraser 9y ago(author here) It's a tough call. In my experience, sort and dist keys only really work out in < 50% of queries, and it's the other 50% where you judge the performance of your warehouse. If we had chosen to use sort and dist keys in Redshift, then we would have also used clustering keys in Snowflake, and it probably would have been somewhat of a wash. BigQuery doesn't really have an equivalent feature yet---they have date partitioning, but that's a very narrow special case that won't work for most queries.
- ruw1090 9y agoIt looks like you had to rewrite the queries pretty significantly to get them to run on all three. Redshit/Bigquery don't support rollup/grouping sets/intersect/except etc. which the TPCDS spec queries have. Is there a reason you don't mention this in the blog post? SQL surface area seems like a pretty important thing to consider when choosing a DW.
- georgewfraser 9y agoI talk about that a little in footnote 5. The changes were mostly just syntax, which I don't think is very important. You may disagree, but surely we can both agree that the important thing is it's all in GitHub so you can see the edit history of the queries :)
- dimfeld 9y agoGROUP BY ROLLUP support actually just arrived in Bigquery a week or two ago. Still a lot of things missing, but they are steadily improving.