sql-pro

Start by running EXPLAIN ANALYZE to see actual row counts versus estimates and identify sequential scans on large tables. Then add indexes on filtered and joined columns, rewrite correlated subqueries as aggregation joins, and filter rows early before joins.

Look for Seq Scan nodes on large tables, which signal missing indexes, and compare actual rows against estimated rows to detect stale statistics. High buffer read counts versus shared hits indicate data coming from disk rather than cache.

ROW_NUMBER assigns a unique sequential number to every row, while RANK gives tied rows the same rank and skips subsequent positions. DENSE_RANK also ties equal values but produces no gaps in the ranking sequence.

PostgreSQL uses INSERT ... ON CONFLICT (key) DO UPDATE SET with the EXCLUDED pseudo-table for new values. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, referencing new values via VALUES() or a row alias in version 8.0.19 and later.

Use a covering index when a frequent query touches only a small set of columns, so the database can answer entirely from the index without reading the table. In PostgreSQL, add extra columns with the INCLUDE clause to keep the index key narrow.

NOT IN returns no rows when the subquery produces any NULL value, because NULL comparisons evaluate to unknown. Rewrite it as NOT EXISTS with a correlated subquery, which handles NULLs correctly and often performs better.