sql-pro

Start by running EXPLAIN ANALYZE to see actual row counts versus estimates and identify sequential scans on large tables. Then rewrite correlated subqueries as aggregation joins, filter early before joins, and add covering indexes that include all columns the query touches.

Run EXPLAIN with ANALYZE and BUFFERS options, then check for Seq Scan on large tables which signals a missing index, large gaps between estimated and actual rows which means stale statistics, and high buffer read counts which indicate cache misses.

The dialects differ in auto-increment columns (SERIAL vs AUTO_INCREMENT vs IDENTITY), pagination (LIMIT/OFFSET vs OFFSET FETCH), UPSERT (ON CONFLICT vs ON DUPLICATE KEY vs MERGE), and recursive CTEs where SQL Server omits the RECURSIVE keyword. The skill's dialect reference covers these mappings.

Use a covering index for frequent queries when the index includes all columns the query touches, enabling an index-only scan with no table access. In PostgreSQL, add non-key columns with the INCLUDE clause to keep the index lean.

NOT IN fails when the subquery returns NULLs and often performs poorly on large sets. Rewrite it as NOT EXISTS with a correlated subquery, which handles NULLs correctly and lets the optimizer use indexes more effectively.