How Query Insights Work

PGVitals automatically analyzes every query your agent collects and flags potential issues using the rules below. These are checked on every snapshot — no configuration needed.

Rule-Based Analysis

Every query is evaluated against these rules. If multiple rules match, all insights are shown. If none match, the query gets a green ✅ ok badge.

Tag	Triggers When	What It Means
🐢 slow	Avg execution time > 100ms (warning) or > 500ms (critical)	Query is taking too long. Likely missing an index or scanning too many rows.
⚡ spike	Max execution time > 3x the average, and max > 50ms	Occasional extreme slowdowns — could indicate lock contention, autovacuum interference, or resource pressure.
📉 unstable	Standard deviation > mean execution time	Performance is highly inconsistent. May be caused by cache misses, varying data sizes, or concurrent load.
🔥 hot	Called more than 10,000 times	This is a hot-path query. Even a small per-call improvement will compound into big savings.
🔄 frequent	Called more than 1,000 times	Query runs often — consider caching results if the underlying data doesn't change frequently.
💾 cache-miss	Buffer cache hit ratio < 90% and > 100 blocks read from disk	Too much data is being read from disk instead of shared buffers. An index could help, or increase shared_buffers.
✳️ select-star	Query uses SELECT *	Fetching all columns wastes I/O and memory. Specify only the columns you need.
🎲 random-sort	Query uses ORDER BY RANDOM()	Forces a full table scan and sort — extremely expensive on large tables. Use a sampling strategy instead.
🔍 leading-wildcard	LIKE with a leading % wildcard	Prevents index usage. Consider using full-text search (tsvector/tsquery) for better performance.
♻️ no-limit	No LIMIT clause and > 1,000 rows returned	Returning large result sets without pagination increases memory usage and network transfer.
✅ ok	No rules triggered	Query is performing well. No action needed.

Data Sources

All metrics come directly from Postgres system views collected by the PGVitals agent:

pg_stat_statements — per-query execution stats (calls, time, rows)
pg_stat_user_tables — table-level sequential scan and tuple counts
pg_stat_user_indexes — index usage statistics

Query Summary

Each query also gets a human-readable summary generated from the SQL text:

SELECT ... FROM orders WHERE ...→Read from orders (filtered)

INSERT INTO events ...→Insert from events

SELECT ... FROM users JOIN orders ... GROUP BY ...→Read from users (with join) (filtered) (aggregated)

CREATE INDEX ...→DDL: CREATE INDEX ...

AI Explain Growth & Scale

On Growth and Scale plans, you can click Explain with AI on any query to get a Claude-powered analysis. This gives you a plain-English explanation of what the query does, why it might be slow, and a specific optimization suggestion tailored to your query and its stats.