A few weeks ago I was working with indexes. I found out that a query must be sargable in order to use an index. I created a table with 100 entries of mock data. then i queried it with some sargable and some non-sargable queries. when i looked into the query details, i saw that with sargable queries, not all rows were read. when i ran a non-sargable query, all columns were read. now i wanted to retry that, but no matter what i do, it always reads all columns. I don’t understand why the result has changed.
Here is an example of the queries I used:
--reads all rows
SELECT * from MOCK_DATA WHERE date_diff('week',md_date,current_date) >= 30;
--reads only necessary rows
SELECT * from MOCK_DATA WHERE md_date < date_add('week',-30,current_date);
Does anyone have any idea why the behavior has changed?
Besides using warp speed, is there anything else I should be aware of regarding indexes in Starburst Galaxy to make my query more efficient?
Well… first… I had to look up Sargable - Wikipedia and while I didn’t know the term (we learn something every day) thankfully I did understand the definition. Thanks for the one. I’ll try to work it into a conversation today to make myself smile.
Yes, warp speed could always help, unless you partitioned on md_date already. That would explain why less data is read and you can see it in the query plan in the source stage in the partition pruning section. Warp speed does NOT attempt to create an index for partitioning as that already does the trick for that scenario (it’ll still start caching the data, of course).
Another thing that helps is that if you are using Parquet or ORC then then much less data could possibly be read since those underlying columnar files keep statistics on each one of themselves. The query engine checks that stuff first in hopes of abandoning any further reads if it realizes the min/max values preclude the value you are looking for are absolutely not in the file.
This is compounded by the fact that for date/ts fields they are very likely already in a natural order which helps this file abandonment happen much much better.
Then, if you throw Iceberg (or DL) into the mix, those file-level statistics are duplicated in the metadata files which mean that files that can’t possibly contain the data you’re looking are fully short-circuited before ANY read of them (not just the included stats) are read and this is one of the BIG benefits of the modern table formats.
Thanks for the fast and detailed response lester! I did not know that parquet and ORC keep statistics of their clumnar files, but thats really good to know.
I’m afraid I need to delve a little deeper. Besides from using Warp Speed, is there any way to leverage indexes in Starburst Galaxy? If somebody is using Warp Speed is there anything one hast to keep an eye on? Does the Sargability affect the use of indexes in Starburst? And is there any way to look into the file-level statistics of an Iceberg table?
Thanks in advance!
For the most part, there is nothing you need to do, or worry about, with Warp Speed. It’s choices of what to index and cache are autonomous based on the query workloads it is watching. There is a free tutorial over at https://academy.starburst.io/test-warp-speed-with-starburst-galaxy if you want to see some canned examples of this.
In the data lake table formats, there are no TRUE indexes like in a RDBMS. There are things that ACT LIKE indexes from behavior. For example, those statistics in the files (and in the Iceberg metadata, too) coupled with a naturally sorted (or even mostly sorted) data on the column you are wanting emulate an index. For additional secondary indexes beyond that, you’ll mostly need to look for frameworks like warp speed, or if/when Trino’s Iceberg connector can tackled Z-Ordering, described below, you’ll have a chance to simulate multiple indexes.
Good news on Iceberg, there are “metadata tables” described at Iceberg connector — Trino 451 Documentation that can help. The $files one will help you on this concept of some of the stats in the underlying files being duplicated for performance reasons.
SELECT * FROM "test_table$files"
1 Like