|
|
|
|
|
|
by swashq
165 days ago
|
|
|
A few patterns that stress different parts of the engine: Window functions (tests optimizer):
SELECT , ROW_NUMBER() OVER (PARTITION BY category ORDER BY date DESC)
FROM large_table Large aggregations (tests hash tables):
SELECT category, COUNT(), AVG(value), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value)
FROM large_table GROUP BY category Self-joins (tests join algorithms):
SELECT a., b. FROM table a
JOIN table b ON a.foreign_key = b.id
WHERE a.date BETWEEN '2024-01-01' AND '2024-12-31'; String operations (often overlooked):
SELECT * FROM table WHERE column LIKE '%pattern%'; Also test with varying data sizes (1K, 100K, 10M rows). Some engines optimize differently at scale. |
|
|