Re: help with getting index scan

On Wed, 6 Mar 2002 08:42:43 -0600 (CST)
"Thomas T. Thai" <tom(at)minnesota(dot)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