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=KDoEpq2faL7srQ1b9vfKbSubIc0BgUJ769Q+FrZPST/XuLsu3rjCtnA3qWmvJR39Za
	XwyeE+XIwq0a+Fw1dem5h8cHGNTS256GZrvIYzx690XIlbjpXeCTleCuOqOWIWBQVtbW
	wPGjZELJy/XifS1YgOitSwbxMrhvK70Hy89fc=
MIME-Version: 1.0
Reply-To: dclements89@gmail.com
In-Reply-To: <28233.1264995140@sss.pgh.pa.us>
References: <1f30b80c1001290049ub864091r8298fca3daa0eb69@mail.gmail.com>
	<1f30b80c1001311650q264dfda0uf9894cac3b9e0c3e@mail.gmail.com>
	<28233.1264995140@sss.pgh.pa.us>
From: Dave Clements <dclements89@gmail.com>
Date: Mon, 1 Feb 2010 15:25:23 +1100
Message-ID: <1f30b80c1001312025m118d5798lcb599aeb517beddc@mail.gmail.com>
Subject: Re: Fwd: Help required on query performance
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

After the analyze I am getting the time 3.20 ms but there is not
HashJoin there. Still all of them are NestLoops. But that is fine.

Now the only problem is the sequence scan on sq_sch_idx table.
I have a query like this:

explain analyze select count(*) from sq_sch_idx where value =3D '%download%=
';

This query does a sequence scan on the table. Is there a way I can
create an index for this?


thanks

On Mon, Feb 1, 2010 at 2:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Clements <dclements89@gmail.com> writes:
>> 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?
>
> BTW, just for the record, that diagnosis was completely off. =A0The
> upper level of your explain results is
>
> =A0HashAggregate =A0(cost=3D38145.19..38145.20 rows=3D1 width=3D149) (act=
ual time=3D2635.965..2636.086 rows=3D243 loops=3D1)
> =A0 -> =A0Nested Loop =A0(cost=3D15.00..38145.18 rows=3D1 width=3D149) (a=
ctual time=3D4.417..2635.086 rows=3D598 loops=3D1)
> =A0 =A0 =A0 =A0 -> =A0Nested Loop =A0(cost=3D4.13..37993.95 rows=3D8 widt=
h=3D153) (actual time=3D0.781..310.579 rows=3D975 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 ...
> =A0 =A0 =A0 =A0 -> =A0Bitmap Heap Scan on sq_ast_lnk_tree t =A0(cost=3D10=
.87..18.88 rows=3D2 width=3D4) (actual time=3D2.382..2.382 rows=3D1 loops=
=3D975)
> =A0 =A0 =A0 =A0 =A0 =A0 ...
>
> from which we can see that the main problem is doing the sq_ast_lnk_tree
> scan over again 975 times, once per row coming out of the other side of
> the join. =A0That accounted for 975*2.382 =3D 2322.450 msec, or the vast
> majority of the runtime. =A0The planner wouldn't have picked this plan
> except that it thought that only 8 rows would come out of the other side
> of the join; repeating the scan 8 times seemed better than the
> alternatives. =A0After you improved the statistics, it most likely
> switched *to* a hash join (or possibly a merge join) for this step,
> rather than switching away from one.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>