Comments (5)
yeah I am using the watch api.
Just added a quick simple index only on that field so the combined indexes probably would be better you came up with.
from spicedb.
@nulian Be curious to see an EXPLAIN
output from your database, just so we can compare to ours
from spicedb.
Not precisly the same database(already put an index on it myself in that database) but is based on a dump from it but this is the explain
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | relation_tuple | ALL | ix_relation_tuple_by_deleted_transaction | 224739 | Using where |
Without index it takes somewhere between 500ms and 5s
With index it's 10ms - 150ms
Also our slow query log was empty after putting the index on that column
from spicedb.
@nulian are you using the Watch
API? Would you mind sharing the index you added?
This access pattern seems to be used by the MySQL datastore Watch API implementation:
spicedb/internal/datastore/mysql/watch.go
Lines 94 to 103 in 8f6136b
this is the explain I got initially with 84K rows:
EXPLAIN |
---|
-> Filter: (((relation_tuple.created_transaction > 158582) and (relation_tuple.created_transaction <= 158583)) or ((relation_tuple.deleted_transaction > 158582) and (relation_tuple.deleted_transaction <= 158583))) (cost=7920 rows=16334) (actual time=71.3..71.3 rows=0 loops=1) |
-> Table scan on relation_tuple (cost=7920 rows=77839) (actual time=0.0697..58.1 rows=84849 loops=1)
|
Then I added the following index, the rows scanned where lower:
CREATE INDEX ix_watch
ON relation_tuple (created_transaction, deleted_transaction DESC);
OR
CREATE INDEX ix_watch
ON relation_tuple (created_transaction DESC, deleted_transaction DESC);
EXPLAIN |
---|
-> Filter: (((relation_tuple.created_transaction > 158582) and (relation_tuple.created_transaction <= 158583)) or ((relation_tuple.deleted_transaction > 158582) and (relation_tuple.deleted_transaction <= 158583))) (cost=0.804 rows=1) (actual time=0.0921..0.0921 rows=0 loops=1) |
-> Sort-deduplicate by row ID (cost=0.804 rows=1) (actual time=0.0902..0.0902 rows=0 loops=1)
-> Index range scan on relation_tuple using ix_watch over (158582 < created_transaction <= 158583) (cost=1.11 rows=1) (actual time=0.0387..0.0387 rows=0 loops=1)
-> Index range scan on relation_tuple using ix_relation_tuple_by_deleted_transaction over (158582 < deleted_transaction <= 158583) (cost=0.36 rows=1) (actual time=0.0342..0.0342 rows=0 loops=1)
|
EXPLAIN |
---|
-> Filter: (((relation_tuple.created_transaction > 158582) and (relation_tuple.created_transaction <= 158583)) or ((relation_tuple.deleted_transaction > 158582) and (relation_tuple.deleted_transaction <= 158583))) (cost=0.804 rows=1) (actual time=0.104..0.104 rows=0 loops=1) |
-> Sort-deduplicate by row ID (cost=0.804 rows=1) (actual time=0.102..0.102 rows=0 loops=1)
-> Index range scan on relation_tuple using ix_watch over (158583 <= created_transaction < 158582) (cost=1.11 rows=1) (actual time=0.0759..0.0759 rows=0 loops=1)
-> Index range scan on relation_tuple using ix_relation_tuple_by_deleted_transaction over (158582 < deleted_transaction <= 158583) (cost=0.36 rows=1) (actual time=0.00633..0.00633 rows=0 loops=1)
|
from spicedb.
@nulian perfect then I'll open a PR with the new proposed index
from spicedb.
Related Issues (20)
- Improvement: Prevent ZedToken's from being used cross-datastore HOT 2
- Bulk Import: Throws a transactional error HOT 1
- Proposal: Avoiding CRDB BulkImport Timeouts HOT 1
- [memdb] Sending multiple concurrent write relationship requests silently fails HOT 3
- Tracing should follow OpenTelemetry Semantic Conventions HOT 1
- Flake: Postgres tests HOT 3
- Flake: Memdb tests
- `BulkCheckPermission` does not return results in the same order as requests HOT 5
- Enable race detector when running tests
- Playground visualization treats `a - b + c` differently than parser
- Query Planner (Feedback Requested) HOT 8
- [mysql] Touches don't reset `deleted_transaction` HOT 2
- Test Flake: TestDigestRollover
- Lookup Subjects + Resources Return Invalid JSON in HTTP Gateway HOT 2
- Flake: TestPostgresDatastore/postgres-16-head-/GarbageCollection HOT 1
- Flake: TestPostgresDatastore/postgres-16-head-/TestRevisionGC
- Fix Transaction ID counter for Postgres backups
- Caveat must appear within the first 100,000 bytes of the schema
- Respect concurrency limit settings for cluster dispatch HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from spicedb.