Skip to content

Commit 5f71e71

Browse files
TheSentinel454Elrondclaude
committed
perf(db): plan multi-value #d reads with their values
A REQ with several `#d` values for NIP-33 kinds (a client resolving channel metadata for up to 128 channels, say) ran as a named, cached statement. After a few runs on a connection, Postgres can switch it to a generic plan. That plan walks the author's `created_at` index and filters `d_tag` row by row. Its LIMIT (one row per value) is never reached early, so it reads every stored version of the kind instead of probing `idx_events_parameterized` once per value. Multi-value `#d` reads now run as unnamed statements, as tag-value matches already do, so every call is planned with its values. The values are also bound as one array (`d_tag = ANY($n)`), so the number of values no longer changes the SQL text. The page SELECT and the COUNT share one helper for the `d` predicate instead of two copies. Co-authored-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz> Co-Authored-By: Claude Opus 5.5 <[email protected]> Signed-off-by: Luke Tornquist <[email protected]>
1 parent 9aa7012 commit 5f71e71

1 file changed

Lines changed: 118 additions & 53 deletions

File tree

‎crates/buzz-db/src/store/event.rs‎

Lines changed: 118 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -580,8 +580,8 @@ pub(crate) async fn query_events_on(
580580
_ => None,
581581
};
582582
// Thread-reply reads must be planned with their root: a cached generic
583-
// plan sorts every reply of a large thread. Tag matches likewise.
584-
let persistent = !has_tag_values(q) && root.is_none();
583+
// plan sorts every reply of a large thread. Tag and `#d` matches likewise.
584+
let persistent = !plans_with_bound_values(q) && root.is_none();
585585
let mut qb = match root {
586586
Some(root) => build_thread_reply_sql(q, root),
587587
None => build_query_events_sql(q),
@@ -799,19 +799,7 @@ fn push_event_filters(qb: &mut QueryBuilder<sqlx::Postgres>, col_prefix: &str, q
799799
}
800800
}
801801

802-
if let Some(ref d) = q.d_tag {
803-
qb.push(format!(" AND {col_prefix}d_tag = "))
804-
.push_bind(d.clone());
805-
} else if let Some(ref ds) = q.d_tags {
806-
if !ds.is_empty() {
807-
qb.push(format!(" AND {col_prefix}d_tag IN ("));
808-
let mut sep = qb.separated(", ");
809-
for d in ds {
810-
sep.push_bind(d.clone());
811-
}
812-
qb.push(")");
813-
}
814-
}
802+
push_d_tag_filter(qb, col_prefix, q);
815803

816804
// Shared-gated visibility pushdown: exclude SHARED_GATED_KINDS events that
817805
// are neither authored by the reader nor explicitly shared. Applied BEFORE
@@ -1134,12 +1122,34 @@ fn push_exact_tag_match(
11341122
.push(")))");
11351123
}
11361124

1137-
/// Whether `q` carries a [`EventQuery::tag_values`] match. Such a query runs
1138-
/// as an unnamed statement, so Postgres plans it with the bound values. After
1139-
/// a few runs, a cached generic plan ignores the value and can scan every
1140-
/// group-state row in the community instead of using the `tags` GIN index.
1141-
fn has_tag_values(q: &EventQuery) -> bool {
1125+
/// Whether `q` must run as an unnamed statement, so Postgres plans it with the
1126+
/// bound values. After a few runs on a connection, Postgres can switch a named
1127+
/// statement to a cached generic plan that ignores the values:
1128+
/// - a [`EventQuery::tag_values`] match then scans every group-state row in
1129+
/// the community instead of using the `tags` GIN index;
1130+
/// - a multi-value `#d` ([`EventQuery::d_tags`]) match walks the author's
1131+
/// `created_at` index and filters `d_tag` row by row. Its `LIMIT` is never
1132+
/// reached when it asks for one row per value, so it reads every stored
1133+
/// version of the kind (relay-signed channel metadata, say) instead of
1134+
/// probing `idx_events_parameterized` once per value.
1135+
fn plans_with_bound_values(q: &EventQuery) -> bool {
11421136
q.tag_values.iter().any(|(_, values)| !values.is_empty())
1137+
|| q.d_tags.as_ref().is_some_and(|ds| !ds.is_empty())
1138+
}
1139+
1140+
/// AND the NIP-33 `d` match: one value, or any of [`EventQuery::d_tags`] as
1141+
/// one array bind, so the number of values does not change the SQL text.
1142+
fn push_d_tag_filter(qb: &mut QueryBuilder<sqlx::Postgres>, col_prefix: &str, q: &EventQuery) {
1143+
if let Some(ref d) = q.d_tag {
1144+
qb.push(format!(" AND {col_prefix}d_tag = "))
1145+
.push_bind(d.clone());
1146+
} else if let Some(ref ds) = q.d_tags {
1147+
if !ds.is_empty() {
1148+
qb.push(format!(" AND {col_prefix}d_tag = ANY("))
1149+
.push_bind(ds.clone())
1150+
.push(")");
1151+
}
1152+
}
11431153
}
11441154

11451155
/// AND one [`push_exact_tag_match`] per non-empty [`EventQuery::tag_values`]
@@ -1336,7 +1346,7 @@ pub(crate) async fn count_events_on(
13361346
let mut qb = count_events_sql(q);
13371347
// Same transaction-local deadline as `query_events_on`, so a COUNT cannot
13381348
// stall indefinitely either.
1339-
let row = fetch_with_read_deadline(conn, &mut qb, !has_tag_values(q), deadline)
1349+
let row = fetch_with_read_deadline(conn, &mut qb, !plans_with_bound_values(q), deadline)
13401350
.await?
13411351
.into_iter()
13421352
.next()
@@ -1443,19 +1453,7 @@ fn count_events_sql(q: &EventQuery) -> QueryBuilder<sqlx::Postgres> {
14431453
.push_bind(u);
14441454
}
14451455

1446-
if let Some(ref d) = q.d_tag {
1447-
qb.push(format!(" AND {col_prefix}d_tag = "))
1448-
.push_bind(d.clone());
1449-
} else if let Some(ref ds) = q.d_tags {
1450-
if !ds.is_empty() {
1451-
qb.push(format!(" AND {col_prefix}d_tag IN ("));
1452-
let mut sep = qb.separated(", ");
1453-
for d in ds {
1454-
sep.push_bind(d.clone());
1455-
}
1456-
qb.push(")");
1457-
}
1458-
}
1456+
push_d_tag_filter(&mut qb, col_prefix, q);
14591457

14601458
qb
14611459
}
@@ -3265,6 +3263,30 @@ mod e_tag_filter_shape_tests {
32653263
assert!(!unbounded.contains("m.event_kind"));
32663264
}
32673265

3266+
/// A multi-value `#d` read binds its values as one array, so the number of
3267+
/// values does not change the SQL text, in both the page SELECT and the
3268+
/// COUNT. One `IN ($n, …)` bind per value made a new statement (and a new
3269+
/// `pg_stat_statements` entry) for every arity.
3270+
#[test]
3271+
fn d_tag_sql_does_not_depend_on_value_count() {
3272+
let sql = |n: usize| {
3273+
let mut q = EventQuery::for_community(CommunityId::from_uuid(uuid::Uuid::nil()));
3274+
q.kinds = Some(vec![39000]);
3275+
q.pubkey = Some(vec![7; 32]);
3276+
q.d_tags = Some((0..n).map(|i| format!("d{i}")).collect());
3277+
(
3278+
build_query_events_sql(&q).sql().as_str().to_owned(),
3279+
count_events_sql(&q).sql().as_str().to_owned(),
3280+
)
3281+
};
3282+
let two = sql(2);
3283+
assert_eq!(two, sql(128));
3284+
for text in [&two.0, &two.1] {
3285+
assert!(text.contains("d_tag = ANY($"), "{text}");
3286+
assert!(!text.contains("d_tag IN ("), "{text}");
3287+
}
3288+
}
3289+
32683290
/// The channel scope is one array bind: a viewer's channel count must not
32693291
/// change the SQL text, or each count is its own statement that is planned
32703292
/// again on every call. Holds for the `#p` join and plain reads, for
@@ -3363,38 +3385,81 @@ mod postgres_tests {
33633385
.expect("read prepared statements")
33643386
}
33653387

3366-
/// A label or creator match must keep parameter-aware planning. A cached
3367-
/// generic plan ignores the label value and scans every group-state row in
3368-
/// the community instead of using the `tags` GIN index.
3388+
/// Label/creator matches and multi-value `#d` reads must keep
3389+
/// parameter-aware planning. A cached generic plan ignores the values: a
3390+
/// label match scans every group-state row in the community instead of
3391+
/// using the `tags` GIN index, and a `#d` read walks the author's
3392+
/// `created_at` index and reads every stored version of the kind. The `#d`
3393+
/// read still returns and counts exactly the requested addresses.
33693394
#[tokio::test]
33703395
#[ignore = "requires Postgres"]
3371-
async fn tag_value_queries_are_not_cached_as_prepared_statements() {
3396+
async fn value_matched_queries_are_not_cached_as_prepared_statements() {
33723397
let pool = setup_pool().await;
33733398
let community = CommunityId::from_uuid(make_test_community(&pool).await);
3399+
let keys = Keys::generate();
3400+
let mut addressed = Vec::new();
3401+
for d in ["a", "b", "c"] {
3402+
let event = EventBuilder::new(Kind::Custom(30023), "")
3403+
.tags([Tag::parse(["d", d]).unwrap()])
3404+
.sign_with_keys(&keys)
3405+
.unwrap();
3406+
insert_event(&pool, community, &event, None)
3407+
.await
3408+
.expect("insert addressable event");
3409+
addressed.push((d, event.id));
3410+
}
33743411
let mut conn = pool.acquire().await.expect("acquire");
33753412
// Clear SQLx's statement cache and Postgres's prepared statements
33763413
// together; a raw `DEALLOCATE ALL` would leave the client cache
33773414
// pointing at statements the server no longer has.
33783415
sqlx::Connection::clear_cached_statements(&mut *conn)
33793416
.await
33803417
.expect("clear prepared statements");
3381-
let mut q = EventQuery::for_community(community);
3382-
q.kinds = Some(vec![39000]);
3383-
q.tag_values = vec![("t".to_owned(), vec!["for-you".to_owned()])];
3384-
for _ in 0..6 {
3385-
query_events_on(&mut conn, &q, ReadDeadline::PerRead)
3386-
.await
3387-
.expect("label query");
3388-
count_events_on(&mut conn, &q, ReadDeadline::PerRead)
3389-
.await
3390-
.expect("label count");
3418+
3419+
let mut label = EventQuery::for_community(community);
3420+
label.kinds = Some(vec![39000]);
3421+
label.tag_values = vec![("t".to_owned(), vec!["for-you".to_owned()])];
3422+
let mut d_tags = EventQuery::for_community(community);
3423+
d_tags.kinds = Some(vec![30023]);
3424+
d_tags.pubkey = Some(keys.public_key().to_bytes().to_vec());
3425+
d_tags.d_tags = Some(vec!["a".to_owned(), "c".to_owned()]);
3426+
for (q, statement) in [(&label, "%tags @> ANY(%"), (&d_tags, "%d_tag = ANY(%")] {
3427+
for _ in 0..6 {
3428+
query_events_on(&mut conn, q, ReadDeadline::PerRead)
3429+
.await
3430+
.expect("value-matched query");
3431+
count_events_on(&mut conn, q, ReadDeadline::PerRead)
3432+
.await
3433+
.expect("value-matched count");
3434+
}
3435+
assert_eq!(prepared_like(&mut conn, statement).await, 0, "{statement}");
33913436
}
3392-
assert_eq!(prepared_like(&mut conn, "%tags @> ANY(%").await, 0);
33933437

3394-
// Control: the same query without a tag match stays a cached statement,
3395-
// so the check above can see one.
3396-
q.tag_values.clear();
3397-
query_events_on(&mut conn, &q, ReadDeadline::PerRead)
3438+
let mut page: Vec<_> = query_events_on(&mut conn, &d_tags, ReadDeadline::PerRead)
3439+
.await
3440+
.expect("d-tag page")
3441+
.into_iter()
3442+
.map(|stored| stored.event.id)
3443+
.collect();
3444+
page.sort();
3445+
let mut expected: Vec<_> = addressed
3446+
.iter()
3447+
.filter(|(d, _)| *d != "b")
3448+
.map(|(_, id)| *id)
3449+
.collect();
3450+
expected.sort();
3451+
assert_eq!(page, expected, "exactly the requested addresses");
3452+
assert_eq!(
3453+
count_events_on(&mut conn, &d_tags, ReadDeadline::PerRead)
3454+
.await
3455+
.expect("d-tag count"),
3456+
2
3457+
);
3458+
3459+
// Control: the same query without a value match stays a cached
3460+
// statement, so the checks above can see one.
3461+
label.tag_values.clear();
3462+
query_events_on(&mut conn, &label, ReadDeadline::PerRead)
33983463
.await
33993464
.expect("plain query");
34003465
assert!(prepared_like(&mut conn, "%FROM events%").await > 0);

0 commit comments

Comments
 (0)