Re: help with getting index scan

On Wed, 6 Mar 2002 08:42:43 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com> wrote:

> On Wed, 6 Mar 2002, Masaru Sugawara wrote:
> [...]
> >  That's not bat, I guess. The query below is a try to manage to get
> >  a pattern like this:
> >
> >    -> Nested Loop
> >       -> Index Scan using phone_cat_nameftx_idx on phone_cat pc
> >       -> Index Scan using phone_cat_address_cid_key on phone_cat_address pca
>
> the query below  still results in a seq scan:
> ...
> ->  Seq Scan on phone_cat_address pca
>    (cost=0.00..5843.01 rows=336701 width=8)
>    (actual time=0.97..2875.06 rows=336701 loops=1)
>       ->  Hash  (cost=43.58..43.58 rows=11 width=4)
>           (actual time=3.91..3.91 rows=0 loops=1)


 It's a pity that the query use no index on phone_cat_address.


> ...
> Total runtime: 5240.28 msec
>
> something is not right about with the planner. using my original query and
> with seqscan off, i can get the query to drop to around 600 ms. can't
> understand why it would choose to use seqscan on phone_cat_address.


 Since phone_cat_address isn't limited by a WHERE cluse, etc., most of its
 rows will be selected. Therefore the planner seems to judge that a
 sequential scan is better/faster than an index scan.



Regards,
Masaru Sugawara