What happens after Eloquent sends a query to PostgreSQL?

PostgreSQL parses it, plans it by choosing a lowest-cost strategy, then executes the chosen plan.

What is an execution plan?

It is PostgreSQL’s step-by-step strategy describing how it will retrieve and process the data.

How do I view the SQL behind an Eloquent query?

Call toSql() and getBindings(), then interpolate the bindings into the SQL string.

What does Seq Scan mean?

A sequential scan reads the entire table row by row and applies filters; it indicates no usable index was chosen.

When is a Seq Scan acceptable?

On small tables or when the filter would match a large percentage of rows, making an index unhelpful.

How do I get PostgreSQL to use an index?

Create a selective index on the filtered column and ensure the query’s condition matches the indexable pattern.

What do cost=0.00..27058.64 numbers represent?

Startup and total estimated work units (not time) used for comparing alternative plans.

What do rows and width indicate in EXPLAIN output?

rows is the estimated output row count for the node; width is the average estimated byte size per row.

What Happens When You Run ->get()? Reading PostgreSQL Execution Plans (Laravel + PostgreSQL Performance Part 2)

We've established that every elegant Eloquent chain, like Post::where('is_published', true)->get(), gets translated into raw SQL. But what happens after Laravel sends that SQL string to PostgreSQL? It's not magic; it's a well-defined, three-step process.

Step 1: Compilation (The Parser) ✍️

First, PostgreSQL receives the SQL query as a text string. Its internal parser checks the string for correct syntax and translates it into an internal, high-level representation called a "logical plan." Think of this as the database understanding what you're asking for, but not yet how it's going to get it. It identifies the tables, columns, and conditions involved.

Step 2: Optimization (The Planner) 🗺️

This is where the real intelligence lies. The component called the "query planner" (or optimizer) takes the logical plan and figures out the most efficient way to execute it.

It asks questions like:

"Should I read the entire posts table from start to finish?" (a Sequential Scan)
"Is there an index on the is_published column I can use to find the matching rows directly?" (an Index Scan)
"If there's a join, what's the best order to connect the tables?"

The planner considers many possible strategies, estimates the "cost" of each one (based on CPU cycles and disk I/O), and chooses the plan with the lowest estimated cost.

The final output of this step is the Execution Plan. This is the crucial document we need to learn how to read. It's the database's turn-by-turn navigation for retrieving your data.

Step 3: Execution (The Executor) 🏃‍♂️

Finally, the "executor" takes the chosen execution plan and follows its instructions precisely, step-by-step, to fetch the data from the disk, perform any necessary operations (like sorting or joining), and return the final result set back to your Laravel application.

How to See the Execution Plan in Your Laravel App

Okay, theory is great, but let's get practical. How do we see this famous execution plan for our own queries? It's a two-step dance:

Get the Raw SQL from Eloquent.
Ask PostgreSQL to EXPLAIN it.

Let's use a simple query as an example:

// Our Eloquent query in Laravel
$postsQuery = App\Models\Post::where('status', 'published');

Step 1: Get the SQL

You can use the toSql() method to see the generated SQL. Remember to also get the bindings!

// In Tinker or your controller
$sql = $postsQuery->toSql();
$bindings = $postsQuery->getBindings();
 
// Manually replace the '?' placeholders
$fullSql = vsprintf(str_replace('?', '%s', $sql), array_map(function ($binding) {
    return is_numeric($binding) ? $binding : "'" . $binding . "'";
}, $bindings));
 
echo $fullSql;
// Outputs: select * from "posts" where "status" = 'published'

Step 2: Get the Plan

Now, take that raw SQL and head over to your favorite database client (like TablePlus, DBeaver, or even just the psql command line). Prefix your query with the EXPLAIN keyword and run it:

EXPLAIN SELECT * FROM "posts" WHERE "status" = 'published';

PostgreSQL won't actually run the query; instead, it will return the execution plan it would use. It might look something like this:

                          QUERY PLAN
    --------------------------------------------------------------
     Seq Scan on posts  (cost=0.00..27058.64 rows=227725 width=71)
       Filter: (status = 'published'::text)
    (2 rows)

Decoding Your First Execution Plan

Let's break that down. You read an execution plan from the most indented line outwards.

Seq Scan on posts: This is the core action. It means "Sequential Scan." PostgreSQL is telling us its plan is to read the posts table from beginning to end, one row at a time.
Filter: (status = 'published'::text): While it's scanning each row, it will apply this filter to see if the status matches.
(cost=0.00..27058.64 rows=227725 width=71): This is the planner's estimate.
- cost: The first number is the startup cost (to get the first row), and the second is the total cost to get all rows. Think of this as an arbitrary unit of work. Lower is better.
- rows: The estimated number of rows that will be returned by this step.
- width: The estimated average size of each row in bytes.

This Seq Scan tells us something very important: PostgreSQL did not use an index for this query. It had to read the entire table. For a small table, that's fine. For a table with millions of posts, that's a huge performance problem waiting to happen.

Now that you know how to see the database's plan, you're ready to start influencing it.

In our next article, we'll focus on optimizing these common "short queries" and learn how to turn that Seq Scan into a much faster Index Scan. Ready for the next step?