Re: Fwd: Help required on query performance

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 = '%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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Clements <dclements89(at)gmail(dot)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.  The
> upper level of your explain results is
>
>  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)
>             ...
>         ->  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)
>             ...
>
> 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.  That accounted for 975*2.382 = 2322.450 msec, or the vast
> majority of the runtime.  The 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.  After 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.
>
>                        regards, tom lane
>