Summary#

PostgreSQL query optimization fundamentals

Problem#

Slow PostgreSQL queries due to missing indexes, poor join strategy, or planner-hostile patterns (wildcard-leading LIKE, functions in WHERE, unnecessary DISTINCT/UNION).

Solution#

Index selectively — avoid indexes on columns where >30% of rows are returned (seq scan wins there). Pre-aggregate with CTEs before joining. Use INNER JOIN over LEFT JOIN where nulls aren't needed. Avoid LIKE '%term', functions on indexed columns in predicates, and SELECT *.

Failure Modes#

• Adding indexes on low-cardinality or high-selectivity columns that the planner ignores anyway.
• CTE optimization fence in older Postgres versions (pre-12) materializing CTEs unnecessarily — use subqueries or WITH ... AS NOT MATERIALIZED instead.
• ORDER BY on large result sets without a supporting index causing expensive sort steps.

Sources#

• https://stackoverflow.com/questions/72084607/what-are-the-best-practices-for-testing-a-fastapi-project-with-jwt-authenticatio
• https://stackoverflow.com/questions/63918342/refresh-token-using-fastapi-and-swagger
• https://stackoverflow.com/questions/53949393/cant-perform-a-react-state-update-on-an-unmounted-component
• https://stackoverflow.com/questions/57960894/kubernetes-deployment-failure-how-to-inspect
• https://stackoverflow.com/questions/54107445/kubernetes-deployment-failing-due-to-image-pull-failure
• https://stackoverflow.com/questions/47536536/whats-the-difference-between-docker-compose-and-kubernetes
• https://stackoverflow.com/questions/37685581/how-to-get-docker-compose-to-use-the-latest-image-from-the-repository
• https://stackoverflow.com/questions/37521440/is-docker-compose-suitable-for-production
• https://stackoverflow.com/questions/60856050/disable-postgresql-query-optimization
• https://stackoverflow.com/questions/27409413/query-optimization-for-postgresql
• https://stackoverflow.com/questions/59157009/query-optimization-how-to-reduce-planning-time-and-execution-time-in-postgresql
• mined_at: 2026-09-13T07:07:16Z

Sagwan Revalidation 2026-09-13T07:43:42Z#

• verdict: refresh
• note: 소스 URL 전체가 FastAPI·React·Kubernetes 관련 링크로, PostgreSQL 내용과 무관 — 출처 오염으로 캡슐 신뢰도 훼손.