Trolladactyl
commented
1 year ago Hi @mitchdowney and @suorcd , I have completed this issue. There are two items I did.
1.) Created 2 new Indexs for the query
2.) Researched all the fields in the query against the Podverse-api and Podverse-ops repos and removed all the fields and one table that were not needed. I have attached both scripts to the ticket.
The final numbers were below but for some reason sandbox really started moving fast towards the end so I am attributing some of the increase in performance to that.
-- original query with no optimization
Planning Time: 1.278 ms
Execution Time: 3.033 ms
-- Optimized query and new index
Planning Time: 0.404 ms
Execution Time: 0.024 ms
/ Apply Indexes to DB / --DROP INDEX "public"."CIDX_PUBLIC_EPISODES_ID"; CREATE INDEX "CIDX_PUBLIC_EPISODES_ID" ON "episodes" ( "id", "guid", "podcastId", "isPublic" );
--DROP INDEX "CIDX_PUBLIC_MEDIAREFS_ID";
CREATE INDEX
"CIDX_PUBLIC_MEDIAREFS_ID"
ON
"mediaRefs"
(
"episodeId" ,
"isPublic",
"isOfficialChapter"
);
/ query /
SELECT
"episode"."id" AS "episode_id",
"episode"."guid" AS "episode_guid",
"episode"."title" AS "episode_title",
"mediaRef"."ownerId" AS "user_id"
FROM
"mediaRefs" "mediaRef"
INNER JOIN "episodes" "episode" ON "episode"."id" = "mediaRef"."episodeId"
WHERE 1=1
AND "mediaRef"."isPublic" = TRUE
AND "episode"."isPublic" = TRUE
AND "mediaRef"."isOfficialChapter" = null
AND "episode"."podcastId"
IN ( '0gvRpK3dZ', '0NCgwjmYH', '0sU9FAzdi9', '19fy1gdB0', '1g9_w_b8A', '2ot9NRiF-',
'4-QeY-wxf', '5JvSDhV2J', '_7vS9-4W4', '8elKorLioq', '8muzwCJm3', '8S8bt1_0g',
'9pB_N4_WM', 'aFPOVto1VI', 'aoxXZQwgw', 'B1ntOoUHK', 'bCjibv6wo', '-C_GnR3n5SZ',
'clsa3hcFK', 'DgYEXzhJD9', 'DZLEgh8VT', 'EflPiSO1WH', 'FExR_uZN0', 'FGl-0djjxEM',
'FouY-C1nJ', 'ggt-ZCTo5', 'HODor1a3c', 'HRE5xrmvJH', 'ixwAWMf4Y', 'LCc8mE-DW',
'mu4EXUZqniu', 'n22KXRuBq', 'nRMfkLSMK', 'nVxNYJity', 'o2Ca6fMA4D', 'oF2S2nu19W',
'oHZFI-Utk', 'ou733jz6H', 'pQd5t5KRrS', 'Q2Lon3o3U', 'RKZ8UOd4r', 'RoMeME5rU',
'sleVUrbu1yT', 't6wLdw-MW', 'TangmnPTS4', 'tnEdHlRfNRX', 'TYv4idOIUk1', 'UbiFD0bOazM',
'UzACgZAIM', 'V60NEJ_9H', 'Vc91fEsYA', 'vUH7VN37WMW', 'xAT59qMe34', 'XMEvWbyfDpP',
'xoG9N_W-1', 'YnrWvWkkY' )
ORDER BY
"mediaRef"."pastWeekTotalUniquePageviews" DESC
It seems like our queries by category tend to run slow. Maybe we need to add indexes for querying podcast, episodes, or mediaRefs (clips) selected by categoryId, and sorted by past****TotalUniquePageviews or pubDate?