perf(db): plan multi-value #d reads with their values - #9131

Summary

A REQ with several #d values for a NIP-33 kind runs as a named, cached statement. A client resolving channel metadata for up to 128 channels sends this kind of REQ. After a few runs on a connection, Postgres can switch the statement to a generic plan, which ignores the bound values:

• The d match stops being an index condition on idx_events_parameterized.
• The generic plan instead walks the author's rows in created_at order and filters d_tag row by row.
• The LIMIT is one row per requested value, so the walk never stops early. It reads every stored version of the kind, not one probe per value.

This change makes three fixes:

• Multi-value #d reads now run as unnamed statements, as tag-value matches already do (plans_with_bound_values), so Postgres plans every call with its values. This covers both the page SELECT and the COUNT.
• The values are bound as one array (d_tag = ANY($n)) instead of one bind per value. The SQL text no longer changes with the number of values, which matches how channel scope is bound.
• The page SELECT and the COUNT now share one push_d_tag_filter helper instead of two copies of the d predicate.

What got simpler:

• One helper replaces the duplicated d predicate blocks.
• A #d read no longer produces a different statement for every value count.

Related issue

N/A. None found.

Testing

• New unit test d_tag_sql_does_not_depend_on_value_count: the SQL text is identical for 2 and 128 values, for both the page query and the count query.
• Postgres test value_matched_queries_are_not_cached_as_prepared_statements, extended from the tag-value test. It runs each #d page and count query 6 times and asserts no d_tag = ANY( statement is left in pg_prepared_statements. It also checks the page and count results.
• Mutants, each caught by the matching test:
  • dropping d_tags from plans_with_bound_values fails the Postgres test;
  • going back to a per-value IN (...) list fails the unit test.
• Gates run at 5f71e71:
  • cargo fmt --check
  • cargo clippy -p buzz-db -p buzz-relay --all-targets -D warnings
  • buzz-db unit tests: 215 passed
  • scripts/postgres-test-run.sh -p buzz-relay -p buzz-db --lib --tests: 1291 passed
  • scripts/test-postgres-test-discovery.sh
• Local EXPLAIN ANALYZE on synthetic data (50k versions of one author's kind 39000 rows, 128 #d values):
  • With force_custom_plan, d_tag = ANY(...) is in the Index Cond on every partition.
  • With force_generic_plan, the partition holding the rows drops d_tag to a row filter.
  • On larger data the generic plan can switch to an ordered created_at walk.

🤖 Generated with Claude Code