From: Dave Clements <dclements89@gmail.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Fwd: Help required on query performance
Date: Mon, 1 Feb 2010 11:50:22 +1100
Message-ID: <1f30b80c1001311650q264dfda0uf9894cac3b9e0c3e@mail.gmail.com> (raw)
In-Reply-To: <1f30b80c1001290049ub864091r8298fca3daa0eb69@mail.gmail.com>
References: <1f30b80c1001290049ub864091r8298fca3daa0eb69@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dclements89@gmail.com
Subject: Re: Fwd: Help required on query performance
In-Reply-To: <1f30b80c1001311650q264dfda0uf9894cac3b9e0c3e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox