X
•4/1/24, 1:30 PM
L
L
L
BBLCombined with db round trips (even with smart placement) being consistently high latency, has led us to have to move to Postgres. We notice that “cold start” times and round trips are consistently better from across the globe, even with a single Postgres instance in us-east :/
M
wrangler d1 insights into the dash)
L
SM
O
DD
AO
SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4 userId and tagLIMIT was 100 I would expect 100 rows_read without the order, but with the order it shows every object in the tag x 2 (i.e. 600 rows_read for 300 existing objects with the tag).... Any way to optimize this? Or another way to order it in descending order without reading the entire index?M
DLFor example if my LIMIT was 100 I would expect 100 rows_read without the order,That's not correct. SQLite has to read rows of its indexes as well to figure out which rows you want. It's very rare that your
LIMIT will be the total number of rows the actual SQLite query will process. Keep in mind, indexes do count as rows read/written themselves too.EXPLAIN and see what SQLite tells you will do, you can do this in the D1 Console UI too. This is the most reliable way to see what index you are missing.MO
O
B
MM
E
森L森LE森
L
LDoes a DO SQLite db not lock the entire database too though?
D
AL
select * from sqlite_master ?A_cf_KV and sqlite_sequence are also listedA
EMEEEMEM
wrangler d1 insightsSELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4userIdtagLIMITLIMITrows_readEXPLAINselect * from sqlite_master_cf_KVsqlite_sequence