Markus Winand • 12/10/2024

Index Usage For EXTRACT(YEAR FROM …), YEAR(…) etc.

This technical article analyzes how major DBMS like PostgreSQL, MySQL, Oracle, and SQL Server automatically rewrite WHERE clause expressions using date/time functions (e.g., EXTRACT(YEAR FROM ...), YEAR(...)) into range expressions to leverage sort-based indexes. It includes a comparison chart and discusses the unconditional rewrite behavior, even when function-based indexes exist, providing insights for SQL performance tuning.

0 comments

#Database Optimization #SQL Performance #Date Functions