sql-pro

Start by running EXPLAIN ANALYZE to see actual row counts versus estimates. Look for sequential scans on large tables, add or fix indexes, filter rows early before joins, and rewrite correlated subqueries as set-based aggregation joins.

Run EXPLAIN with ANALYZE, BUFFERS, and FORMAT TEXT options. Check for Seq Scan on large tables indicating missing indexes, actual rows far exceeding estimated rows signaling stale statistics, and high buffer read counts indicating cache misses.

CTEs isolate expensive logic for reuse and readability, letting you filter early and reference the result multiple times. Subqueries, especially correlated ones, execute once per outer row and are typically slower than an equivalent aggregation join.

Yes, dialects differ in date functions, pagination syntax, index features like PostgreSQL covering indexes with INCLUDE, and optimizer behavior. The skill loads dialect-specific guidance from its references when targeting a particular platform.

Use a covering index when a frequent query touches a fixed set of columns, so the database can answer entirely from the index without reading the table. In PostgreSQL, add non-key columns with the INCLUDE clause.