Improve querying for podcasts / episodes / mediaRefs by category and sorted by popularity or date

[mitchdowney]

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?

[Trolladactyl]

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