I have a table with ~35M rows, and trying to find "processed" records to remove from time to time. There are 14 valid statuses, and 10 of them are processed. id uuid default uuid_generate_v4() not null primary key, fk_id uuid…
I have two columns: column1 with two distinct values (0, 1) and column2 with 3 distinct values ('A', 'B', 'C'). Value in column1 is always 0 for 'A' and 'B' in column2, but if the value in column2 is 'C'…
I wrote a SQL query to fetch all movies and, if present, include the name 'Brad Pitt' for movies where he's a cast member. The query is as follows: select m.title, p_cast.person_name From movie m left join ( movie_cast m_cast…
So I have this query which is extremely slow to run - SELECT "conversation".*, "teamBotLocation"."id" AS "teamBotLocation.id", "teamBotLocation"."name" AS "teamBotLocation.name" FROM "conversation" AS "conversation" LEFT OUTER JOIN "teamBotLocation" AS "teamBotLocation" ON "conversation"."teamBotLocationId" = "teamBotLocation"."id" WHERE "conversation"."teamBotId" = 'some_id' AND "conversation"."teamBotLocationId"…
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 am trying to implement a Postgres function with optional parameters. I tried a stable PL/pgSQL function with required parameters, splitting cases along user input in an IF construct. Here is a simplified example: Setup: CREATE TABLE IF NOT EXISTS…
I have this query I'm running in MySQL: SELECT count(*) FROM library AS l JOIN plays AS p ON p.user_id = l.user_id AND l.path = p.path WHERE l.user_id = 20977 AND p.time >= '2022-10-17'; When EXPLAIN ANALYZE is run: |…
My application has a myriad of queries and I wanted to get the execution plan of some of them. Most if not all of the queries have multiple parameters and I can't find how to get the execution plan for…
Example at Oracle: explain plan for select * from sample_table where column_name = :1 This will return query plan with detailed steps and cost in a tabular form. Example at PostgreSQL explain (format yaml) select * from sample_table where column_name…
I have a multi-join query that targeting the hospital's chart database. this takes 5~10 seconds or more. This is the visual expain using mysql workbench. The query is below. select sc.CLIENT_ID as 'guardianId', sp.PET_ID as 'patientId', sp.NAME as 'petName' ,…