Виды индексов: Clustered, Non-Clustered, Unique, Filtered, Partial, Composite, Full-Text?

-- Уникальный email пользователя без учёта регистра
CREATE UNIQUE INDEX ux_users_email ON users (lower(email));

-- Уникальность комбинации
ALTER TABLE order_items ADD CONSTRAINT uq_order_product UNIQUE (order_id, product_id);

try { await db.SaveChangesAsync(ct); }
catch (DbUpdateException ex) when (ex.InnerException is PostgresException { SqlState: PostgresErrorCodes.UniqueViolation })
{
    return Results.Conflict(new ProblemDetails { Title = "Email уже зарегистрирован" });
}

CREATE INDEX ix_orders_customer_created ON orders (customer_id, created_at DESC);

-- PostgreSQL: индекс только по «живым» задачам очереди — крошечный, хотя таблица огромная
CREATE INDEX ix_jobs_pending ON jobs (created_at) WHERE status = 'pending';

-- Условная уникальность: один активный email, удалённые не мешают
CREATE UNIQUE INDEX ux_users_email_active ON users (email) WHERE deleted_at IS NULL;

-- SQL Server: filtered index
CREATE UNIQUE INDEX ux_users_phone ON users (phone) WHERE phone IS NOT NULL;

modelBuilder.Entity<User>().HasIndex(u => u.Email).IsUnique().HasFilter("deleted_at IS NULL");

CREATE INDEX ix_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower(@email);      -- выражение должно совпадать

CREATE INDEX ix_orders_day ON orders ((created_at::date));    -- PostgreSQL
CREATE INDEX ix_events_type ON events ((payload->>'type'));   -- поле JSONB

-- PostgreSQL
ALTER TABLE products ADD COLUMN search tsvector
    GENERATED ALWAYS AS (to_tsvector('russian', coalesce(name, '') || ' ' || coalesce(description, ''))) STORED;
CREATE INDEX ix_products_search ON products USING GIN (search);

SELECT id, name, ts_rank(search, q) AS rank
FROM products, plainto_tsquery('russian', 'беспроводные наушники') q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

var found = await db.Products
    .Where(p => p.Search.Matches(EF.Functions.PlainToTsQuery("russian", term)))
    .OrderByDescending(p => p.Search.Rank(EF.Functions.PlainToTsQuery("russian", term)))
    .Take(20).ToListAsync(ct);