Skip to content
Snippets Groups Projects
  1. Mar 08, 2023
  2. Mar 06, 2023
  3. Mar 04, 2023
  4. Mar 03, 2023
  5. Feb 22, 2023
  6. Feb 20, 2023
  7. Feb 18, 2023
  8. Feb 17, 2023
  9. Feb 10, 2023
  10. Feb 08, 2023
  11. Feb 07, 2023
  12. Feb 01, 2023
  13. Jan 18, 2023
  14. Jan 11, 2023
  15. Jan 05, 2023
  16. Dec 15, 2022
  17. Dec 06, 2022
  18. Dec 01, 2022
  19. Nov 27, 2022
    • Kaspar V's avatar
      refactor(vacuum statuses): reduce amount of db queries and load for each query... · 47f0d702
      Kaspar V authored
      refactor(vacuum statuses): reduce amount of db queries and load for each query - improve performance (#21487)
      
      * refactor(statuses_vacuum): remove dead code - unused
      
      Method is not called inside class and private.
      Clean up dead code.
      
      * refactor(statuses_vacuum): make retention_period present test explicit
      
      This private method only hides functionality.
      It is best practice to be as explicit as possible.
      
      * refactor(statuses_vacuum): improve query performance
      
      - fix statuses_scope having sub-select for Account.remote scope by
        `joins(:account).merge(Account.remote)`
      - fix statuses_scope unnecessary use of `Status.arel_table[:id].lt`
        because it is inexplicit, bad practice and even slower than normal
        `.where('statuses.id < ?'`
      - fix statuses_scope remove select(:id, :visibility) for having reusable
        active record query batches (no re queries)
      - fix vacuum_statuses! to use in_batches instead of find_in_batches,
        because in_batches delivers a full blown active record query result,
        in stead of an array - no requeries necessary
      - send(:unlink_from_conversations) not to perform another db query, but
        reuse the in_batches result instead.
      - remove now obsolete remove_from_account_conversations method
      - remove_from_search_index uses array of ids, instead of mapping
        the ids from an array - this should be more efficient
      - use the in_batches scope to call delete_all, instead of running
        another db query for this - because it is again more efficient
      - add TODO comment for calling models private method with send
      
      * refactor(status): simplify unlink_from_conversations
      
      - add `has_many through:` relation mentioned_accounts
      - use model scope local instead of method call `Status#local?`
      - more readable add account to inbox_owners when account.local?
      
      * refactor(status): searchable_by way less sub selects
      
      These queries all included a sub-select. Doing the same with a joins
      should be more efficient.
      Since this method does 5 such queries, this should be significant,
      since it technically halves the query count.
      
      This is how it was:
      
      ```ruby
      [3] pry(main)> Status.first.mentions.where(account: Account.local, silent: false).explain
        Status Load (1.6ms)  SELECT "statuses".* FROM "statuses" WHERE "statuses"."deleted_at" IS NULL ORDER BY "statuses"."id" DESC LIMIT $1  [["LIMIT", 1]]
        Mention Load (1.5ms)  SELECT "mentions".* FROM "mentions" WHERE "mentions"."status_id" = $1 AND "mentions"."account_id" IN (SELECT "accounts"."id" FROM "accounts" WHERE "accounts"."domain" IS NULL) AND "mentions"."silent" = $2  [["status_id", 109382923142288414], ["silent", false]]
      => EXPLAIN for: SELECT "mentions".* FROM "mentions" WHERE "mentions"."status_id" = $1 AND "mentions"."account_id" IN (SELECT "accounts"."id" FROM "accounts" WHERE "accounts"."domain" IS NULL) AND "mentions"."silent" = $2 [["status_id", 109382923142288414], ["silent", false]]
                                                          QUERY PLAN
      ------------------------------------------------------------------------------------------------------------------
       Nested Loop  (cost=0.15..23.08 rows=1 width=41)
         ->  Seq Scan on accounts  (cost=0.00..10.90 rows=1 width=8)
               Filter: (domain IS NULL)
         ->  Index Scan using index_mentions_on_account_id_and_status_id on mentions  (cost=0.15..8.17 rows=1 width=41)
               Index Cond: ((account_id = accounts.id) AND (status_id = '109382923142288414'::bigint))
               Filter: (NOT silent)
      (6 rows)
      ```
      
      This is how it is with this change:
      
      ```ruby
      [4] pry(main)> Status.first.mentions.joins(:account).merge(Account.local).active.explain
        Status Load (1.7ms)  SELECT "statuses".* FROM "statuses" WHERE "statuses"."deleted_at" IS NULL ORDER BY "statuses"."id" DESC LIMIT $1  [["LIMIT", 1]]
        Mention Load (0.7ms)  SELECT "mentions".* FROM "mentions" INNER JOIN "accounts" ON "accounts"."id" = "mentions"."account_id" WHERE "mentions"."status_id" = $1 AND "accounts"."domain" IS NULL AND "mentions"."silent" = $2  [["status_id", 109382923142288414], ["silent", false]]
      => EXPLAIN for: SELECT "mentions".* FROM "mentions" INNER JOIN "accounts" ON "accounts"."id" = "mentions"."account_id" WHERE "mentions"."status_id" = $1 AND "accounts"."domain" IS NULL AND "mentions"."silent" = $2 [["status_id", 109382923142288414], ["silent", false]]
                                                          QUERY PLAN
      ------------------------------------------------------------------------------------------------------------------
       Nested Loop  (cost=0.15..23.08 rows=1 width=41)
         ->  Seq Scan on accounts  (cost=0.00..10.90 rows=1 width=8)
               Filter: (domain IS NULL)
         ->  Index Scan using index_mentions_on_account_id_and_status_id on mentions  (cost=0.15..8.17 rows=1 width=41)
               Index Cond: ((account_id = accounts.id) AND (status_id = '109382923142288414'::bigint))
               Filter: (NOT silent)
      (6 rows)
      ```
      Unverified
      47f0d702
  20. Nov 14, 2022
  21. Nov 11, 2022
  22. Nov 10, 2022
  23. Nov 08, 2022
Loading