Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 3 · Lesson 17 · 7 min read

PostgreSQL Query Optimization for SQL Server DBAs

On this page
  1. Common query problems after migration
  2. "The login lookup returns nothing"
  3. "The date filter does a full scan"
  4. "NOT IN suddenly returns zero rows"
  5. "Everything was slow right after the data load"
  6. "The planner ignores my index"
  7. Two more habits worth changing
  8. Check yourself

Common query problems after migration

After a migration, most queries perform about the same. A few get dramatically slower, or start returning different results, and they usually fall into a handful of patterns. This lesson goes through them as symptom, cause and fix.

"The login lookup returns nothing"

Symptom. WHERE email = 'Jane@Example.com' found the user on SQL Server and finds nobody on PostgreSQL.

Cause. Most SQL Server databases use a case-insensitive collation, so = ignores case. PostgreSQL's default collations are case-sensitive. This isn't a performance problem at first, it's a correctness one, and it hides in logins, lookups by code and duplicate checks.

Fix. Pick one approach per column:

SQL
CREATE TABLE customers (customer_id int PRIMARY KEY, email text NOT NULL);
INSERT INTO customers VALUES (1, 'jane@example.com');

-- Option 1: compare in lowercase, with a matching expression index
CREATE INDEX customers_email_lower_idx ON customers (lower(email));
SELECT customer_id FROM customers WHERE lower(email) = lower('Jane@Example.com');
  • Expression index on lower(), as above. Simple and fast, but every query must use lower() on both sides.
  • Store values normalized. Lowercase emails on the way in, for example with a BEFORE trigger (Day 10) or in the application.
  • A case-insensitive collation. PostgreSQL supports nondeterministic ICU collations such as CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false). They behave most like SQL Server, but have limits, for example on pattern matching before PostgreSQL 18. Test them before adopting.
  • The citext extension gives a case-insensitive text type. It's an older approach, but you'll see it in existing systems.

Here's the collation option end to end, tested on PostgreSQL 18:

SQL
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE TABLE users (user_id int PRIMARY KEY, email text COLLATE ci NOT NULL UNIQUE);
INSERT INTO users VALUES (1, 'jane@example.com');

SELECT user_id FROM users WHERE email = 'Jane@Example.COM';   -- 1
INSERT INTO users VALUES (2, 'JANE@example.com');             -- ERROR: duplicate key
SELECT user_id FROM users WHERE email LIKE 'JANE%';           -- 1

Equality, the UNIQUE constraint and LIKE all ignore case, which is how the SQL Server column behaved. The LIKE line is the version catch: on PostgreSQL 16 the same query fails with "nondeterministic collations are not supported for LIKE". If your target runs 17 or older, use the lower() approach for columns searched with patterns.

Find every column where application code relies on case-insensitive matching before cutover. It's one of the most common post-migration bugs.

"The date filter does a full scan"

Symptom. A query filtering on one day reads the whole table.

Cause. The column is wrapped in a function, so the index on the raw column can't be used. It's the same rule as SQL Server's sargability, but the functions have different names. A translated CAST(order_date AS date) = '2026-08-01' or date_trunc('day', order_date) = ... hides the column.

Fix. Rewrite as a range on the bare column:

SQL
-- Instead of: WHERE date_trunc('day', created_at) = '2026-08-01'
SELECT count(*) FROM orders
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02';

If you really need to filter on a computed value often, create an expression index on exactly that expression.

"NOT IN suddenly returns zero rows"

Symptom. WHERE customer_id NOT IN (SELECT customer_id FROM blacklist) returns nothing.

Cause. One blacklist.customer_id is NULL. NOT IN against a list containing NULL is never true, in both databases. Code that was safe because a column was NOT NULL in SQL Server can break if the migrated column lost its constraint.

Fix. Use NOT EXISTS, which has no NULL trap and plans just as well:

SQL
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.customer_id = c.customer_id);

For positive checks, IN and EXISTS usually produce the same plan in PostgreSQL. Write whichever reads more clearly.

"Everything was slow right after the data load"

Symptom. The first reports after migration are badly slow, then mysteriously improve an hour later.

Cause. The tables were bulk-loaded, and the planner's statistics still described empty tables until autovacuum got around to analyzing them. SQL Server updates statistics automatically when a query notices they're stale. PostgreSQL relies on autovacuum's thresholds (Day 18).

Fix. Make ANALYZE the last step of every load:

SQL
ANALYZE;                          -- the whole database
ANALYZE orders;                   -- or one table
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;   -- more detail for a skewed column

In EXPLAIN ANALYZE (Day 15), estimated rows far from actual rows is the sign to look for.

"The planner ignores my index"

Symptom. A new index exists, but EXPLAIN still shows a Seq Scan.

Cause. Often there's nothing wrong. If the query returns a large share of the table, reading it straight through is cheaper than jumping between index and table for every row. SQL Server makes the same decision, but people expect a seek more often there, because a clustered index scan feels different from a heap scan.

Fix. Check the three things that can really be wrong:

  • Statistics: run ANALYZE and look again.
  • random_page_cost: the default of 4 assumes spinning disks. On SSDs, 1.1 is typical and makes index scans look cheaper (Day 20).
  • Types: a comparison between different types, such as a text parameter against an integer column, can prevent index use. Drivers that send every parameter as text are a classic cause.

Two more habits worth changing

Select only the columns you need. SELECT * reads every column and rules out index-only scans, exactly as in SQL Server. Code generated by ORMs often does it, so check the busiest queries.

Page with keys, not OFFSET. OFFSET 100000 LIMIT 50 reads and discards 100,000 rows in PostgreSQL as well. For deep paging, remember the last key and use WHERE order_id > :last_id ORDER BY order_id LIMIT 50.

To compare rewrites in DataGrip, run each variant two or three times so the cache is warm, then compare both timings and plans. The query history panel keeps each run with its duration.

Check yourself

SQL Server's collation was case-insensitive, and PostgreSQL's default is case-sensitive. Normalize emails to lowercase, or compare with lower() backed by an expression index, or use a case-insensitive collation.

The planner uses statistics from before the load until autovacuum analyzes the tables. Run ANALYZE on the loaded tables as the final step of the load job.

Filter on a range of the bare column: created_at >= '2026-08-01' AND created_at < '2026-08-02'. Wrapping the column in a cast forces PostgreSQL to compute it for every row, so the index on created_at can't be used. The range keeps the column untouched and gives the same result. An expression index on (created_at::date) is the alternative if many queries need it.

After a prepared statement runs five times, PostgreSQL may switch to a generic plan that ignores the actual parameter values. For skewed data, that plan can be far worse. Compare with EXPLAIN using the real values, and if needed set plan_cache_mode = force_custom_plan for that workload.

The two planners transform subqueries differently. PostgreSQL turns IN and EXISTS into semi-joins well, but NOT IN can't use an anti-join when NULLs are possible and returns surprising results. Prefer NOT EXISTS, and check the plan for any query that regressed after migration.