Badly written SQL is causing AAC to slow down when searching for Tags

XMLWordPrintable

    • Severity 3 - Minor

      I noticed that AAC was very slow when I searched for a tag. I traced the executing SQL to this:

      SELECT
  COUNT(*)
FROM
  "forum_node"
WHERE
  ("forum_node"."node_type" = E'answer'
  AND "forum_node"."parent_id" IN
                                   (
                                   SELECT DISTINCT
                                     U0."id"
                                   FROM
                                     "forum_node" U0 ,
                                     "forum_rootnode_doc"
                                   WHERE
                                     (U0."node_type" = E'question'
                                     AND "forum_rootnode_doc"."node_id" = "forum_node"."id"
                                     AND ("forum_rootnode_doc"."document" @@ to_tsquery('english', E'')
                                       OR "forum_node"."title" ILIKE E'%%')
                                     AND U0."node_type" = E'question'
                                     AND (U0."tagnames" ~* E'(^| )greenhopper( |$)' )
                                     AND NOT (U0."state_string"::text LIKE E'%(deleted)%' ))))

      This takes 16 seconds to execute, making a very bad user experience.

      I examined the SQL and discovered what I think is an error in the coding. When "forum_node" is referenced within the subselect (IN), it appears to be incorrectly referencing "forum_node" from the main level of the query, rather than "forum_node" in the subquery (which has the name of U0). This happens in two places:

      • "forum_rootnode_doc"."node_id" = "forum_node"."id"
      • "forum_node"."title" ILIKE E'%%'

      When I changed these references from "forum_node" to U0, the query executed in 0.4 seconds and returned the same result. The reason for the speed-up is that the current query is causing the sub-clause to execute many times, whereas the fixed query only requires it to run once.

      The challenge now is for you to figure out where this is being called from the OSQA code. Let me know if I can be of assistance!

            Assignee:
            eternicode
            Reporter:
            JohnA
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: