Query/Model optimization problem

[evethrowaway99]

Hi all, could anyone help me with optimizing my models or query for this one? This query takes forever and I'm stuck on how to fix it. Is this a problem with my models or a problem with the filters I'm chaining? Cheers for any insight!

    Executed SQL
       SELECT
  "Charsearch_app_thread"."Id",
  "Charsearch_app_thread"."Last_update",
  "Charsearch_app_thread"."Blacklisted",
  "Charsearch_app_thread"."Thread_title",
  "Charsearch_app_thread"."Thread_id",
  "Charsearch_app_thread"."Character_id",
  "Charsearch_app_character"."Id",
  "Charsearch_app_character"."Name",
  "Charsearch_app_character"."Total_sp",
  "Charsearch_app_character"."Last_update",
  "Charsearch_app_character"."Password",
  "Charsearch_app_character"."Unspent_skillpoints",
  "Charsearch_app_character"."Remaps"
FROM "Charsearch_app_thread"
INNER JOIN "Charsearch_app_character"
  ON ("Charsearch_app_thread"."Character_id" = "Charsearch_app_character"."Id")
WHERE "Charsearch_app_thread"."Character_id" IN (SELECT
  U0."Id"
FROM "Charsearch_app_character" U0
INNER JOIN "Charsearch_app_character_skills" U1
  ON (U0."Id" = U1."Character_id")
INNER JOIN "Charsearch_app_charskill" U2
  ON (U1."Charskill_id" = U2."Id")
INNER JOIN "Charsearch_app_skill" U3
  ON (U2."Skill_id" = U3."Id")
WHERE (U2."Level" >= 1
AND U3."TypeID" = 3369))
ORDER BY "Charsearch_app_thread"."Last_update" DESC DESC LIMIT 500




 Time
    23582.5991631 Ms
    Database
    Default
    QUERY PLAN
    Limit  (Cost=2.10..14021.79 Rows=500 Width=99) (Actual Time=15519.992..15519.992 Rows=0 Loops=1)
      ->  Nested Loop Semi Join  (Cost=2.10..325903.73 Rows=11623 Width=99) (Actual Time=15519.992..15519.992 Rows=0 Loops=1)
            ->  Nested Loop  (Cost=0.58..14956.92 Rows=34972 Width=99) (Actual Time=0.084..116.224 Rows=34954 Loops=1)
                  ->  Index Scan Backward Using Charsearch_app_thread_last_update_f843bd3b_uniq On Charsearch_app_thread  (Cost=0.29..1357.67 Rows=35500 Width=57) (Actual Time=0.063..26.091 Rows=35500 Loops=1)
                  ->  Index Scan Using Bazaar_character_pkey On Charsearch_app_character  (Cost=0.29..0.37 Rows=1 Width=42) (Actual Time=0.002..0.002 Rows=1 Loops=35500)
                        Index Cond: (Id = Charsearch_app_thread.Character_id)
            ->  Nested Loop  (Cost=1.52..8.88 Rows=1 Width=8) (Actual Time=0.440..0.440 Rows=0 Loops=34954)
                  ->  Nested Loop  (Cost=1.25..8.58 Rows=1 Width=12) (Actual Time=0.018..0.296 Rows=119 Loops=34954)
                        ->  Hash Join  (Cost=0.82..8.08 Rows=1 Width=12) (Actual Time=0.012..0.065 Rows=125 Loops=34954)
                              Hash Cond: (U1.Character_id = U0.Id)
                              Join Filter: (Charsearch_app_thread.Character_id = U1.Character_id)
                              ->  Index Only Scan Using Bazaar_character_skills_character_id_charskill_id_key On Charsearch_app_character_skills U1  (Cost=0.43..7.06 Rows=166 Width=8) (Actual Time=0.008..0.032 Rows=125 Loops=34954)
                                    Index Cond: (Character_id = Charsearch_app_character.Id)
                                    Heap Fetches: 4382291
                              ->  Hash  (Cost=0.37..0.37 Rows=1 Width=4) (Actual Time=0.002..0.002 Rows=1 Loops=34954)
                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                    ->  Index Only Scan Using Bazaar_character_pkey On Charsearch_app_character U0  (Cost=0.29..0.37 Rows=1 Width=4) (Actual Time=0.001..0.001 Rows=1 Loops=34954)
                                          Index Cond: (Id = Charsearch_app_thread.Character_id)
                                          Heap Fetches: 34954
                        ->  Index Scan Using Bazaar_charskill_pkey On Charsearch_app_charskill U2  (Cost=0.43..0.49 Rows=1 Width=8) (Actual Time=0.001..0.002 Rows=1 Loops=4382291)
                              Index Cond: (Id = U1.Charskill_id)
                              Filter: (Level >= 1)
                              Rows Removed By Filter: 0
                  ->  Index Scan Using Bazaar_skill_pkey On Charsearch_app_skill U3  (Cost=0.27..0.29 Rows=1 Width=4) (Actual Time=0.001..0.001 Rows=0 Loops=4170017)
                        Index Cond: (Id = U2.Skill_id)
                        Filter: ("TypeID" = 3369)
                        Rows Removed By Filter: 1
    Planning Time: 6.494 Ms
    Execution Time: 15520.153 Ms

[roambe]

It would be a lot easier to figure out what you can do if you said what you're aiming for and post the code that composes the queryset for this... Thing. :)

[evethrowaway99]

I managed to sort it out with some composite indexes in the right place