PostgreSQL Indexing für Laravel-Apps: Wann welcher Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM articles
WHERE author_id = 42 AND published_at IS NOT NULL
ORDER BY published_at DESC LIMIT 20;

-- Limit  (cost=0.42..18.74 rows=20 width=48) (actual time=0.041..0.182 rows=20 loops=1)
--   ->  Index Scan Backward using articles_author_published_idx on articles
--         (cost=0.42..1834.21 rows=2003 width=48)
--         (actual time=0.039..0.176 rows=20 loops=1)
--         Index Cond: (author_id = 42)
--         Filter: (published_at IS NOT NULL)
--   Buffers: shared hit=8
-- Planning Time: 0.182 ms
-- Execution Time: 0.214 ms

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('articles', function (Blueprint $table) {
            // B-Tree (Default) auf einer Foreign-Key-Spalte
            $table->index('author_id', 'articles_author_idx');

            // GIN auf einer JSONB-Spalte für ->> und @> Operatoren
            // Laravel 13 unterstützt ->index() nicht für GIN - also raw:
            // (Spalte muss bereits als jsonb existieren)
        });

        DB::statement('CREATE INDEX articles_metadata_gin_idx
                       ON articles USING GIN (metadata jsonb_path_ops)');

        // Volltext über title + body, generierte tsvector-Spalte
        DB::statement("ALTER TABLE articles
                       ADD COLUMN search_vector tsvector
                       GENERATED ALWAYS AS (
                           to_tsvector('german', coalesce(title,'') || ' ' || coalesce(body,''))
                       ) STORED");
        DB::statement('CREATE INDEX articles_search_gin_idx
                       ON articles USING GIN (search_vector)');

        // GiST für Range-Overlap-Suche auf bookings
        DB::statement('CREATE INDEX bookings_period_gist_idx
                       ON bookings USING GIST (period)');
    }

    public function down(): void
    {
        DB::statement('DROP INDEX IF EXISTS articles_metadata_gin_idx');
        DB::statement('DROP INDEX IF EXISTS articles_search_gin_idx');
        DB::statement('DROP INDEX IF EXISTS bookings_period_gist_idx');
        DB::statement('ALTER TABLE articles DROP COLUMN IF EXISTS search_vector');

        Schema::table('articles', function (Blueprint $table) {
            $table->dropIndex('articles_author_idx');
        });
    }
};

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // Composite-Index: zuerst Gleichheit, dann Sortierung
        Schema::table('articles', function (Blueprint $table) {
            $table->index(
                ['author_id', 'published_at'],
                'articles_author_published_idx'
            );
        });

        // Partial Index: nur veröffentlichte Einträge
        DB::statement('CREATE INDEX articles_published_only_idx
                       ON articles (published_at DESC)
                       WHERE published_at IS NOT NULL');

        // Partial Index: nur offene Tickets
        DB::statement("CREATE INDEX tickets_open_idx
                       ON tickets (assigned_to, created_at)
                       WHERE status = 'open'");

        // Trigram für ILIKE-Suche auf customers.name
        DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');
        DB::statement('CREATE INDEX customers_name_trgm_idx
                       ON customers USING GIN (name gin_trgm_ops)');

        // Volltext-Fulltext direkt über Laravel-Schema (Postgres-Default)
        Schema::table('articles', function (Blueprint $table) {
            $table->fullText('title', 'articles_title_fulltext_idx');
        });
    }

    public function down(): void
    {
        DB::statement('DROP INDEX IF EXISTS articles_published_only_idx');
        DB::statement('DROP INDEX IF EXISTS tickets_open_idx');
        DB::statement('DROP INDEX IF EXISTS customers_name_trgm_idx');

        Schema::table('articles', function (Blueprint $table) {
            $table->dropFullText('articles_title_fulltext_idx');
            $table->dropIndex('articles_author_published_idx');
        });
    }
};