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.