Partial & Covering Indexes in Laravel Explained | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel On this page 1. [ Why Generic Indexes Are Not Enough ](#why-generic-indexes-are-not-enough) 2. [ Partial Indexes ](#partial-indexes) 3. [ When to Use Partial Indexes ](#when-to-use-partial-indexes) 4. [ Covering Indexes ](#covering-indexes) 5. [ Reading EXPLAIN ANALYZE Output ](#reading-explain-analyze-output) 6. [ Key Lines to Watch ](#key-lines-to-watch) 7. [ Wiring It Into Your Workflow ](#wiring-it-into-your-workflow) 8. [ Takeaways ](#takeaways) ![Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel](https://cdn.msaied.com/310/02c82307092f4dfe5b8849156da17ab6.png) #laravel #postgresql #performance #database Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel ======================================================================= 28 Jun 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Generic Indexes Are Not Enough ](#why-generic-indexes-are-not-enough) 2. [ 02 Partial Indexes ](#partial-indexes) 3. [ 03 When to Use Partial Indexes ](#when-to-use-partial-indexes) 4. [ 04 Covering Indexes ](#covering-indexes) 5. [ 05 Reading EXPLAIN ANALYZE Output ](#reading-explain-analyze-output) 6. [ 06 Key Lines to Watch ](#key-lines-to-watch) 7. [ 07 Wiring It Into Your Workflow ](#wiring-it-into-your-workflow) 8. [ 08 Takeaways ](#takeaways) Why Generic Indexes Are Not Enough ---------------------------------- Adding `->index()` to a migration column is the first instinct when a query is slow. It works — until the table has millions of rows and your query filters on a narrow condition like `status = 'pending'`. A full B-tree index on `status` stores every row, including the 98% that are `completed`. The planner may skip it entirely and fall back to a sequential scan. Two index types fix this elegantly: **partial indexes** (index only the rows you care about) and **covering indexes** (include all columns the query needs so the engine never touches the heap). --- Partial Indexes --------------- A partial index carries a `WHERE` clause. Only rows satisfying that predicate are indexed. ```php // database/migrations/2024_01_01_000000_add_partial_index_to_jobs_table.php public function up(): void { DB::statement( 'CREATE INDEX idx_jobs_pending_created ON jobs (created_at) WHERE status = \'pending\'' ); } public function down(): void { DB::statement('DROP INDEX IF EXISTS idx_jobs_pending_created'); } ``` Now this Eloquent query hits only the small pending slice: ```php $jobs = Job::where('status', 'pending') ->orderBy('created_at') ->limit(50) ->get(); ``` The index is smaller, fits in cache more readily, and the planner chooses it confidently because the predicate matches exactly. ### When to Use Partial Indexes - Soft-deleted tables: index only `deleted_at IS NULL` rows. - Queue-style tables: index only `status IN ('pending', 'processing')`. - Feature flags: index only `is_active = true` users. --- Covering Indexes ---------------- Even with a good index, PostgreSQL performs a **heap fetch** for every matched row to retrieve columns not stored in the index. A covering index eliminates that with `INCLUDE`. ```php DB::statement( 'CREATE INDEX idx_orders_user_status_covering ON orders (user_id, status) INCLUDE (total_cents, created_at)' ); ``` A query that selects only those four columns now triggers an **Index Only Scan** — zero heap access: ```php $summary = Order::where('user_id', $userId) ->where('status', 'completed') ->select(['user_id', 'status', 'total_cents', 'created_at']) ->get(); ``` The `INCLUDE` columns are not part of the B-tree key, so they add minimal overhead to writes while making reads dramatically cheaper. --- Reading EXPLAIN ANALYZE Output ------------------------------ Never guess — verify. Laravel makes it easy to dump the raw plan: ```php $sql = Order::where('user_id', 1) ->where('status', 'completed') ->select(['user_id', 'status', 'total_cents', 'created_at']) ->toRawSql(); $plan = DB::select('EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) ' . $sql); foreach ($plan as $row) { echo $row->{'QUERY PLAN'} . "\n"; } ``` ### Key Lines to Watch | Term | Good sign | Bad sign | |---|---|---| | `Index Only Scan` | Covering index hit | — | | `Index Scan` | Index used, heap fetched | Many heap fetches | | `Seq Scan` | Small table | Large table | | `Heap Fetches` | 0 | > 0 on covering index | | `actual rows` vs `rows` | Close match | Large divergence = stale stats | If `Heap Fetches` is non-zero after adding a covering index, run `VACUUM ANALYZE orders;` — the visibility map may be stale. --- Wiring It Into Your Workflow ---------------------------- Add a `DB::listen` hook in a local `AppServiceProvider` to log slow queries during development: ```php if (app()->isLocal()) { DB::listen(function (QueryExecuted $query) { if ($query->time > 100) { logger()->warning('Slow query', [ 'sql' => $query->sql, 'ms' => $query->time, ]); } }); } ``` Pair this with `pg_stat_statements` in staging to surface the real worst offenders before they reach production. --- Takeaways --------- - **Partial indexes** shrink index size by scoping to a predicate — ideal for status columns and soft deletes. - **Covering indexes** with `INCLUDE` enable Index Only Scans, eliminating heap fetches entirely. - Always verify with `EXPLAIN (ANALYZE, BUFFERS)` — never assume an index is used. - Stale visibility maps cause unexpected heap fetches; run `VACUUM ANALYZE` after bulk writes. - Keep `select()` explicit in Eloquent so the planner can match covering indexes reliably. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpartial-indexes-covering-indexes-and-query-plan-analysis-in-laravel&text=Partial+Indexes%2C+Covering+Indexes%2C+and+Query+Plan+Analysis+in+Laravel) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpartial-indexes-covering-indexes-and-query-plan-analysis-in-laravel) Frequently Asked Questions ---------------------------- 3 questions Q01 Can I define partial indexes in standard Laravel migrations without raw SQL? Not with the Schema builder alone — it has no native partial index API. Use DB::statement() inside up() and down() to issue raw CREATE INDEX and DROP INDEX DDL. This is idiomatic and fully reversible. Q02 Does a covering index slow down INSERT and UPDATE operations? Slightly, because the INCLUDE columns must be written to the index leaf pages. In practice the overhead is small compared to the read gains, especially on read-heavy tables like orders or audit logs. Q03 How do I know if PostgreSQL is actually using my new index? Run EXPLAIN (ANALYZE, BUFFERS) on the exact query. Look for 'Index Only Scan' or 'Index Scan' on your index name. If you see 'Seq Scan', check that your query predicate matches the index predicate and that table statistics are current. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Laravel 13: New Features, Helpers, and Practical Upgrade Notes](https://cdn.msaied.com/339/58c4fa6fe9b6d25a2dac17c621b6f4c6.png) laravel laravel-13 upgrade ### Laravel 13: New Features, Helpers, and Practical Upgrade Notes Laravel 13 ships with async-first defaults, a leaner bootstrapping layer, and several quality-of-life helpers.... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 1 Jul 2026 3 min read Read ](https://msaied.com/articles/laravel-13-new-features-helpers-and-practical-upgrade-notes) [ ![Laravel 12: Structured Route Files, Slim Skeletons, and the New Application Bootstrapping](https://cdn.msaied.com/337/05b39d16d0f88a5fb94d0cf74049b88b.png) laravel laravel-12 upgrade ### Laravel 12: Structured Route Files, Slim Skeletons, and the New Application Bootstrapping Laravel 12 ships with a leaner skeleton, first-class route file organisation, and a revised application bootst... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 1 Jul 2026 3 min read Read ](https://msaied.com/articles/laravel-12-structured-route-files-slim-skeletons-and-the-new-application-bootstrapping) [ ![Laravel API Resources: Sparse Fieldsets, Conditional Relationships, and Versioning](https://cdn.msaied.com/336/89d518450335e8fcdaa5be882cf4dd3e.png) laravel api resources ### Laravel API Resources: Sparse Fieldsets, Conditional Relationships, and Versioning Go beyond basic API resources. Learn how to implement sparse fieldsets, conditionally load relationships, and... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 1 Jul 2026 3 min read Read ](https://msaied.com/articles/laravel-api-resources-sparse-fieldsets-conditional-relationships-and-versioning) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)