How do I decide if a query is short or long?

Short queries return a tiny targeted set (often by key); long queries scan or aggregate a large share of rows for reports.

What is the first step before tuning a slow query?

Extract the exact SQL (toSql or Telescope) and run EXPLAIN to see the real plan instead of guessing.

Why might a short lookup use a Seq Scan instead of an index?

Missing or non-selective index, function-wrapped column in WHERE, mismatched data types, or leading wildcard patterns.

How do I fix the N+1 'shopping list' problem?

Move queries out of loops using eager loading (with), batching (whereIn), or a single JSON-building function for deep graphs.

When should I aggregate before joining?

When early grouping drastically shrinks a huge table, reducing join input size and memory.

What is the benefit of WHERE EXISTS over a JOIN + DISTINCT?

EXISTS stops at the first match (semi-join) instead of generating duplicates that must later be de-duplicated.

When is a sequential scan acceptable?

For long queries needing most rows; forcing an index would add random I/O without reducing work.

Why use a CTE in PostgreSQL 12+?

For readability and stepwise logic; most CTEs are now inlined so performance matches equivalent subqueries.

When should I create a database view?

For complex reusable join/compute logic referenced in multiple endpoints or reports.

What triggers considering table partitioning?

Very large time-series or log tables where pruning, retention deletes, or wide-range scans become slow.

What is the NORM pattern?

A PostgreSQL function assembling a full nested JSON payload in one call to eliminate multiple relationship queries.

How do I know if an index is actually used?

EXPLAIN shows Index Scan or Bitmap Index Scan referencing that index; absence means it was skipped.

Bitmap Index Scan vs Index Scan—difference?

Bitmap collects matching TIDs then batches heap page reads; plain Index Scan visits rows immediately—bitmap wins for many scattered matches.

Why avoid function calls on indexed columns in WHERE?

Wrapping (e.g., DATE(created_at)) prevents using a normal B-tree index; rewrite as a range predicate.

What is the core optimization habit?

Always inspect generated SQL and its plan early instead of adding reactive fixes later.

The Ultimate Laravel + PostgreSQL Query Optimization Checklist (Part 7)

You've learned the theory, seen the execution plans, and explored advanced patterns. Now, let's put it all together. This is your step-by-step guide to follow whenever you're writing a new query or debugging a slow one.

Think of this as the Ultimate Optimization Algorithm from the book, adapted for your Laravel workflow.

Step 1: Identify Your Query Type: Short or Long?

Before you write a single line of optimization code, ask yourself this fundamental question :

Is this a Short Query? Am I fetching a small, specific set of records (often just one)? This includes most day-to-day operations like find(), first(), or a where() clause on a unique key.
Is this a Long Query? Am I processing a large percentage of a table to generate a report, an export, or an aggregate dashboard?

Your answer determines your entire strategy.

Step 2: The Short Query Checklist (The Quick Wins)

If you're dealing with a short query, your goal is to use an index to avoid a Seq Scan.

✅ Get the Raw SQL: Use toSql() or Laravel Telescope to see the exact query being generated.
✅ Run EXPLAIN: Go to your database client and check the execution plan. Do you see an Index Scan or Bitmap Index Scan?
- If YES, you're likely in good shape.
- If NO, and you see a Seq Scan, proceed to the next step.
✅ Add the Index: The most common oversight is a missing index on a foreign key or a frequently searched column. Open the relevant migration and add $table->index('column_name');. Remember, foreignId()->constrained() adds this for you automatically in recent Laravel versions.
✅ Check for Query Transformations: Does your WHERE clause modify the column value, like WHERE DATE(created_at) = '...'? This will prevent a normal index from being used. Rewrite it to be "index-friendly": WHERE created_at >= '...' AND created_at < '...'.
✅ Kill N+1 Loops: Is this query running inside a foreach loop? This is the "Shopping List Problem." Refactor your code to fetch all the data you need before the loop, typically using Eloquent's with() (eager loading) or a collection method like whereIn().

Step 3: The Long Query Checklist (The Marathon Strategy)

If you have a long query, your goal is to make the table scans and joins as efficient as possible.

✅ Filter as Early as Possible: Your most important task is to reduce the size of the datasets before joining them. Use whereExists() (a semi-join) to filter a large table based on a condition in another table without performing a full join.
✅ Group Strategically:
- Filter First, Group Last: For most reports, apply all your WHERE clauses first to shrink the dataset, and then perform your groupBy() and aggregates.
- Group First, Join Last: If you need to aggregate a massive table (e.g., counting all orders per user), do it in a subquery or CTE first, then join the much smaller result set to other tables.
✅ Use CTEs for Readability: If your report involves multiple logical steps, use withExpression() or DB::raw('WITH ...') to structure it as a Common Table Expression. This makes the query much easier to maintain and doesn't hurt performance in modern PostgreSQL.
✅ Avoid Multiple Scans: Does your query hit the same large table multiple times (e.g., joining to an entity-attribute-value table three times to get three attributes)? Rewrite it to scan the table only once and use CASE statements or other logic to pivot the data.

Step 4: The Advanced Scenarios Checklist (The Power Tools)

If you've followed the steps above and still face performance issues, especially with complex, nested data, it's time to bring out the heavy machinery.

✅ Consider a View: If you have a very complex set of joins and calculations that you reuse all over your application, encapsulate that logic in a database View via a migration. Then, create a read-only Eloquent model to interact with it cleanly. This is great for keeping your logic DRY.
✅ Implement the NORM Pattern: If you have an API endpoint that needs to return a deeply nested JSON object and is suffering from the N+1 problem, this is the ultimate solution. Create a PostgreSQL function that builds the entire JSON object in a single query. Call this one function from Laravel and simply decode the result. This transforms hundreds of potential queries into one.
✅ Investigate Partitioning: If you have a table with hundreds of millions of rows (like logs or events) and your queries are timing out, consider partitioning the table by a date range (e.g., by month) using raw SQL in your migrations. This allows the query planner to scan only the relevant partitions, drastically improving performance.

Final Thought: Optimization is a Habit, Not a Task

The most important takeaway from this entire series is to "think like a database." Make looking at query logs in Telescope a regular part of your development process. When you write a new feature, take the extra minute to run EXPLAIN on the queries it generates.

By making these small checks a regular habit, you'll stop thinking of optimization as a painful chore and start seeing it for what it is: a crucial part of writing clean, scalable, and professional Laravel applications. Happy coding!