PgHero
No long running queries
Connections healthy 97
Vacuuming healthy
No columns near integer overflow (12 unreadable sequences)
4 invalid indexes
9 duplicate indexes
No suggested indexes
31 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_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);
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);

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
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_idy (taggable_id, taggable_type, tagger_id, 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)

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
24,404 min 4% 415 ms 3,524,701 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
7,574 min 1% 22 ms 20,708,439 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
4,716 min 0.8% 30 ms 9,445,794 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
4,618 min 0.8% 25 ms 10,908,434 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
4,483 min 0.7% 34 ms 8,019,074 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
4,086 min 0.7% 37 ms 6,589,824 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
3,684 min 0.6% 40 ms 5,501,606 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
3,244 min 0.5% 42 ms 4,608,880 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
2,902 min 0.5% 45 ms 3,867,801 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
2,644 min 0.4% 47 ms 3,409,560 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
2,494 min 0.4% 24 ms 6,273,402 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
2,406 min 0.4% 49 ms 2,953,287 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
2,280 min 0.4% 23 ms 5,954,996 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
2,114 min 0.3% 50 ms 2,528,300 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
1,863 min 0.3% 52 ms 2,151,155 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
1,691 min 0.3% 54 ms 1,880,024 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
1,622 min 0.3% 57 ms 1,716,505 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
1,619 min 0.3% 23 ms 4,314,011 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
1,313 min 0.2% 57 ms 1,386,839 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
1,277 min 0.2% 61 ms 1,256,430 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
1,165 min 0.2% 61 ms 1,144,336 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
1,082 min 0.2% 66 ms 986,316 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
1,071 min 0.2% 23 ms 2,756,920 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
1,002 min 0.2% 66 ms 911,041 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
971 min 0.2% 62 ms 946,863 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
900 min 0.1% 55 ms 982,121 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
864 min 0.1% 63 ms 824,769 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,$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 OR ancestry LIKE $41 OR ancestry LIKE $42) AND state NOT IN ($43,$44,$45) ), 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 = $46 AND frozen = $47 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
808 min 0.1% 66 ms 735,915 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,$21,$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 OR ancestry LIKE $41 OR ancestry LIKE $42 OR ancestry LIKE $43 OR ancestry LIKE $44) AND state NOT IN ($45,$46,$47) ), 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 = $48 AND frozen = $49 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
735 min 0.1% 78 ms 565,388 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"."user_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241, $242, $243, $244, $245, $246, $247, $248, $249, $250, $251, $252, $253, $254, $255, $256, $257, $258, $259, $260, $261, $262, $263, $264, $265, $266, $267, $268, $269, $270, $271, $272, $273, $274, $275, $276, $277, $278, $279, $280, $281, $282, $283, $284, $285, $286, $287, $288, $289, $290, $291, $292, $293, $294, $295, $296, $297, $298, $299, $300, $301, $302, $303, $304, $305, $306, $307, $308, $309, $310, $311, $312, $313, $314, $315, $316, $317, $318, $319, $320, $321, $322, $323, $324, $325, $326, $327, $328, $329, $330, $331, $332, $333, $334, $335, $336, $337, $338, $339, $340, $341, $342, $343, $344, $345, $346, $347, $348, $349, $350, $351, $352, $353, $354, $355, $356, $357, $358, $359, $360, $361, $362, $363, $364, $365, $366, $367, $368, $369, $370, $371, $372, $373, $374, $375, $376, $377, $378, $379, $380, $381, $382, $383, $384, $385, $386, $387, $388, $389, $390, $391, $392, $393, $394, $395, $396, $397, $398, $399, $400, $401, $402, $403, $404, $405, $406, $407, $408, $409, $410, $411, $412, $413, $414, $415, $416, $417, $418, $419, $420, $421, $422, $423, $424, $425, $426, $427, $428, $429, $430, $431, $432, $433, $434, $435, $436, $437, $438, $439, $440, $441, $442, $443, $444, $445, $446, $447, $448, $449, $450, $451, $452, $453, $454, $455, $456, $457, $458, $459, $460, $461, $462, $463, $464, $465, $466, $467, $468, $469, $470, $471, $472, $473, $474, $475, $476, $477, $478, $479, $480, $481, $482, $483, $484, $485, $486, $487, $488, $489, $490, $491, $492, $493, $494, $495, $496, $497, $498, $499, $500)
Covered by index on (user_id)
Rows: 40047676
Row progression: 40047676, 155

Row estimates
- user_id (=): 155

Existing indexes
- id PRIMARY
- id, user_id PRIMARY
- ancestry
- ancestry text_pattern_ops
- ancestry varchar_pattern_ops
- anki_deck_id
- category_id
- cloned_from_id
- deck_import_id
- deleted_by_user_at, user_id
- id, state
- library_deck_id
- public_state, visibility
- sharing_token varchar_pattern_ops UNIQUE
- state
- user_id
- user_id
726 min 0.1% 31 ms 1,415,577 ankipro 
WITH LatestCardProgress AS ( SELECT DISTINCT ON (cp.note_id) cp.note_id, cp.next_review_at, cp.learning_state FROM v2.card_progresses AS cp WHERE cp.user_id = $1 AND cp.deck_id IN ($2) ORDER BY cp.note_id, cp.next_review_at ASC NULLS LAST ) SELECT notes.id,notes.deck_id,notes.template_kind,notes.fields, notes.cards_count, notes.css, notes.created_at FROM v2.notes AS notes JOIN LatestCardProgress AS lcp ON lcp.note_id = notes.id WHERE notes.deck_id IN ($3) ORDER BY lcp.next_review_at ASC NULLS LAST, notes.id ASC OFFSET $4 LIMIT $5
717 min 0.1% 65 ms 660,017 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,$21,$22,$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 OR ancestry LIKE $41 OR ancestry LIKE $42 OR ancestry LIKE $43 OR ancestry LIKE $44 OR ancestry LIKE $45 OR ancestry LIKE $46) AND state NOT IN ($47,$48,$49) ), 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 = $50 AND frozen = $51 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