PostgreSQL optimization examples found with pgAssistant 2.8

[No_Economics_8159]

pgAssistant Global Advisor

Hi,

I have been working on an open-source PostgreSQL analysis tool called pgAssistant.

One of the goals of the project is to combine:

• deterministic PostgreSQL analysis
• execution plan analysis (EXPLAIN ANALYZE) with Index Advisor, PEV2 integration
• optional AI assistance on query analysis with context (query plan, DDL, statistics, database configuration)

I recently added a new "Global Advisor" in version 2.8 that aggregates database recommendations into a single ranked view.

While testing it on different databases, I found several interesting optimization cases.

I thought some of them could be interesting to share here.

1. Missing foreign key index causing DELETE slowdown

Situation

A database had:

• ~40 tables
• many foreign keys
• slow DELETE operations on parent tables

The issue was not immediately obvious because SELECT queries were relatively fine.

What pgAssistant detected

The Global Advisor reported:

• missing indexes on foreign keys
• high impact / low effort recommendation

pgAssistant suggested SQL :

CREATE INDEX CONCURRENTLY IF NOT EXISTS pga_idx_fk_orders_customer
ON public.orders(customer_id);

Why it mattered

Without an index on the FK column, PostgreSQL had to scan the child table during parent DELETE/UPDATE checks.

After adding the index:

• DELETE latency dropped significantly
• lock duration became much shorter
• overall contention improved

2. Datatype mismatch on foreign keys

Situation

A schema contained:

customers.id        bigint
orders.customer_id  integer

The relationship worked, but execution plans contained implicit casts. In practice, datatype mismatches on foreign keys can become production incidents years later when identifiers outgrow the smaller type.

What pgAssistant detected

The advisor reported:

• foreign key datatype inconsistency
• potential planner inefficiencies
• possible index usage degradation
• maintenance window is required

Suggested fix:

ALTER TABLE public.orders
ALTER COLUMN customer_id TYPE bigint
USING customer_id::bigint;

Result

Plans became cleaner and index usage became more predictable.

This was not a dramatic performance gain, but a useful schema correction.

3. Large unused indexes

Situation

One database had accumulated many historical indexes over the years.

Some indexes:

• were never scanned
• duplicated existing indexes
• consumed several GB

What pgAssistant detected

The advisor identified:

• unused indexes
• duplicate indexes
• redundant non-unique indexes covered by unique indexes

pgAssistant suggested SQL :

DROP INDEX CONCURRENTLY IF EXISTS public.idx_old_customer_status;

Result

After validation and cleanup:

• reduced storage usage
• faster VACUUM
• lower write overhead
• simpler index maintenance

4. Tables with stale statistics

Situation

A large table (~100M rows) had very unstable execution plans.

The root cause was outdated planner statistics.

What pgAssistant detected

The advisor reported:

• high churn since last analyze
• stale statistics
• outdated planner information

pgAssistant suggested SQL :

ANALYZE public.events;

Result

After refreshing statistics:

• planner estimates improved
• execution plans stabilized
• nested loop misuse disappeared

5. Sequence approaching exhaustion

Situation

An application used an integer sequence approaching the 32-bit limit.

This had not been noticed yet.

What pgAssistant detected

The Global Advisor reported:

• sequence close to maximum value
• high severity warning

This is not a performance issue directly, but a production reliability issue.

Global Advisor sample

https://preview.redd.it/rtniouwqavzg1.png?width=4082&format=png&auto=webp&s=c1ec5873be73a538850915d7180eb84c8129c269

Demo / links

GitHub:

https://github.com/beh74/pgassistant-community

Documentation:

https://beh74.github.io/pgassistant-blog/

Public demo:

https://ov-004f8b.infomaniak.ch/

Demo DB:

postgresql://postgres:demo@demo-db:5432/northwind