search

fiatjaf / eventstore

an abstraction layer over databases for nostr events
The Unlicense
26 stars 15 forks source link

Why pubkey is matched by LIKE ? #8

Closed mattn closed 7 months ago

mattn commented 7 months ago

Current implementation, pubkey is matched by LIKE.

https://github.com/fiatjaf/eventstore/blob/f5d23a3b515bd5081a34dc80bea0fd6b70acbc84/postgresql/query.go#L93-L100

Amethyst send this long filter to relay server.

https://gist.github.com/mattn/e15c770b0a5dc6f0f3f968338bb9a492

So many like query are executed. This is too heavy. Why eventstore/postgresql use LIKE for this? I guess that "pubkey in (xxx, yyy, zzz)" should be faster.

mattn commented 7 months ago

One more question, why this part use left-hand match?

mattn commented 7 months ago

BenchmarkHex1: current implementation (use parsed) BenchmarkHex2: current implementation (use key) BenchmarkHex3: make pubkey in (...) query (use key) BenchmarkHex4: make pubkey in (...) query (use pubkey)

https://gist.github.com/mattn/d5ca6172554a56eb7f779aa9d764a386

$ go test -bench .
goos: linux
goarch: amd64
pkg: hex-benchmark
cpu: Intel(R) Core(TM) i5-8250U CPU @ 1.60GHz
BenchmarkHex1-8        4462    229385 ns/op
BenchmarkHex2-8        6105    184495 ns/op
BenchmarkHex3-8        7024    174365 ns/op
BenchmarkHex4-8        6008    181123 ns/op
PASS
ok      hex-benchmark   5.467s
fiatjaf commented 7 months ago

There was this thing in the protocol that said we should support prefix matching for authors and ids in relays. But that was removed from the spec and can be safely removed now from this postgres library.

fiatjaf commented 7 months ago

Because of that we also had these indexes:

USING btree (id text_pattern_ops)

Which are required for optimal prefix matching, but should be changed now too for normal indexes.