Snowflake - Poor performance on clustered table

Question

I have a quite large table (1.5TB, 18 billion records) that holds IoT type of data. In general, it has EVENT_TYPE (VARCHAR), EVENT_TIME (TIMESTAMP), and some columns storing event data. There are 12 event types in total with varying record distribution (from 5-10 million to 1-5 billion).

Most of the queries are filtering on specific event type and date range. Also, there are views created pointing to the data for specific event type. These are usually queried by date range.

There is clustered index created on EVENT_TYPE, CAST(EVENT_TIME AS DATE). I thought it would be most suitable for this type of workload. However, the performance is not very good.

For example, query like SELECT COUNT(1) FROM <event table> WHERE EVENT_TIME >= '2020-01-01' AND EVENT_TYPE = '<some type>' runs for 30 seconds on XL warehouse. I would expect better performance, as it 's using indexed columns. Also, it is scanning 25% of the partitions (42,786 out of 174,201), while it covers only 0.5% of total data.

I suspect that clustered index is not optimal. Here are initial results from SYSTEM$CLUSTERING_INFORMATION:

{
  "cluster_by_keys" : "LINEAR(EVENT_TYPE, CAST(EVENT_TIME AS DATE))",
  "total_partition_count" : 175792,
  "total_constant_partition_count" : 44575,
  "average_overlaps" : 97273.7777,
  "average_depth" : 93801.4483,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 44536,
    "00002" : 0,
    "00003" : 0,
    "00004" : 0,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0,
    "131072" : 130790,
    "65536" : 466
  }
}

I have tried re-clustering the table, but it did not improve much:

{
  "cluster_by_keys" : "LINEAR(EVENT_TYPE, CAST(EVENT_TIME AS DATE))",
  "total_partition_count" : 173905,
  "total_constant_partition_count" : 55880,
  "average_overlaps" : 78938.3633,
  "average_depth" : 74663.1889,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 55829,
    "00002" : 0,
    "00003" : 0,
    "00004" : 0,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0,
    "04096" : 5,
    "08192" : 7,
    "131072" : 117196,
    "16384" : 15,
    "32768" : 529,
    "65536" : 324
  }
}

Looking into results above I see a couple of issues: 1. average_overlaps and average_depth appears to high 2. The histogram is skewed to the end. I would expect more or less even distribution

Any ideas on what might be wrong, or how to improve it?

Thanks, Rimvis

Answer 1

TLDR: Try using hash(event_type) instead of event_type itself when defining your clustering keys.

---

I had the exact same issue, and our Snowflake sales engineer told us that it is caused by Snowflake using only the first six characters of a varchar field for determining the value of its clustering key.

In other words, all of the following values would be considered identical from the perspective of Snowflake's clustering operation:

event_1
event_2
event_3

In my case the clustering was showing a similar distribution as yours: good average depth, good overlap, a large percent of constant partitions, but a very strange tail in the partition depth histogram.

{
  "cluster_by_keys" : "LINEAR(\n  received_date,\n  event_type\n)",
  "total_partition_count" : 4214,
  "total_constant_partition_count" : 4129,
  "average_overlaps" : 1.0152,
  "average_depth" : 1.7243,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 4119,
    "00002" : 3,
    "00003" : 4,
    "00004" : 0,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 3,
    "00015" : 0,
    "00016" : 0,
    "00032" : 14,
    "00064" : 71
  }
}

To fix this, I experimented by using a clustering key on the hash(event_type) rather than the event type itself. Notice how this improves all the metrics as well as fixes the tail on the histogram of clustering depth.

{
  "cluster_by_keys" : "LINEAR(\n  received_date, hash(event_type)\n)",
  "total_partition_count" : 2028,
  "total_constant_partition_count" : 1653,
  "average_overlaps" : 0.357,
  "average_depth" : 1.2002,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 1643,
    "00002" : 367,
    "00003" : 15,
    "00004" : 3,
    "00005" : 0,
    "00006" : 0,
    "00007" : 0,
    "00008" : 0,
    "00009" : 0,
    "00010" : 0,
    "00011" : 0,
    "00012" : 0,
    "00013" : 0,
    "00014" : 0,
    "00015" : 0,
    "00016" : 0
  }
}

It's worth noting that the total number of partitions has decreased on the better clustered table. So some queries that were only pulling a few partitions may actually be slower, as they need to load and scan a larger sized partition.

But my guess is you'll have better performance by clustering on the hash(event) type over the event_type itself for most workloads.

Answer 2

Regarding your overlaps and depth, that is actually what you want. You want those numbers to be as close to 1 as possible.

I recommend a few things for you:

1) Contact support and find out why auto-clustering isn't enabled

2) Recreate that table using an INSERT WITH OVERWRITE SELECT * FROM table ORDER BY event_time, event_type using the largest warehouse you can make (so it doesn't spill). This will save you money when auto-clustering finally starts for you.

3) Create your cluster on event_time::DATE, event_type, instead of the other way around.

I know the documentation specifies the other way around for those keys, but since you are loading the data every day by that date, this will naturally order your data by date, so you'll have far less auto-clustering costs to keep things nice and neat. And if you don't have enough data to benefit from the additional key on event_type, then you can just drop it anyway. Meaning, if the amount of data stored for a single day of data is only a few micro-partitions of data, then the additional key won't help very much, anyway.