Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E3A32632B8C for ; Sun, 31 Jan 2010 21:03:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 25555-09 for ; Mon, 1 Feb 2010 01:02:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-px0-f194.google.com (mail-px0-f194.google.com [209.85.216.194]) by mail.postgresql.org (Postfix) with ESMTP id 0D861632BF6 for ; Sun, 31 Jan 2010 21:02:57 -0400 (AST) Received: by pxi32 with SMTP id 32so3341110pxi.15 for ; Sun, 31 Jan 2010 17:02:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:reply-to:received :in-reply-to:references:from:date:x-google-sender-auth:message-id :subject:to:cc:content-type:content-transfer-encoding; bh=rz6ODusNcp4dIvTplp70UZZqgxZAZsJGFy6Elw3RIkM=; b=xbLXYJz03ux2y7cRmvQ8HBDgXOWlcPVIqhIf72vtdw+GJfMxGHvIg+6LUPuCJfTMCe gtMOQ3lTcfKuZYZ9g9H4itlIpHSRuxOcaMvA4z2tb5wd82CWn5ggHswHkIOAWQu69eS0 iFYJwfFx3aqMrEvvYa216hMRAA01S8ttVjhm4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:reply-to:in-reply-to:references:from:date :x-google-sender-auth:message-id:subject:to:cc:content-type :content-transfer-encoding; b=Nd4VovyjxEfiqoE5yCdv4owhFJUg0HQprzP3qNb0c1kS6h4sETqyzUdfDmJ1sg6Egt WZZdIuwjmYzooHWBHbauMP5UnUxDaaRDnGZ/keLme3VR6u8Bd/u7s7jm7gyKoJcBQz3Y K8VES46G7GyPEu/7TzW6pQYrtE2Qd5hQwAM00= MIME-Version: 1.0 Reply-To: dclements89@gmail.com Received: by 10.114.2.19 with SMTP id 19mr2592789wab.211.1264986176084; Sun, 31 Jan 2010 17:02:56 -0800 (PST) In-Reply-To: References: <1f30b80c1001290049ub864091r8298fca3daa0eb69@mail.gmail.com> <1f30b80c1001311650q264dfda0uf9894cac3b9e0c3e@mail.gmail.com> From: Dave Clements Date: Mon, 1 Feb 2010 12:02:36 +1100 X-Google-Sender-Auth: 51b3c8f62aefe6bd Message-ID: <1f30b80c1001311702g3ed5e895gc6e504ffe23d7840@mail.gmail.com> Subject: Re: Fwd: Help required on query performance To: Scott Marlowe Cc: pgsql-sql Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/58 X-Sequence-Number: 33594 Hi, following the output from explain analyze. ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----- HashAggregate (cost=3D38145.19..38145.20 rows=3D1 width=3D149) (actual time=3D2635.965..2636.086 rows=3D243 loops=3D1) -> Nested Loop (cost=3D15.00..38145.18 rows=3D1 width=3D149) (actual time=3D4.417..2635.086 rows=3D598 loops=3D1) -> Nested Loop (cost=3D4.13..37993.95 rows=3D8 width=3D153) (actual time=3D0.781..310.579 rows=3D975 loops=3D1) -> Nested Loop IN Join (cost=3D0.00..37260.18 rows=3D1 width=3D168) (actual time=3D0.747..298.686 rows=3D532 loops=3D1) -> Nested Loop (cost=3D0.00..37216.90 rows=3D2 width=3D236) (actual time=3D0.731..292.449 rows=3D563 loops=3D1) -> Seq Scan on sq_sch_idx ai (cost=3D0.00..20921.47 rows=3D10 width=3D149) (actual time=3D0.616..260.601 rows=3D677 loops=3D1) Filter: ((value)::text ~~ '%download%'::te= xt) -> Index Scan using sq_ast_pkey on sq_ast a (cost=3D0.00..1629.53 rows=3D1 width=3D87) (actual time=3D0.045..0.046 rows=3D1 loops=3D677) Index Cond: (("outer".assetid)::text =3D (a.assetid)::text) Filter: ((status >=3D 16::smallint) AND (subplan)) SubPlan -> HashAggregate (cost=3D1623.50..1623.52 rows=3D1 width=3D150) (actual time=3D0.031..0.031 rows=3D1 loops=3D586) Filter: (min("granted") <> '0'::bp= char) -> Hash Left Join (cost=3D21.32..1619.40 rows=3D820 width=3D150) (actual time=3D0.023..0.028 rows=3D1 loops=3D586) Hash Cond: (("outer".userid)::text =3D ("inner".roleid)::text) Filter: (((("outer".userid)::text =3D '7'::text) OR (("inner".userid)::text =3D '7'::text)) AND ((("outer".permission =3D 1::smallint) AND ((("outer".userid)::text <> '7'::text) OR ("inner".userid IS NULL) OR (("inner".userid)::text <> '7'::text) OR ((("outer".userid)::text =3D '7'::text) AND ("outer"."granted" =3D '1'::bpchar)) OR ((("inner".userid)::text =3D '7'::text) AND ("outer"."granted" =3D '1'::bpchar)))) OR (("outer".permission > 1::smallint) AND ("outer"."granted" =3D '1'::bpchar)))) -> Bitmap Heap Scan on sq_ast_perm p (cost=3D7.87..1521.54 rows=3D820 width=3D297) (actual time=3D0.019..0.023 rows=3D2 loops=3D586) Recheck Cond: (($0)::text =3D (assetid)::text) -> Bitmap Index Scan on sq_ast_perm_assetid (cost=3D0.00..7.87 rows=3D820 width=3D0) (actual time=3D0.014..0.014 rows=3D2 loops=3D586) Index Cond: (($0)::text =3D (assetid)::text) -> Hash (cost=3D12.88..12.88 rows=3D229 width=3D164) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) -> Seq Scan on sq_ast_role (cost=3D0.00..12.88 rows=3D229 width=3D164) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((userid)::text <> '0'::text) -> Index Scan using sq_ast_typ_inhd_type_code on sq_ast_typ_inhd (cost=3D0.00..21.62 rows=3D1 width=3D68) (actual time=3D0.010..0.010 rows=3D1 loops=3D563) Index Cond: (("outer".type_code)::text =3D (sq_ast_typ_inhd.type_code)::text) Filter: (((inhd_type_code)::text =3D 'page'::text) OR ((type_code)::text =3D 'file'::text) OR ((type_code)::text =3D 'page_rss_feed'::text)) -> Bitmap Heap Scan on sq_ast_lnk l (cost=3D4.13..729.73 rows=3D324 width=3D23) (actual time=3D0.016..0.019 row= s=3D2 loops=3D532) Recheck Cond: ((l.minorid)::text =3D ("outer".assetid)= ::text) -> Bitmap Index Scan on sq_ast_lnk_minorid (cost=3D0.00..4.13 rows=3D324 width=3D0) (actual time=3D0.012..0.012 rows= =3D2 loops=3D532) Index Cond: ((l.minorid)::text =3D ("outer".assetid)::text) -> Bitmap Heap Scan on sq_ast_lnk_tree t (cost=3D10.87..18.88 rows=3D2 width=3D4) (actual time=3D2.382..2.382 rows=3D1 loops=3D975) Recheck Cond: (t.linkid =3D "outer".linkid) Filter: (treeid ~~ '0005%'::bytea) -> BitmapAnd (cost=3D10.87..10.87 rows=3D2 width=3D0) (actual time=3D2.379..2.379 rows=3D0 loops=3D975) -> Bitmap Index Scan on sq_ast_lnk_tree_linkid (cost=3D0.00..4.33 rows=3D381 width=3D0) (actual time=3D0.005..0.005 rows= =3D1 loops=3D975) Index Cond: (t.linkid =3D "outer".linkid) -> Bitmap Index Scan on sq_ast_lnk_tree_pkey (cost=3D0.00..6.28 rows=3D381 width=3D0) (actual time=3D3.521..3.521 rows=3D16476 loops=3D657) Index Cond: ((treeid >=3D '0005'::bytea) AND (treeid < '0006'::bytea)) Total runtime: 2636.294 ms thanks On Mon, Feb 1, 2010 at 11:54 AM, Scott Marlowe wr= ote: > On Sun, Jan 31, 2010 at 5:50 PM, Dave Clements wr= ote: >> 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. > > What does > > explain analyze select ... (rest of your query) > > say? >