I have rewritten the below query from using a correlated subquery to using a LATERAL JOIN. It is now faster, and I would like to understand why? This SO answer says that there is no golden rule and that it…
I have a PostgreSQL (v15) DB view that rolls up a bunch of data per user for a single organization, to display a report of data like fees owed/paid per user, etc. This is executed with an org ID and…
I have a request to add new column that will count distinct column named kode_buatan from january to the month in the record (so if the record have date in march 2024 count distinct kode buatan from january 2024 to…
Mysql 8 takes 2 minutes to sort an empty set The following table on 8.0.33 MySQL Community Server CREATE TABLE `eventiAcc` ( `chiave` int NOT NULL AUTO_INCREMENT, `tipo` tinyint NOT NULL, `account` int DEFAULT NULL, `utente` int DEFAULT NULL, `timestp`…
Some background: I'm running the following simple select statement on Postgres 15.3 server with 128GB memory, and contrary to my belief it takes ~6 minutes. The statement in involve two relations, big_table_70m with approximately 70M rows and other_table_50m with a…
When I perform EXPLAIN on the following query to fetch users with bookings SELECT user.id FROM user LEFT JOIN booking ON booking.user_id = user.id AND booking.end_timestamp > 1706878800 AND booking.end_timestamp <= 1706882400 AND booking.status IN ('pending', 'progress', 'done'); I get…
I have two tables "Tasks"(300k rows) and "TaskAssignedRoles"(500k rows). CREATE TABLE public."Tasks" ( "ID" uuid NOT NULL, "RowID" uuid NOT NULL, "Planned" timestamptz NOT NULL, CONSTRAINT "pk_Tasks" PRIMARY KEY ("RowID") ); CREATE INDEX "idx_Tasks_ID" ON public."Tasks" USING btree ("ID"); CREATE…
My goal I'm trying to track and display a user's daily streak posting to my app, but struggling to write a query that works reliably and returns an accurate count. Some context My app has a prompt and a post…
I have a MongoDB collection that stores some test submissions. The highlevel schema looks as follows. { "lessonType": "quiz", "userId": "639cb6d9513a420026eddca1" "courseId": "639cb6d9513a420026eddca2", "lessonId": "639cb6d9513a420026eddca3", ...some_more_fields } Now there are two indexes on this collection. first one: { courseId: 1,…
I have the following postgres query that's meant to update all message entries: update message m set user_id = u.id from "user" u where u.user_pgid = m.user_pgid The trouble is that I have ~300,000,000 message records, and postgres seems to…