@jacksonchen666@micro.jacksonchen666.com FoundKey has some terrible queries which are autogenerated by the ORM. That gives you beautiful stuff like the image I attached to this post. I ran VACUUM on the most used columns and that seems to have had some effect but not much.
Conversation
Notices
-
Embed this notice
@Dwarf (dwarf@borg.social)'s status on Tuesday, 06-Dec-2022 02:42:32 JST @Dwarf
-
Embed this notice
jacksonchen666 :dado_verified: :clock: :antiverified: (jacksonchen666@micro.jacksonchen666.com)'s status on Tuesday, 06-Dec-2022 02:42:43 JST jacksonchen666 :dado_verified: :clock: :antiverified:
@dwarf some questions to ask:
- is the SSD dying or showing signs of potential death?
- is the connection reliable and still functioning at a fast speed?
- do you just have too much overhead trying to do a query? (or simply read the data on the disk?)
- is the query somehow inefficient? (needing to go through the table because can't use index index) -
Embed this notice
@Dwarf (dwarf@borg.social)'s status on Tuesday, 06-Dec-2022 02:42:44 JST @Dwarf
I wonder if there's a way to find out why simple queries like these are slow on FoundKey. The database is running in a podman container with a volume hosted on a SSD mounted over USB 3.0, so that should not have a performance impact this big, right?foundkey[444234]: query is slow: SELECT count(distinct "note"."userId") FROM "note" "note" WHERE '{"bot"}' <@ "note"."tags" AND "note"."createdAt" < $1 AND "note"."createdAt" > $2 -- PARAMETERS: ["2022-12-04T19:55:00.000Z","2022-12-04T19:45:00.000Z"] foundkey[444234]: execution time: 402 #postgres #db #dba #fediadmin
-
Embed this notice