Partial & Covering Indexes in Laravel + PostgreSQL | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. PostgreSQL Partial and Covering Indexes in Laravel Migrations and Query Plans PostgreSQL Partial and Covering Indexes in Laravel Migrations and Query Plans ============================================================================== Learn how to define partial and covering indexes in Laravel migrations, read EXPLAIN ANALYZE output, and write Eloquent queries that actually use them — with concrete examples. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 6 Jul 2026 · Updated 6 Jul 2026 · 4 min read ShareCopy linkCopied ![PostgreSQL Partial and Covering Indexes in Laravel Migrations and Query Plans](https://cdn.msaied.com/373/a6b1671af111801768bc65898128a6e4.png) On this page +1. [Why Generic Indexes Leave Performance on the Table](#why-generic-indexes-leave-performance-on-the-table) 2. [Partial Indexes: Index Only What You Query](#partial-indexes-index-only-what-you-query) 3. [Covering Indexes: Eliminate Heap Fetches](#covering-indexes-eliminate-heap-fetches) 4. [Reading EXPLAIN ANALYZE Output](#reading-explain-analyze-output) 5. [Combining Both: Partial Covering Index](#combining-both-partial-covering-index) 6. [Practical Takeaways](#practical-takeaways) Why Generic Indexes Leave Performance on the Table -------------------------------------------------- Most Laravel applications add indexes reactively — a slow query appears, someone runs `Schema::table('orders', fn($t) => $t->index('status'))`, and the problem is patched. That works until the table grows and the planner decides a sequential scan is cheaper because the index covers too many rows. Two PostgreSQL features fix this at the schema level: **partial indexes** (index only the rows you actually query) and **covering indexes** (embed payload columns so the planner never touches the heap). Neither requires a custom database driver — just raw index definitions in your migrations. --- Partial Indexes: Index Only What You Query ------------------------------------------ A partial index carries a `WHERE` clause. If your application only ever queries `orders` where `status = 'pending'`, index only those rows. ```php // database/migrations/2024_06_01_000001_add_partial_index_to_orders.php public function up(): void { DB::statement( "CREATE INDEX idx_orders_pending_created ON orders (created_at DESC) WHERE status = 'pending'" ); } public function down(): void { DB::statement('DROP INDEX IF EXISTS idx_orders_pending_created'); } ``` The index is tiny — it only contains rows where `status = 'pending'`. The planner will use it when your query includes the same predicate: ```php // Planner sees the WHERE clause matches the partial index condition Order::where('status', 'pending') ->orderByDesc('created_at') ->limit(50) ->get(); ``` If you omit `->where('status', 'pending')`, PostgreSQL will not use this index. That is intentional — the index is meaningless for other statuses. --- Covering Indexes: Eliminate Heap Fetches ---------------------------------------- A covering index uses `INCLUDE` to attach non-key columns. The planner can satisfy the entire query from the index leaf pages without a heap fetch (an **Index Only Scan**). ```php DB::statement( "CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (id, name, created_at)" ); ``` Now this query never touches the `users` heap: ```php User::select('id', 'name', 'created_at') ->where('email', $email) ->first(); ``` The `INCLUDE` columns are not part of the B-tree key, so they add no ordering overhead and do not bloat the index structure as aggressively as adding them to the key would. --- Reading EXPLAIN ANALYZE Output ------------------------------ Always verify with `EXPLAIN (ANALYZE, BUFFERS)`: ```php $sql = User::select('id', 'name', 'created_at') ->where('email', 'alice@example.com') ->toRawSql(); $plan = DB::select("EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) {$sql}"); foreach ($plan as $row) { echo $row->{'QUERY PLAN'} . "\n"; } ``` Key lines to look for: - **Index Only Scan** — the covering index is working; heap fetches are zero. - **Index Scan** — the index is used but heap rows are still fetched (add `INCLUDE` columns). - **Bitmap Heap Scan** — common for range queries; acceptable but check `Heap Blocks: exact=0` for visibility map wins. - **Seq Scan** — the planner chose a full scan; check row estimates and `work_mem`. ```yaml Index Only Scan using idx_users_email_covering on users (cost=0.43..8.45 rows=1 width=52) (actual time=0.021..0.022 rows=1 loops=1) Index Cond: (email = 'alice@example.com') Heap Fetches: 0 ``` `Heap Fetches: 0` confirms the visibility map is current and no heap I/O occurred. --- Combining Both: Partial Covering Index -------------------------------------- You can combine both techniques: ```php DB::statement( "CREATE INDEX idx_invoices_unpaid_covering ON invoices (due_date ASC) INCLUDE (id, customer_id, total_cents) WHERE paid_at IS NULL" ); ``` This index is small (only unpaid invoices), ordered for due-date queries, and carries the columns needed for a dashboard list — all without touching the heap. --- Practical Takeaways ------------------- - Use partial indexes when a query always filters on a low-cardinality column with a fixed value (`status`, `type`, boolean flags). - Use `INCLUDE` to turn an Index Scan into an Index Only Scan for read-heavy list queries. - Combine both for high-traffic, narrow-predicate queries like dashboards or queue polling. - Always confirm with `EXPLAIN (ANALYZE, BUFFERS)` — the planner can surprise you. - Run `VACUUM ANALYZE` after bulk loads; stale statistics cause the planner to ignore valid indexes. - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [laravel](https://www.msaied.com/public/articles?search=laravel) - [performance](https://www.msaied.com/public/articles?search=performance) - [database](https://www.msaied.com/public/articles?search=database) Frequently asked questions --------------------------- Will Laravel's Schema builder create partial or covering indexes natively?Not yet. The Blueprint API has no `partial()` or `include()` methods. You must use `DB::statement()` with raw DDL inside your migration's `up()` and `down()` methods, which is perfectly safe and version-controlled. When should I prefer a partial index over a composite index?Use a partial index when one column in your composite key is always a fixed value in queries (e.g., `status = 'pending'`). Moving that fixed predicate into the index `WHERE` clause shrinks the index significantly and can make the difference between an index scan and a sequential scan on large tables. Does INCLUDE affect write performance?Yes, but less than adding columns to the index key. INCLUDE columns are stored only in leaf pages, not in internal B-tree nodes, so the structural overhead is lower. For write-heavy tables, benchmark with realistic insert/update loads before committing. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleMacros, Mixins, and Custom Collection Methods in Laravel](https://www.msaied.com/public/articles/macros-mixins-and-custom-collection-methods-in-laravel-1) [Next articleLivewire v3 Islands, Lazy Components, and Deferred Loading in Practice](https://www.msaied.com/public/articles/livewire-v3-islands-lazy-components-and-deferred-loading-in-practice-2) On this page ------------- 1. [Why Generic Indexes Leave Performance on the Table](#why-generic-indexes-leave-performance-on-the-table) 2. [Partial Indexes: Index Only What You Query](#partial-indexes-index-only-what-you-query) 3. [Covering Indexes: Eliminate Heap Fetches](#covering-indexes-eliminate-heap-fetches) 4. [Reading EXPLAIN ANALYZE Output](#reading-explain-analyze-output) 5. [Combining Both: Partial Covering Index](#combining-both-partial-covering-index) 6. [Practical Takeaways](#practical-takeaways) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)