63 lines
4.6 KiB
PHP
63 lines
4.6 KiB
PHP
<?php
|
|
|
|
namespace Tests\Benchmarks;
|
|
|
|
use Illuminate\Database\Events\QueryExecuted;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
require_once __DIR__.'/ModuleAssociationLoadTest.php';
|
|
|
|
final class ModuleAssociationIndexPlanTest extends ModuleAssociationLoadTest
|
|
{
|
|
public function test_synthetic_volume_and_query_plans(): void
|
|
{
|
|
$run = (string) getenv('SGF_ISOLATED_REGRESSION_RUN');
|
|
$this->assertMatchesRegularExpression('/^[a-f0-9]{12}$/D', $run);
|
|
$identity = DB::selectOne("SELECT current_database() AS database,current_user AS role,inet_server_port() AS port,current_setting('cluster_name') AS cluster");
|
|
$this->assertSame('sgf_tempo_test', $identity->database); $this->assertSame('sgf_test_runner', $identity->role);
|
|
$this->assertSame('sgf_modular_regression_'.$run, $identity->cluster); $this->assertSame(55439, (int) $identity->port);
|
|
$f = $this->fixture(10000, true); $actor = $f['identity'];
|
|
$this->actingAs($actor['manager'])->withSession(['auth_version' => $actor['manager']->auth_version]);
|
|
$token = $this->postJson('/api/context', ['tenant_id' => $actor['tenant'], 'company_id' => $actor['company'], 'unit_id' => $actor['unit'], 'module_id' => null])->assertOk()->json('context.token');
|
|
$this->withHeader('X-SGF-Context', $token);
|
|
$capturing = false; $queries = [];
|
|
DB::listen(function (QueryExecuted $q) use (&$capturing, &$queries) { if ($capturing && str_contains($q->sql, 'identity_audit')) $queries[hash('sha256', $q->sql)] = ['sql' => $q->sql, 'bindings' => $q->bindings]; });
|
|
$report = ['date' => '2026-11-02', 'identity' => $identity, 'records_per_area' => 10000, 'variants' => []];
|
|
foreach (['batch', 'indexed_batch'] as $mode) {
|
|
DB::beginTransaction();
|
|
try {
|
|
if ($mode === 'indexed_batch') {
|
|
DB::statement("CREATE INDEX sgf_qa_assoc_links ON identity_audit (tenant_id,company_id,(details->>'area'),(details->>'record_id'),(details->>'module_id'),id DESC) WHERE action IN ('module.record.associated','module.record.disassociated')");
|
|
DB::statement("CREATE INDEX sgf_qa_assoc_revision ON identity_audit (tenant_id,company_id,(details->>'area'),(details->>'record_id'),id DESC) WHERE action IN ('module.record.associated','module.record.disassociated','module.record.reviewed')");
|
|
DB::statement('ANALYZE identity_audit');
|
|
}
|
|
$queries = []; $capturing = true;
|
|
$this->getJson('/api/module-records?area=billing_drafts&per_page=100')->assertOk();
|
|
$this->getJson('/api/module-records?area=clients&state=pending&per_page=25')->assertOk();
|
|
$capturing = false;
|
|
foreach ($queries as $signature => $q) {
|
|
DB::beginTransaction(); DB::statement("SET LOCAL statement_timeout = '3s'");
|
|
try {
|
|
$raw = DB::select('EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) '.$q['sql'], $q['bindings']);
|
|
$plan = json_decode($raw[0]->{'QUERY PLAN'}, true, 512, JSON_THROW_ON_ERROR)[0]; $nodes = [];
|
|
$this->nodesOnly($plan['Plan'], $nodes);
|
|
$report['variants'][$mode][] = ['signature' => $signature, 'shape' => $q['sql'], 'execution_ms' => $plan['Execution Time'], 'nodes' => $nodes];
|
|
} catch (\Illuminate\Database\QueryException $e) { $report['variants'][$mode][] = ['signature' => $signature, 'status' => 'plan_timeout', 'sqlstate' => $e->errorInfo[0] ?? $e->getCode()];
|
|
} finally { DB::rollBack(); }
|
|
}
|
|
} finally { $capturing = false; DB::rollBack(); }
|
|
}
|
|
$this->assertSame(0, (int) DB::selectOne("SELECT count(*) AS n FROM pg_indexes WHERE schemaname='public' AND indexname LIKE 'sgf_qa_assoc_%'")->n);
|
|
$report['indexes_rolled_back'] = true;
|
|
$output = getenv('SGF_ASSOCIATION_LOAD_OUTPUT');
|
|
if (!$output || realpath(dirname($output)) !== realpath(base_path('..'))) throw new \RuntimeException('Unexpected report directory');
|
|
file_put_contents($output, json_encode($report, JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR).PHP_EOL);
|
|
}
|
|
|
|
private function nodesOnly(array $node, array &$out): void
|
|
{
|
|
$out[] = array_intersect_key($node, array_flip(['Node Type', 'Relation Name', 'Index Name', 'Actual Rows', 'Actual Loops', 'Rows Removed by Filter', 'Shared Hit Blocks', 'Shared Read Blocks', 'Temp Read Blocks', 'Temp Written Blocks']));
|
|
foreach ($node['Plans'] ?? [] as $child) $this->nodesOnly($child, $out);
|
|
}
|
|
}
|