---

name: postgres-optimization description: Unconventional PostgreSQL optimization techniques license: MIT tier: 2 allowed-tools:

• read_file
• write_file
• run_terminal_cmd
• grep related: [debugging, plan-then-execute, robust-first] tags: [moollm, database, postgresql, performance, optimization, indexing] inputs: query: type: string required: false description: "Query to optimize" table: type: string required: false description: "Table to analyze" outputs:
• OPTIMIZATION.md
• EXPLAIN-ANALYSIS.txt credits: source: title: "Unconventional PostgreSQL Optimizations" author: "Haki Benita" url: "https://hakibenita.com/postgresql-unconventional-optimizations"

---

🐘 PostgreSQL Optimization

"Beyond 'just add an index' — creative solutions for real performance problems."

Unconventional optimization techniques for PostgreSQL that go beyond standard DBA playbooks.

Purpose

When conventional approaches fall short — query rewrites, adding indexes, VACUUM, ANALYZE — these techniques offer creative solutions:

• Eliminate impossible query scans with constraint exclusion
• Reduce index size with function-based indexes
• Enforce uniqueness with hash indexes instead of B-Trees

When to Use

• Ad-hoc query environments where users make mistakes
• Large indexes approaching table size
• Uniqueness constraints on large text values (URLs, documents)
• Timestamp columns queried at coarser granularity

---

Technique 1: Constraint Exclusion

The Problem

Check constraints prevent invalid data, but PostgreSQL doesn't use them to optimize queries by default.

CopyCREATE TABLE users (
    id INT PRIMARY KEY,
    username TEXT NOT NULL,
    plan TEXT NOT NULL,
    CONSTRAINT plan_check CHECK (plan IN ('free', 'pro'))
);

An analyst writes:

CopySELECT * FROM users WHERE plan = 'Pro';  -- Note: capital P

Despite the check constraint making this condition impossible, PostgreSQL scans the entire table.

The Solution

CopySET constraint_exclusion TO 'on';

With constraint exclusion enabled:

CopyEXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';

CopyResult  (cost=0.00..0.00 rows=0 width=0)
  One-Time Filter: false
Execution Time: 0.008 ms

PostgreSQL recognizes the condition contradicts the constraint and skips the scan entirely.

When to Enable

Tradeoffs

• Benefit: Eliminates impossible query scans
• Cost: Extra planning overhead evaluating constraints against conditions
• Default: 'partition' — only used for partition pruning

---

Technique 2: Function-Based Indexes for Lower Cardinality

The Problem

You have a sales table with timestamps:

CopyCREATE TABLE sale (
    id INT PRIMARY KEY,
    sold_at TIMESTAMPTZ NOT NULL,
    charged INT NOT NULL
);

Analysts query by day:

CopySELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged)
FROM sale
WHERE sold_at BETWEEN '2025-01-01 UTC' AND '2025-02-01 UTC'
GROUP BY 1;

You add a B-Tree index on sold_at — 214 MB for a 160 MB table. The index is almost half the table size!

The Solution

Index only what queries need:

CopyCREATE INDEX sale_sold_at_date_ix 
ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date);

The function-based index is 3x smaller because:

• Dates are 4 bytes vs 8 bytes for timestamptz
• Fewer distinct values enable deduplication

The Discipline Problem

Function-based indexes require exact expression match:

Copy-- Uses the index ✓
WHERE date_trunc('day', sold_at AT TIME ZONE 'UTC')::date 
      BETWEEN '2025-01-01' AND '2025-01-31'

-- Does NOT use the index ✗
WHERE (sold_at AT TIME ZONE 'UTC')::date 
      BETWEEN '2025-01-01' AND '2025-01-31'

Solution: Virtual Generated Columns (PostgreSQL 18+)

CopyALTER TABLE sale ADD sold_at_date DATE
GENERATED ALWAYS AS (date_trunc('day', sold_at AT TIME ZONE 'UTC'));

Now queries use the virtual column:

CopySELECT sold_at_date, SUM(charged)
FROM sale
WHERE sold_at_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1;

Benefits:

• Smaller index
• Faster queries
• No discipline required — column guarantees correct expression
• No ambiguity about timezones

Limitation: PostgreSQL 18 doesn't support indexes directly on virtual columns (yet).

---

Technique 3: Hash Index for Uniqueness

The Problem

You have a table with large URLs:

CopyCREATE TABLE urls (
    id INT PRIMARY KEY,
    url TEXT NOT NULL,
    data JSON
);

You add a unique B-Tree index:

CopyCREATE UNIQUE INDEX urls_url_unique_ix ON urls(url);

The index is almost as large as the table because B-Tree stores actual values in leaf blocks.

The Solution

Use an exclusion constraint with a hash index:

CopyALTER TABLE urls 
ADD CONSTRAINT urls_url_unique_hash 
EXCLUDE USING HASH (url WITH =);

The hash index is 5x smaller because it stores hash values, not the actual URLs.

Uniqueness Is Enforced

CopyINSERT INTO urls (id, url) VALUES (1000002, 'https://example.com');
-- ERROR: conflicting key value violates exclusion constraint

Queries Still Fast

CopyEXPLAIN ANALYZE SELECT * FROM urls WHERE url = 'https://example.com';

CopyIndex Scan using urls_url_unique_hash on urls
Execution Time: 0.022 ms  -- Faster than B-Tree's 0.046 ms!

Limitations

Workaround: Use MERGE

Instead of INSERT ... ON CONFLICT DO UPDATE:

CopyMERGE INTO urls t
USING (VALUES (1000004, 'https://example.com')) AS s(id, url)
ON t.url = s.url
WHEN MATCHED THEN UPDATE SET id = s.id
WHEN NOT MATCHED THEN INSERT (id, url) VALUES (s.id, s.url);

---

Quick Reference

Diagnostic Queries

Check index sizes:

Copy\di+ table_*

Compare index to table size:

CopySELECT 
    relname AS name,
    pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class 
WHERE relname LIKE 'your_table%'
ORDER BY pg_relation_size(oid) DESC;

Check constraint_exclusion setting:

CopySHOW constraint_exclusion;

Decision Tree

CopyIs the query scanning impossibly?
├── Yes → Enable constraint_exclusion
└── No
    ↓
Is index nearly as large as table?
├── Yes, timestamp column → Function-based index on date
├── Yes, large text column → Hash exclusion constraint
└── No → Standard B-Tree is fine

---

Commands

---

Integration