Fwd: Help required on query performance

From: Dave Clements <dclements89(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Fwd: Help required on query performance
Date: 2010-02-01 00:50:22
Message-ID: 1f30b80c1001311650q264dfda0uf9894cac3b9e0c3e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello, I have this query in my system which takes around 2.5 seconds
to run. I have diagnosed that the problem is actually a hashjoin on
perm and s_ast_role tables. Is there a way I can avoid that join? I
just want to change the
query and no environment change.

SELECT
 ai.aid,
 SUM(ai.score) as search_score
FROM
 sq_sch_idx ai,
 (
   SELECT
     a.aid
   FROM
     t a
     INNER JOIN slink l ON l.minorid = a.aid
     INNER JOIN slink_tree t ON t.linkid = l.linkid
   WHERE
     (t.treeid LIKE '0005%')
     AND a.status  >= '16'
     AND a.type_code IN
     (
       SELECT type_code FROM t_typ_inhd WHERE inhd_type_code IN
('page') OR type_code IN ('file', 'page_rss_feed')
     )
     AND a.aid IN
     (
       SELECT
         p.aid
       FROM
         perm p LEFT JOIN s_ast_role r ON (p.userid = r.roleid)
       WHERE
         (
           p.userid IN ('7') OR r.userid IN ('7')
         ) AND
         (
           (
             p.permission = '1'
             AND
             (
               (
                 p.userid <> '7' OR
                 (r.userid IS NULL OR r.userid <> '7')
               )
               OR
               (p.userid = '7' AND granted = '1')
               OR
               (r.userid = '7' AND granted = '1')
             )
           )
           OR
           (p.permission > '1' AND p.granted = '1')
         )
         AND a.aid=p.aid
       GROUP BY
         p.aid
       HAVING
         MIN(p.granted) <> '0'
     )
 ) asset_check
WHERE
 (ai.aid=asset_check.aid)
 AND (ai.value LIKE '%download%')
GROUP BY
 ai.aid
;

Thanks

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Scott Marlowe 2010-02-01 00:54:51 Re: Fwd: Help required on query performance
Previous Message Tom Lane 2010-01-31 21:07:46 Re: Howto have a unique restraint on UPPER (textfield)