PgHero
Healthy replication lag 0 ms
No long running queries
Connections healthy 50
Vacuuming healthy
No columns near integer overflow (12 unreadable sequences)
4 invalid indexes
10 duplicate indexes
No suggested indexes
30 slow queries

Invalid Indexes

These indexes exist, but can’t be used. You should recreate them.

Name
idx_card_progresses_deck_id_id v2 
DROP INDEX CONCURRENTLY "v2.idx_card_progresses_deck_id_id";
CREATE INDEX CONCURRENTLY idx_card_progresses_deck_id_id ON v2.card_progresses USING btree (deck_id, id);
card_progresses_user_id_id_idx v2 
DROP INDEX CONCURRENTLY "v2.card_progresses_user_id_id_idx";
CREATE INDEX CONCURRENTLY card_progresses_user_id_id_idx ON v2.card_progresses USING btree (user_id, id);
trgm_idx_notes_front_side v2 
DROP INDEX CONCURRENTLY "v2.trgm_idx_notes_front_side";
CREATE INDEX CONCURRENTLY trgm_idx_notes_front_side ON v2.notes USING gin (((fields ->> 'front_side'::text)) gin_trgm_ops);
trgm_idx_notes_back_side v2 
DROP INDEX CONCURRENTLY "v2.trgm_idx_notes_back_side";
CREATE INDEX CONCURRENTLY trgm_idx_notes_back_side ON v2.notes USING gin (((fields ->> 'back_side'::text)) gin_trgm_ops);

Duplicate Indexes

These indexes exist, but aren’t needed. Remove them for faster writes. 

rails generate migration remove_unneeded_indexes

And paste

remove_index :motor_alert_locks, name: "index_motor_alert_locks_on_alert_id", column: :alert_id
remove_index :notes, name: "index_notes_on_id_asc_and_deck_id", column: [:id, :deck_id]
remove_index :streak_day_freezes, name: "index_streak_day_freezes_on_streak_id", column: :streak_id
remove_index :streak_day_freezes, name: "index_streak_day_freezes_on_user_id", column: :user_id
remove_index :taggings, name: "index_taggings_on_tag_id", column: :tag_id
remove_index :taggings, name: "index_taggings_on_taggable_id", column: :taggable_id
remove_index :taggings, name: "index_taggings_on_taggable_type", column: :taggable_type
remove_index :taggings, name: "index_taggings_on_tagger_id", column: :tagger_id
remove_index :user_social_links, name: "index_user_social_links_on_user_id", column: :user_id
remove_index :users, name: "index_users_on_analytics_id", column: :analytics_id
Details
On motor_alert_locks 
index_motor_alert_locks_on_alert_id (alert_id)
is covered by 
index_motor_alert_locks_on_alert_id_and_lock_timestamp (alert_id, lock_timestamp)
On notes 
index_notes_on_id_asc_and_deck_id (id, deck_id)
is covered by 
notes_pkey (id, deck_id)
On streak_day_freezes 
index_streak_day_freezes_on_streak_id (streak_id)
is covered by 
index_streak_day_freezes_on_streak_id_and_freeze_date (streak_id, freeze_date)
On streak_day_freezes 
index_streak_day_freezes_on_user_id (user_id)
is covered by 
index_streak_day_freezes_on_user_id_and_freeze_date (user_id, freeze_date)
On taggings 
index_taggings_on_tag_id (tag_id)
is covered by 
taggings_idx (tag_id, taggable_id, taggable_type, context, tagger_id, tagger_type)
On taggings 
index_taggings_on_taggable_id (taggable_id)
is covered by 
taggings_taggable_context_idx (taggable_id, taggable_type, context)
On taggings 
index_taggings_on_taggable_type (taggable_type)
is covered by 
index_taggings_on_taggable_type_and_taggable_id (taggable_type, taggable_id)
On taggings 
index_taggings_on_tagger_id (tagger_id)
is covered by 
index_taggings_on_tagger_id_and_tagger_type (tagger_id, tagger_type)
On user_social_links 
index_user_social_links_on_user_id (user_id)
is covered by 
index_user_social_links_on_user_id_and_social_provider_id (user_id, social_provider_id)
On users 
index_users_on_analytics_id (analytics_id)
is covered by 
idx_users_analytics_id (analytics_id)

Slow Queries

Slow queries take 20 ms or more on average and have been called at least 100 times.

Explain queries to see where to add indexes.

Total Time Average Time Calls
1,716 min 2% 361 ms 285,212 ankipro 
SELECT "v2"."decks"."id", "v2"."decks"."deck_collaborators_count", "v2"."decks"."deleted_by_user_at", "v2"."decks"."downvotes", "v2"."decks"."import_error_message", "v2"."decks"."name", "v2"."decks"."last_reset_at", "v2"."decks"."public_state", "v2"."decks"."sharing_token", "v2"."decks"."state", "v2"."decks"."visibility", "v2"."decks"."upvotes", "v2"."decks"."user_id", "v2"."decks"."created_at", "v2"."decks"."updated_at", "v2"."decks"."ancestry", "v2"."decks"."slug", "v2"."decks"."seo_description", "v2"."decks"."description", "v2"."decks"."category", "v2"."decks"."cloned_times_count", "v2"."decks"."is_indexable", "v2"."decks"."study_score", "v2"."decks"."library_deck_id", "v2"."decks"."file_hash", "v2"."decks"."cloned_from_id", "v2"."decks"."anki_deck_id", "v2"."decks"."studiers_count", "v2"."decks"."deck_id", "v2"."decks"."moved_at", "v2"."decks"."source", "v2"."decks"."reviews_count", "v2"."decks"."duplicated_from_id", "v2"."decks"."no_cards_limit", "v2"."decks"."category_id" FROM "v2"."decks" WHERE "v2"."decks"."public_state" = $1 AND "v2"."decks"."ancestry" = $2 AND "v2"."decks"."sharing_token" = $3 LIMIT $4
702 min 0.9% 21 ms 2,035,372 ankipro 
WITH cte AS ( SELECT id, deck_id, (sum(v2.notes.cards_count) over (order by v2.notes.id)) AS custom_offset FROM v2.notes WHERE $1=$2 AND v2.notes.id IN ($3) ORDER BY id ASC LIMIT $4 ) SELECT * FROM v2.notes AS notes INNER JOIN cte AS c ON c.id = notes.id AND c.deck_id = notes.deck_id AND c.custom_offset > $5 WHERE $6=$7 ORDER BY notes.id ASC LIMIT $8
640 min 0.8% 932 ms 41,164 ankipro 
SELECT DISTINCT "users"."id" AS alias_0, "users"."id" FROM "users" LEFT OUTER JOIN "devices" ON "devices"."user_id" = "users"."id" WHERE (last_seen_time > $1 OR last_seen_time IS NULL) AND "devices"."firebase_token" IS NOT NULL AND "users"."timezone_offset" IN ($2, $3, $4, $5, $6, $7) AND "users"."id" > $8 ORDER BY "users"."id" ASC LIMIT $9
546 min 0.7% 20 ms 1,627,813 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2) OR ancestry LIKE $3 OR ancestry LIKE $4) AND state NOT IN ($5,$6,$7) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $8 AND frozen = $9 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
524 min 0.7% 32 ms 985,678 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4) OR ancestry LIKE $5 OR ancestry LIKE $6 OR ancestry LIKE $7 OR ancestry LIKE $8) AND state NOT IN ($9,$10,$11) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $12 AND frozen = $13 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
512 min 0.7% 26 ms 1,166,466 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3) OR ancestry LIKE $4 OR ancestry LIKE $5 OR ancestry LIKE $6) AND state NOT IN ($7,$8,$9) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $10 AND frozen = $11 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
495 min 0.6% 35 ms 837,620 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5) OR ancestry LIKE $6 OR ancestry LIKE $7 OR ancestry LIKE $8 OR ancestry LIKE $9 OR ancestry LIKE $10) AND state NOT IN ($11,$12,$13) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $14 AND frozen = $15 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
450 min 0.6% 38 ms 702,706 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6) OR ancestry LIKE $7 OR ancestry LIKE $8 OR ancestry LIKE $9 OR ancestry LIKE $10 OR ancestry LIKE $11 OR ancestry LIKE $12) AND state NOT IN ($13,$14,$15) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $16 AND frozen = $17 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
394 min 0.5% 41 ms 581,637 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7) OR ancestry LIKE $8 OR ancestry LIKE $9 OR ancestry LIKE $10 OR ancestry LIKE $11 OR ancestry LIKE $12 OR ancestry LIKE $13 OR ancestry LIKE $14) AND state NOT IN ($15,$16,$17) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $18 AND frozen = $19 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
357 min 0.5% 43 ms 492,325 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8) OR ancestry LIKE $9 OR ancestry LIKE $10 OR ancestry LIKE $11 OR ancestry LIKE $12 OR ancestry LIKE $13 OR ancestry LIKE $14 OR ancestry LIKE $15 OR ancestry LIKE $16) AND state NOT IN ($17,$18,$19) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $20 AND frozen = $21 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
335 min 0.4% 48 ms 420,736 ankipro 
SELECT "v2"."notes"."id", "v2"."notes"."template_kind", "v2"."notes"."fields", "v2"."notes"."cards_count", "v2"."notes"."deck_id", "v2"."notes"."css" FROM "v2"."notes" WHERE "v2"."notes"."deck_id" = $1 ORDER BY "v2"."notes"."id" ASC LIMIT $2
314 min 0.4% 46 ms 407,320 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9) OR ancestry LIKE $10 OR ancestry LIKE $11 OR ancestry LIKE $12 OR ancestry LIKE $13 OR ancestry LIKE $14 OR ancestry LIKE $15 OR ancestry LIKE $16 OR ancestry LIKE $17 OR ancestry LIKE $18) AND state NOT IN ($19,$20,$21) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $22 AND frozen = $23 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
278 min 0.4% 47 ms 351,443 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) OR ancestry LIKE $11 OR ancestry LIKE $12 OR ancestry LIKE $13 OR ancestry LIKE $14 OR ancestry LIKE $15 OR ancestry LIKE $16 OR ancestry LIKE $17 OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20) AND state NOT IN ($21,$22,$23) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $24 AND frozen = $25 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
262 min 0.3% 49 ms 323,188 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) OR ancestry LIKE $12 OR ancestry LIKE $13 OR ancestry LIKE $14 OR ancestry LIKE $15 OR ancestry LIKE $16 OR ancestry LIKE $17 OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22) AND state NOT IN ($23,$24,$25) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $26 AND frozen = $27 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
249 min 0.3% 23 ms 650,076 ankipro 
WITH cte AS ( SELECT id, deck_id, (sum(v2.notes.cards_count) over (order by v2.notes.id)) AS custom_offset FROM v2.notes WHERE $1=$2 AND v2.notes.id IN ($3,$4,$5,$6,$7) ORDER BY id ASC LIMIT $8 ) SELECT * FROM v2.notes AS notes INNER JOIN cte AS c ON c.id = notes.id AND c.deck_id = notes.deck_id AND c.custom_offset > $9 WHERE $10=$11 ORDER BY notes.id ASC LIMIT $12
229 min 0.3% 52 ms 265,626 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) OR ancestry LIKE $13 OR ancestry LIKE $14 OR ancestry LIKE $15 OR ancestry LIKE $16 OR ancestry LIKE $17 OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24) AND state NOT IN ($25,$26,$27) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $28 AND frozen = $29 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
222 min 0.3% 112 ms 118,763 ankipro 
SELECT "v2"."notes"."id", "v2"."notes"."template_kind", "v2"."notes"."fields", "v2"."notes"."cards_count", "v2"."notes"."deck_id", "v2"."notes"."created_at", "v2"."notes"."updated_at", "v2"."notes"."library_note_id", "v2"."notes"."library_card_id", "v2"."notes"."anki_id", "v2"."notes"."css" FROM "v2"."notes" WHERE "v2"."notes"."deck_id" = $1 ORDER BY "v2"."notes"."id" ASC LIMIT $2
208 min 0.3% 22 ms 570,322 ankipro 
WITH cte AS ( SELECT id, deck_id, (sum(v2.notes.cards_count) over (order by v2.notes.id)) AS custom_offset FROM v2.notes WHERE $1=$2 AND v2.notes.id IN ($3,$4,$5) ORDER BY id ASC LIMIT $6 ) SELECT * FROM v2.notes AS notes INNER JOIN cte AS c ON c.id = notes.id AND c.deck_id = notes.deck_id AND c.custom_offset > $7 WHERE $8=$9 ORDER BY notes.id ASC LIMIT $10
199 min 0.3% 53 ms 225,767 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) OR ancestry LIKE $14 OR ancestry LIKE $15 OR ancestry LIKE $16 OR ancestry LIKE $17 OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26) AND state NOT IN ($27,$28,$29) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $30 AND frozen = $31 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
194 min 0.2% 138 ms 84,057 ankipro 
WITH cte AS ( SELECT id, deck_id, (sum(v2.notes.cards_count) over (order by v2.notes.id)) AS custom_offset FROM v2.notes WHERE $1=$2 AND v2.notes.deck_id IN ($3) ORDER BY id ASC LIMIT $4 ) SELECT * FROM v2.notes AS notes INNER JOIN cte AS c ON c.id = notes.id AND c.deck_id = notes.deck_id AND c.custom_offset > $5 WHERE $6=$7 ORDER BY notes.id ASC LIMIT $8
185 min 0.2% 55 ms 203,232 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14) OR ancestry LIKE $15 OR ancestry LIKE $16 OR ancestry LIKE $17 OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26 OR ancestry LIKE $27 OR ancestry LIKE $28) AND state NOT IN ($29,$30,$31) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $32 AND frozen = $33 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
183 min 0.2% 59 ms 184,662 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15) OR ancestry LIKE $16 OR ancestry LIKE $17 OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26 OR ancestry LIKE $27 OR ancestry LIKE $28 OR ancestry LIKE $29 OR ancestry LIKE $30) AND state NOT IN ($31,$32,$33) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $34 AND frozen = $35 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
156 min 0.2% 21 ms 437,613 ankipro 
WITH cte AS ( SELECT id, deck_id, (sum(v2.notes.cards_count) over (order by v2.notes.id)) AS custom_offset FROM v2.notes WHERE $1=$2 AND v2.notes.id IN ($3,$4) ORDER BY id ASC LIMIT $5 ) SELECT * FROM v2.notes AS notes INNER JOIN cte AS c ON c.id = notes.id AND c.deck_id = notes.deck_id AND c.custom_offset > $6 WHERE $7=$8 ORDER BY notes.id ASC LIMIT $9
154 min 0.2% 74 ms 124,122 ankipro 
SELECT DISTINCT "users"."id" AS alias_0, "users"."id" FROM "users" LEFT OUTER JOIN "subscriptions" ON "subscriptions"."user_id" = "users"."id" WHERE "subscriptions"."expires_at" < $1 AND "users"."id" > $2 ORDER BY "users"."id" ASC LIMIT $3
134 min 0.2% 57 ms 140,457 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16) OR ancestry LIKE $17 OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26 OR ancestry LIKE $27 OR ancestry LIKE $28 OR ancestry LIKE $29 OR ancestry LIKE $30 OR ancestry LIKE $31 OR ancestry LIKE $32) AND state NOT IN ($33,$34,$35) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $36 AND frozen = $37 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
128 min 0.2% 60 ms 127,138 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17) OR ancestry LIKE $18 OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26 OR ancestry LIKE $27 OR ancestry LIKE $28 OR ancestry LIKE $29 OR ancestry LIKE $30 OR ancestry LIKE $31 OR ancestry LIKE $32 OR ancestry LIKE $33 OR ancestry LIKE $34) AND state NOT IN ($35,$36,$37) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $38 AND frozen = $39 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
115 min 0.1% 59 ms 116,556 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18) OR ancestry LIKE $19 OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26 OR ancestry LIKE $27 OR ancestry LIKE $28 OR ancestry LIKE $29 OR ancestry LIKE $30 OR ancestry LIKE $31 OR ancestry LIKE $32 OR ancestry LIKE $33 OR ancestry LIKE $34 OR ancestry LIKE $35 OR ancestry LIKE $36) AND state NOT IN ($37,$38,$39) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $40 AND frozen = $41 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
112 min 0.1% 63 ms 105,419 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19) OR ancestry LIKE $20 OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26 OR ancestry LIKE $27 OR ancestry LIKE $28 OR ancestry LIKE $29 OR ancestry LIKE $30 OR ancestry LIKE $31 OR ancestry LIKE $32 OR ancestry LIKE $33 OR ancestry LIKE $34 OR ancestry LIKE $35 OR ancestry LIKE $36 OR ancestry LIKE $37 OR ancestry LIKE $38) AND state NOT IN ($39,$40,$41) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $42 AND frozen = $43 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
109 min 0.1% 66 ms 99,939 ankipro 
WITH subtree_decks AS ( SELECT id, ancestry FROM v2.decks WHERE (id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20) OR ancestry LIKE $21 OR ancestry LIKE $22 OR ancestry LIKE $23 OR ancestry LIKE $24 OR ancestry LIKE $25 OR ancestry LIKE $26 OR ancestry LIKE $27 OR ancestry LIKE $28 OR ancestry LIKE $29 OR ancestry LIKE $30 OR ancestry LIKE $31 OR ancestry LIKE $32 OR ancestry LIKE $33 OR ancestry LIKE $34 OR ancestry LIKE $35 OR ancestry LIKE $36 OR ancestry LIKE $37 OR ancestry LIKE $38 OR ancestry LIKE $39 OR ancestry LIKE $40) AND state NOT IN ($41,$42,$43) ), min_next_reviews AS ( SELECT deck_id, MIN(next_review_at) AS min_next_review_at FROM v2.card_progresses WHERE deck_id IN (SELECT id FROM subtree_decks) AND user_id = $44 AND frozen = $45 GROUP BY deck_id ) SELECT sd.id AS deck_id, sd.ancestry, mn.min_next_review_at FROM subtree_decks sd LEFT JOIN min_next_reviews mn ON sd.id = mn.deck_id
107 min 0.1% 22 ms 288,134 ankipro 
WITH cte AS ( SELECT id, deck_id, (sum(v2.notes.cards_count) over (order by v2.notes.id)) AS custom_offset FROM v2.notes WHERE $1=$2 AND v2.notes.id IN ($3,$4,$5,$6) ORDER BY id ASC LIMIT $7 ) SELECT * FROM v2.notes AS notes INNER JOIN cte AS c ON c.id = notes.id AND c.deck_id = notes.deck_id AND c.custom_offset > $8 WHERE $9=$10 ORDER BY notes.id ASC LIMIT $11