Query Optimization After Migration: The First 30 Days

The Migration Worked. Now the Queries Are Slow.

The data landed, the app connects, validation passed — and the first thing the team notices is that a report that took 2 seconds on Oracle now takes 40 on PostgreSQL. This is normal, expected, and fixable. PostgreSQL's planner is different from Oracle's, and right after a bulk load it's flying blind. The first 30 days are about giving it the information it needs.

Day 1: ANALYZE Everything

The single highest-leverage action post-migration. A bulk COPY loads rows but leaves the planner with no statistics — so it guesses, and it guesses badly (sequential scans on huge tables, wrong join orders). Run ANALYZE across the database before anyone benchmarks anything.

-- Refresh planner statistics for the whole database
ANALYZE VERBOSE;

-- Make sure autovacuum/autoanalyze keeps them fresh afterwards (defaults are usually fine,
-- but high-churn tables benefit from a lower threshold)
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Find the Slow Queries — Don't Guess

Enable pg_stat_statements and let it tell you where the time actually goes. Optimizing by hunch wastes the first week; the extension ranks queries by total time so you fix the real top-10.

-- one-time: add to shared_preload_libraries, then
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- the queries actually costing you time
SELECT calls, round(mean_exec_time::numeric, 1) AS avg_ms,
       round(total_exec_time::numeric, 0) AS total_ms, query
FROM   pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Read the Plan with EXPLAIN (ANALYZE, BUFFERS)

For each slow query, EXPLAIN (ANALYZE, BUFFERS) shows what the planner did versus what it expected. The tells: a Seq Scan on a big table where Oracle used an index (often a missing index or stale stats), or a row-estimate that's off by orders of magnitude (stats or a correlation the planner can't see — fix with CREATE STATISTICS).

Tune the Cost Inputs for Your Hardware

PostgreSQL ships with conservative defaults written for spinning disks. On modern SSD/NVMe the most impactful changes are below — they tell the planner that random I/O is cheap and that it has memory to work with.

-- SSD/NVMe: random reads are nearly as cheap as sequential
ALTER SYSTEM SET random_page_cost = 1.1;

-- Let the planner know the OS cache size (≈ 50–75% of RAM)
ALTER SYSTEM SET effective_cache_size = '24GB';

-- Memory per sort/hash node — raises in-memory sorts (watch total = work_mem × connections)
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

Verify Indexes Are Actually Used

Indexes migrate across, but Oracle-specific ones (bitmap, function-based, reverse-key) become a different shape in PostgreSQL, and some Oracle indexes were workarounds the PG planner doesn't need. Check pg_stat_user_indexes for indexes with zero scans (dead weight slowing writes) and add the indexes the new EXPLAIN plans are crying out for.