topic Re: What's the best practice on running ANALYZE on Delta Tables for query performance optimization? in Data Engineering

What's the best practice on running ANALYZE on Delta Tables for query performance optimization?

aladda — Fri, 28 May 2021 18:50:52 GMT

User16826994223 — Tue, 08 Jun 2021 11:10:01 GMT

Nicely Written

aladda — Sun, 20 Jun 2021 03:21:49 GMT

The ANALYZE Command specifically captures statistics which are relevant for the Cost Based Optimizer to make better decisions.
The 32 columns of statistics that Delta auto-collects are specifically for data skipping. This is separate from the ANALYZE command
The reason docs currently say Do not run on Delta tables’ is because Its best to run Analyze on Delta tables after completion of any data update/delete operation and when the data has changed by around 10%. This gives the CBO the best and most up-to-date statistics to work with
General best practices:
- ANALYZE whenever the data has changed by about 10%
- Make sure when you use ANALYZE, you are specifying the COLUMNS or PARTITIONS you want to collect statistics for. Otherwise, as you have noted, it will re-analyze the entire table
- Here is the syntax: https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-aux-analyze-table.html

jlickt — Thu, 09 May 2024 17:10:51 GMT

Super write-up; very useful in understanding how the Delta and non-Delta approaches have evolved.