Re: help with getting index scan

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)
...
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.

having tuned postgresql, there has been a huge speed increase. however,
the seq scan is still costing me. doing a 'set enable_seqscan to off;'
before the actual query seems like a kludge.

> set enable_seqscan to on;
> explain analyze   --- (1'')
> SELECT *
>   FROM (SELECT p.name, p.address, p.city, p.state,
>                geo_distance((SELECT point(z.longitude, z.latitude)
>                                FROM zipcodes AS z
>                               WHERE z.zip_code='55404'),
>                              point(p.long, p.lat)) as dist
>           FROM phone_address AS p,
>                (SELECT pca.aid
>                  FROM (SELECT * FROM phone_cat
>                           WHERE nameftx ## 'salon') AS pc,
>                       phone_cat_address AS pca
>                  WHERE pc.cid = pca.cid
>                  ORDER BY 1
>                ) AS pc_pca
>          WHERE pc_pca.aid = p.aid
>        ) AS ss
>  WHERE ss.dist < 35
>  ORDER BY ss.dist
> LIMIT 20;

--
Thomas T. Thai
Minnesota.com, Inc.