SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN

This is one of the most common questions asked by developers writing SQL queries against PostgreSQL. There are multiple ways to structure subqueries or lookups, and PostgreSQL’s optimizer is quite effective at transforming queries for better performance.

Let’s walk through an example using the pgbench schema.

Note: pgbench is a benchmarking tool included with PostgreSQL. You can initialize sample data with:

Update some sample data:

Inclusion Queries

Goal: Find the number of accounts per branch where branch balance is greater than zero.

1. Using IN

2. Using ANY

3. Using EXISTS

4. Using INNER JOIN

PostgreSQL produces the same execution plan for all of these approaches.

This means you can typically choose the syntax you prefer.

Exclusion Queries

Goal: Find accounts per branch excluding branches with positive balances.

1. Using NOT IN

2. Using <> ALL

3. Using NOT EXISTS

4. Using LEFT JOIN

NOT EXISTS and LEFT JOIN produce better execution plans (hash anti-joins), while NOT IN and <> ALL may generate subplans.

Large Subquery Considerations

With small datasets, PostgreSQL optimizes NOT IN well using hashed subplans. But with large subqueries, performance degrades significantly:

This results in expensive materialization and poor performance.

Datatype Conversion Considerations

Different syntax can introduce implicit casts:

This may cast values to text, adding overhead.

Using IN avoids unnecessary casting:

Summary

• PostgreSQL often optimizes different query styles into the same plan
• EXISTS and JOIN are generally safer for exclusion queries
• IN works well for small subqueries but can degrade with large datasets
• Be aware of implicit datatype conversions
• Always validate with EXPLAIN

General approach:

1. Identify required tables
2. Determine joins
3. Minimize rows in joins

Never assume performance based on small datasets—test at scale.

Our white paper “Why Choose PostgreSQL?” explores features, benefits, and migration strategies.

Download PDF