PostgreSQL Partial & Covering Indexes for Laravel | 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) PostgreSQL Partial, Covering, and Expression Indexes for Laravel Query Tuning On this page 1. [ Beyond Basic Indexes: PostgreSQL's Hidden Performance Levers ](#beyond-basic-indexes-postgresqls-hidden-performance-levers) 2. [ Partial Indexes: Index Only What You Query ](#partial-indexes-index-only-what-you-query) 3. [ Covering Indexes: Eliminate the Heap Fetch ](#covering-indexes-eliminate-the-heap-fetch) 4. [ Expression Indexes: Index the Computation, Not the Column ](#expression-indexes-index-the-computation-not-the-column) 5. [ Combining All Three ](#combining-all-three) 6. [ Key Takeaways ](#key-takeaways) ![PostgreSQL Partial, Covering, and Expression Indexes for Laravel Query Tuning](https://cdn.msaied.com/443/0a5c76bb2c3443c7912b77d66353f647.png) #postgresql #laravel #performance #database #indexing PostgreSQL Partial, Covering, and Expression Indexes for Laravel Query Tuning =============================================================================== 19 Jul 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Beyond Basic Indexes: PostgreSQL's Hidden Performance Levers ](#beyond-basic-indexes-postgresqls-hidden-performance-levers) 2. [ 02 Partial Indexes: Index Only What You Query ](#partial-indexes-index-only-what-you-query) 3. [ 03 Covering Indexes: Eliminate the Heap Fetch ](#covering-indexes-eliminate-the-heap-fetch) 4. [ 04 Expression Indexes: Index the Computation, Not the Column ](#expression-indexes-index-the-computation-not-the-column) 5. [ 05 Combining All Three ](#combining-all-three) 6. [ 06 Key Takeaways ](#key-takeaways) Beyond Basic Indexes: PostgreSQL's Hidden Performance Levers ------------------------------------------------------------ Most Laravel developers reach for `$table->index(['status', 'created_at'])` and call it a day. That works — until your `orders` table hits 50 million rows and your dashboard query still does a sequential scan. PostgreSQL offers three index types that most teams underuse: **partial**, **covering**, and **expression** indexes. Each solves a distinct problem. --- Partial Indexes: Index Only What You Query ------------------------------------------ A partial index includes only rows matching a `WHERE` predicate. If 95 % of your `jobs` table has `status = 'processed'` and your application only ever queries `status = 'pending'`, a full index wastes space and write overhead on rows you never filter. ```php // database/migrations/2024_06_01_000001_add_partial_index_to_jobs.php public function up(): void { DB::statement( "CREATE INDEX idx_jobs_pending_created ON jobs (created_at DESC) WHERE status = 'pending'" ); } public function down(): void { DB::statement('DROP INDEX IF EXISTS idx_jobs_pending_created'); } ``` Now this Eloquent query hits the partial index directly: ```php Job::where('status', 'pending') ->orderByDesc('created_at') ->limit(100) ->get(); ``` Run `EXPLAIN ANALYZE` to confirm: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at DESC LIMIT 100; ``` You should see `Index Scan using idx_jobs_pending_created` with a tiny `rows=` estimate — not a `Seq Scan`. --- Covering Indexes: Eliminate the Heap Fetch ------------------------------------------ PostgreSQL's `INCLUDE` clause lets you attach non-key columns to an index so the planner can satisfy a query entirely from the index page — an **Index Only Scan** — without touching the heap. ```php DB::statement( "CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (id, name, created_at)" ); ``` This is valuable for API list endpoints that always return the same small column set: ```php User::where('email', $email) ->select(['id', 'name', 'created_at']) ->first(); ``` Without `INCLUDE`, PostgreSQL fetches the index entry, then does a second I/O to the heap row. With a covering index the heap fetch disappears entirely. On a busy read replica this difference is measurable. > **Note:** `INCLUDE` columns are not part of the B-tree key, so you cannot filter or sort on them via the index — they are payload only. --- Expression Indexes: Index the Computation, Not the Column --------------------------------------------------------- If your application lowercases emails before lookup, or extracts a JSONB key, index the expression itself: ```php // Case-insensitive email lookup DB::statement( 'CREATE INDEX idx_users_lower_email ON users (lower(email))' ); ``` ```php // Eloquent query must match the expression exactly User::whereRaw('lower(email) = ?', [strtolower($input)])->first(); ``` For JSONB columns storing user preferences: ```php DB::statement( "CREATE INDEX idx_profiles_country ON profiles ((settings->>'country'))" ); ``` ```php Profile::whereRaw("settings->>'country' = ?", ['DE'])->get(); ``` PostgreSQL will only use the expression index when the query predicate matches the expression **exactly** — including function name and argument order. Verify with `EXPLAIN`. --- Combining All Three ------------------- You can compose these features. A partial covering expression index is valid: ```sql CREATE INDEX idx_subscriptions_active_plan ON subscriptions (lower(plan_name) DESC) INCLUDE (user_id, expires_at) WHERE cancelled_at IS NULL; ``` This serves a dashboard query that lists active subscriptions by plan name (case-insensitive) and needs `user_id` and `expires_at` without a heap fetch — and skips all cancelled rows entirely. --- Key Takeaways ------------- - **Partial indexes** shrink index size and write cost by excluding rows your queries never touch. - **Covering indexes** (`INCLUDE`) enable Index Only Scans, removing heap I/O for fixed column sets. - **Expression indexes** let the planner use an index when your predicate applies a function to a column. - Always verify with `EXPLAIN (ANALYZE, BUFFERS)` — the planner may ignore an index if statistics are stale; run `ANALYZE table_name` after bulk loads. - Laravel migrations can execute raw DDL via `DB::statement()`; wrap in `Schema::hasIndex()` guards for idempotency in CI. - Indexes cost write overhead — profile before adding, and drop unused ones with `pg_stat_user_indexes`. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-partial-covering-and-expression-indexes-for-laravel-query-tuning&text=PostgreSQL+Partial%2C+Covering%2C+and+Expression+Indexes+for+Laravel+Query+Tuning) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-partial-covering-and-expression-indexes-for-laravel-query-tuning) Frequently Asked Questions ---------------------------- 3 questions Q01 Will Laravel's schema builder create partial or covering indexes natively? Not as of Laravel 12. You must use DB::statement() with raw DDL inside your migration's up() method. Wrap the statement in a conditional check against pg_indexes if you need idempotency. Q02 How do I confirm PostgreSQL is actually using my new index? Run EXPLAIN (ANALYZE, BUFFERS) on the exact query. Look for 'Index Scan' or 'Index Only Scan' in the plan. If you see 'Seq Scan', check that the query predicate matches the index definition exactly and that table statistics are current (run ANALYZE). Q03 Does an expression index work with Eloquent's where() helper? Only if the generated SQL matches the indexed expression exactly. For lower(email) you need whereRaw('lower(email) = ?', [...]). A plain where('email', $value) produces email = $value which does not match the expression and will skip the index. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Laravel + Python FastAPI: Image OCR Demo](https://cdn.msaied.com/447/5f3d87146fbf19957973cbc88c6c0155.png) Laravel Python FastAPI ### Laravel + Python FastAPI: Image OCR Demo Learn how to call a Python image OCR script from Laravel using FastAPI as the bridge. This premium tutorial wa... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 20 Jul 2026 3 min read Read ](https://msaied.com/articles/laravel-python-fastapi-image-ocr-demo) [ ![CQRS in Laravel Without a Framework: Commands, Handlers, and Read Models That Stay Lean](https://cdn.msaied.com/446/a05febe70b72107387f210e0f9eae089.png) laravel cqrs architecture ### CQRS in Laravel Without a Framework: Commands, Handlers, and Read Models That Stay Lean Skip the heavy event-sourcing libraries. This guide shows how to implement a practical CQRS layer in Laravel u... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 20 Jul 2026 1 min read Read ](https://msaied.com/articles/cqrs-in-laravel-without-a-framework-commands-handlers-and-read-models-that-stay-lean) [ ![Laravel Event Sourcing: Projections, Snapshots, and Replay Without the Framework Tax](https://cdn.msaied.com/445/b4f83e0b5da7c11e2fe942eebc1cad08.png) laravel event-sourcing ddd ### Laravel Event Sourcing: Projections, Snapshots, and Replay Without the Framework Tax Event sourcing in Laravel without a heavy framework. Build lean projectors, snapshot aggregates at scale, and... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 19 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-event-sourcing-projections-snapshots-and-replay-without-the-framework-tax) [ ![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)