Code Examples

var sw = Stopwatch.StartNew();

// What the service actually runs with: Program.cs may set it without the environment variable.
var enabled = redb.Configuration.EnablePvtPrefilter;
var log = new StringBuilder();
var summary = new List<string>();
var withPrefilter = 0;

log.AppendLine($"-- EnablePvtPrefilter = {(enabled ? "true (enabled)" : "false (disabled)")}");
log.AppendLine();

async Task CaseAsync(string label, string expectation, IRedbQueryable<EmployeeProps> query)
{
    log.AppendLine("-----------------------------------------------------------------");
    log.AppendLine($"-- {label}");
    log.AppendLine($"-- expected when enabled: {expectation}");
    log.AppendLine("-----------------------------------------------------------------");

    string sql;
    try
    {
        sql = await query.ToSqlStringAsync();
    }
    catch (Exception ex)
    {
        log.AppendLine($"-- SQL BUILD FAIL: {ex.GetType().Name}: {ex.Message}");
        log.AppendLine();
        summary.Add($"{label}: SQL build failed");
        Console.WriteLine($"[BUILD-FAIL] {label}: {ex.Message}");
        return;
    }

    log.AppendLine(sql);

    // Console.WriteLine();
    // Console.WriteLine($"--- {label} (expected when enabled: {expectation}) ---");
    // Console.WriteLine(sql);

    // "pf" is the alias the planner gives the prefilter scan. The Row form is the extra
    // parenthesised group spliced into the _values scan; note it may hold a SINGLE branch
    // (a merged range), so matching on " OR " would miss it.
    // Semi form was removed after measuring 3x worse than nothing; this stays as a tripwire.
    var hasSemi = sql.Contains("FROM _values pf");
    var hasRow = sql.Contains("AND ((v._id_structure");
    var form = hasSemi ? "Semi" : hasRow ? "Row" : "none";
    if (form != "none") withPrefilter++;

    string ids;
    try
    {
        var rows = await query.ToListAsync();
        ids = rows.Count == 0
            ? "<empty>"
            : string.Join(",", rows.Select(r => r.Id).OrderBy(x => x));
    }
    catch (Exception ex)
    {
        ids = $"EXEC FAIL {ex.GetType().Name}: {ex.Message.Split('\n')[0]}";
    }

    log.AppendLine($"-- prefilter form: {form}");
    log.AppendLine($"-- ids: {ids}");
    log.AppendLine();

    summary.Add($"{label}: {form}");
    Console.WriteLine($"[{form,4}] {label}");
}

// Top-level OR, both branches selective. The tsum case: one search string, several
// fields. Row form, and with pg_trgm the LIKE finally reaches the column.
await CaseAsync(
    "OR: two string fields, same needle",
    "Row",
    redb.Query<EmployeeProps>()
        .Where(e => e.Position.Contains("Design") || e.Department.Contains("Design"))
        .Take(100));

// Top-level AND on a single field: two predicates merge into one selective range.
// Splitting them into an OR would give a tautology, hence the merge.
await CaseAsync(
    "AND: range on one field",
    "Row, single branch with both bounds merged",
    redb.Query<EmployeeProps>()
        .Where(e => e.Salary >= 60000m && e.Salary < 90000m)
        .Take(100));

// Top-level AND across fields. Only the most selective conjunct is pushed; the weak
// one (Position != "") stays above the aggregate where it belongs.
await CaseAsync(
    "AND: three fields, one selective",
    "none (pivot not covered: Position and Age have no branch)",
    redb.Query<EmployeeProps>()
        .Where(e => e.Position != "" && e.Age >= 30 && e.Salary > 70000m)
        .Take(100));

// One weak branch in an OR voids the group: a disjunction is never narrower than its
// widest term, so the extra scan would buy nothing.
await CaseAsync(
    "OR: one weak branch",
    "none (weakest branch below threshold)",
    redb.Query<EmployeeProps>()
        .Where(e => e.Position != "" || e.Salary > 70000m)
        .Take(100));

// Null check is not expressible as a row predicate. Under AND it is simply dropped
// and the other conjunct still gets pushed.
await CaseAsync(
    "AND: null check plus selective field",
    "none (pivot not covered: EmployeeCode has no branch)",
    redb.Query<EmployeeProps>()
        .Where(e => e.EmployeeCode == null && e.Salary > 70000m)
        .Take(100));

// Arrays are not single-row values. Under OR that voids the whole prefilter.
await CaseAsync(
    "OR: array branch",
    "none (array is not a row predicate)",
    redb.Query<EmployeeProps>()
        .Where(e => e.Skills!.Contains("C#") || e.Salary > 70000m)
        .Take(100));

// Sorting pulls Age into the pivot without putting it in the filter. The Row form
// would null that column out, so the guard suppresses the prefilter entirely.
//
// ThenByRedb(Id) is not decoration: Age has thousands of ties, and Take(100) over a tie
// returns whichever hundred the plan happens to produce. Without a unique tiebreak this
// case reported a difference between two runs of the SAME configuration, which reads as
// a prefilter defect and is not one. Id is a base field, so it adds nothing to the pivot
// and the guard still fires on Age.
await CaseAsync(
    "OR plus OrderBy on an uncovered field",
    "none (pivot coverage guard: Age comes from OrderBy)",
    redb.Query<EmployeeProps>()
        .Where(e => e.Position.Contains("Design") || e.Department.Contains("Design"))
        .OrderBy(e => e.Age)
        .ThenByRedb(o => o.Id)
        .Take(100));

// ---- dates -------------------------------------------------------
// Same algebra as numbers, different column (_datetimeoffset) and a different
// parameter round-trip. Worth its own cases: a DateTime that survives C# to Npgsql
// to the pivot but not into the prefilter branch would silently narrow the result.

// Closed range on one field. Two predicates, one structure, one merged branch.
await CaseAsync(
    "AND: date range on one field",
    "Row, single branch with both bounds merged",
    redb.Query<EmployeeProps>()
        .Where(e => e.HireDate >= new DateTime(2030, 1, 1) && e.HireDate < new DateTime(2031, 1, 1))
        .Take(100));

// Open-ended comparison, single leaf at the root.
await CaseAsync(
    "date: single greater-than",
    "Row, one branch",
    redb.Query<EmployeeProps>()
        .Where(e => e.HireDate > new DateTime(2048, 1, 1))
        .Take(100));

// Mixed types under OR: a date branch and a string branch in one disjunction.
await CaseAsync(
    "OR: date branch plus string branch",
    "Row, two branches on different columns",
    redb.Query<EmployeeProps>()
        .Where(e => e.HireDate > new DateTime(2050, 1, 1) || e.Position.Contains("Design"))
        .Take(100));

// Inequality on a date is not selective enough to earn a scan.
await CaseAsync(
    "date: not-equal",
    "none (NotEqual passes almost everything)",
    redb.Query<EmployeeProps>()
        .Where(e => e.HireDate != new DateTime(2030, 1, 1))
        .Take(100));

string? outPath = null;
try
{
    var dir = Path.Combine(AppContext.BaseDirectory, "Output");
    Directory.CreateDirectory(dir);
    outPath = Path.Combine(dir, enabled ? "pvt_prefilter_on.txt" : "pvt_prefilter_off.txt");
    await File.WriteAllTextAsync(outPath, log.ToString());
}
catch (Exception ex)
{
    Console.WriteLine($"[WARN] failed to write dump file: {ex.Message}");
}

sw.Stop();

// The run time above is the sum over all the queries of the example, each printed and then
// executed, so the count comes first: without it the figure reads as one slow query.
var output = new List<string>
{
    $"{summary.Count} queries in this example: SQL printed, then each executed",
    $"Prefilter: {(enabled ? "ENABLED" : "disabled")}, applied in {withPrefilter} of {summary.Count} cases"
};
output.AddRange(summary);
output.AddRange(new[]
{
    outPath != null ? $"Output: {outPath}" : "Output file: <not written>",
    "Diff pvt_prefilter_off.txt against pvt_prefilter_on.txt: id sets must match exactly"
});