Re: Fwd: Help required on query performance

Hi, following the output from explain analyze.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=38145.19..38145.20 rows=1 width=149) (actual
time=2635.965..2636.086 rows=243 loops=1)
-> Nested Loop (cost=15.00..38145.18 rows=1 width=149) (actual
time=4.417..2635.086 rows=598 loops=1)
-> Nested Loop (cost=4.13..37993.95 rows=8 width=153)
(actual time=0.781..310.579 rows=975 loops=1)
-> Nested Loop IN Join (cost=0.00..37260.18 rows=1
width=168) (actual time=0.747..298.686 rows=532 loops=1)
-> Nested Loop (cost=0.00..37216.90 rows=2
width=236) (actual time=0.731..292.449 rows=563 loops=1)
-> Seq Scan on sq_sch_idx ai
(cost=0.00..20921.47 rows=10 width=149) (actual time=0.616..260.601
rows=677 loops=1)
Filter: ((value)::text ~~ '%download%'::text)
-> Index Scan using sq_ast_pkey on sq_ast
a (cost=0.00..1629.53 rows=1 width=87) (actual time=0.045..0.046
rows=1 loops=677)
Index Cond: (("outer".assetid)::text
= (a.assetid)::text)
Filter: ((status >= 16::smallint) AND
(subplan))
SubPlan
-> HashAggregate
(cost=1623.50..1623.52 rows=1 width=150) (actual time=0.031..0.031
rows=1 loops=586)
Filter: (min("granted") <> '0'::bpchar)
-> Hash Left Join
(cost=21.32..1619.40 rows=820 width=150) (actual time=0.023..0.028
rows=1 loops=586)
Hash Cond:
(("outer".userid)::text = ("inner".roleid)::text)
Filter:
(((("outer".userid)::text = '7'::text) OR (("inner".userid)::text =
'7'::text)) AND ((("outer".permission = 1::smallint) AND
((("outer".userid)::text <> '7'::text) OR ("inner".userid IS NULL) OR
(("inner".userid)::text <> '7'::text) OR ((("outer".userid)::text =
'7'::text) AND ("outer"."granted" = '1'::bpchar)) OR
((("inner".userid)::text = '7'::text) AND ("outer"."granted" =
'1'::bpchar)))) OR (("outer".permission > 1::smallint) AND
("outer"."granted" = '1'::bpchar))))
-> Bitmap Heap Scan on
sq_ast_perm p (cost=7.87..1521.54 rows=820 width=297) (actual
time=0.019..0.023 rows=2 loops=586)
Recheck Cond:
(($0)::text = (assetid)::text)
-> Bitmap Index
Scan on sq_ast_perm_assetid (cost=0.00..7.87 rows=820 width=0)
(actual time=0.014..0.014 rows=2 loops=586)
Index Cond:
(($0)::text = (assetid)::text)
-> Hash
(cost=12.88..12.88 rows=229 width=164) (actual time=0.001..0.001
rows=0 loops=1)
-> Seq Scan on
sq_ast_role (cost=0.00..12.88 rows=229 width=164) (actual
time=0.001..0.001 rows=0 loops=1)
Filter:
((userid)::text <> '0'::text)
-> Index Scan using sq_ast_typ_inhd_type_code on
sq_ast_typ_inhd (cost=0.00..21.62 rows=1 width=68) (actual
time=0.010..0.010 rows=1 loops=563)
Index Cond: (("outer".type_code)::text =
(sq_ast_typ_inhd.type_code)::text)
Filter: (((inhd_type_code)::text =
'page'::text) OR ((type_code)::text = 'file'::text) OR
((type_code)::text = 'page_rss_feed'::text))
-> Bitmap Heap Scan on sq_ast_lnk l
(cost=4.13..729.73 rows=324 width=23) (actual time=0.016..0.019 rows=2
loops=532)
Recheck Cond: ((l.minorid)::text = ("outer".assetid)::text)
-> Bitmap Index Scan on sq_ast_lnk_minorid
(cost=0.00..4.13 rows=324 width=0) (actual time=0.012..0.012 rows=2
loops=532)
Index Cond: ((l.minorid)::text =
("outer".assetid)::text)
-> Bitmap Heap Scan on sq_ast_lnk_tree t (cost=10.87..18.88
rows=2 width=4) (actual time=2.382..2.382 rows=1 loops=975)
Recheck Cond: (t.linkid = "outer".linkid)
Filter: (treeid ~~ '0005%'::bytea)
-> BitmapAnd (cost=10.87..10.87 rows=2 width=0)
(actual time=2.379..2.379 rows=0 loops=975)
-> Bitmap Index Scan on sq_ast_lnk_tree_linkid
(cost=0.00..4.33 rows=381 width=0) (actual time=0.005..0.005 rows=1
loops=975)
Index Cond: (t.linkid = "outer".linkid)
-> Bitmap Index Scan on sq_ast_lnk_tree_pkey
(cost=0.00..6.28 rows=381 width=0) (actual time=3.521..3.521
rows=16476 loops=657)
Index Cond: ((treeid >= '0005'::bytea) AND
(treeid < '0006'::bytea))
Total runtime: 2636.294 ms

thanks

On Mon, Feb 1, 2010 at 11:54 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Sun, Jan 31, 2010 at 5:50 PM, Dave Clements <dclements89(at)gmail(dot)com> wrote:
>> 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?
>