Re: help with getting index scan

On Tue, 26 Feb 2002, Jean-Luc Lachance wrote:

> I think it is because of the SS intermediate result.
> PG does not have an index on ss.aid to do the join, so it it has to sort
> the result.
>
> Let's try it the other way around; take the ss out of the picture.
>
> SELECT p.name, p.address, p.city, p.state,
> geo_distance(
> ( SELECT point( longitude, latitude)
> FROM zipcodes WHERE zip_code ='55404'),
> point(long, lat)
> ) AS dist
> FROM phone_address p, phone_cat AS pc, phone_cat_address AS pca
> WHERE pc.nameftx ## 'salon' AND pc.cid=pca.cid AND pca.aid=p.aid AND
> geo_distance(
> (SELECT point( longitude, latitude)
> FROM zipcodes WHERE zip_code ='55404'),
> point(long, lat)
> ) < 35
> ORDER BY dist LIMIT 20;

this still uses seq scan if i have 'set enable_seqscan to on;'

Limit (cost=9279.49..9279.49 rows=20 width=98)
(actual time=19257.96..19258.02 rows=20 loops=1)
InitPlan
-> Index Scan using zipcodes_zc_idx on zipcodes
(cost=0.00..3.01 rows=1 width=16)
(actual time=0.00..0.00 rows=1 loops=1)
-> Index Scan using zipcodes_zc_idx on zipcodes
(cost=0.00..3.01 rows=1 width=16)
(actual time=26.37..26.37 rows=1 loops=1)
-> Sort (cost=9279.49..9279.49 rows=112 width=98)
(actual time=19257.96..19257.98 rows=21 loops=1)
-> Nested Loop (cost=44.52..9275.67 rows=112 width=98)
(actual time=873.05..19203.65 rows=1745 loops=1)
-> Hash Join (cost=44.52..7244.26 rows=337 width=16)
(actual time=778.33..8155.07 rows=4217 loops=1)
-> Seq Scan on phone_cat_address pca
(cost=0.00..5512.02 rows=336702 width=12)
(actual time=6.83..5012.23 rows=336702 loops=1)
-> Hash (cost=44.50..44.50 rows=11 width=4)
(actual time=186.52..186.52 rows=0 loops=1)
-> Index Scan using phone_cat_nameftx_idx
on phone_cat pc
(cost=0.00..44.50 rows=11 width=4)
(actual time=67.38..186.51 rows=8 loops=1)
-> Index Scan using phone_address_aid_key on phone_address
p
(cost=0.00..6.02 rows=1 width=82)
(actual time=2.57..2.58 rows=0 loops=4217)
Total runtime: 19258.94 msec

Limit (cost=9279.49..9279.49 rows=20 width=98)
(actual time=19257.96..19258.02 rows=20 loops=1)
InitPlan
-> Index Scan using zipcodes_zc_idx on zipcodes
(cost=0.00..3.01 rows=1 width=16)
(actual time=0.00..0.00 rows=1 loops=1)
-> Index Scan using zipcodes_zc_idx on zipcodes
(cost=0.00..3.01 rows=1 width=16)
(actual time=26.37..26.37 rows=1 loops=1)
-> Sort (cost=9279.49..9279.49 rows=112 width=98)
(actual time=19257.96..19257.98 rows=21 loops=1)
-> Nested Loop (cost=44.52..9275.67 rows=112 width=98)
(actual time=873.05..19203.65 rows=1745 loops=1)
-> Hash Join (cost=44.52..7244.26 rows=337 width=16)
(actual time=778.33..8155.07 rows=4217 loops=1)
-> Seq Scan on phone_cat_address pca
(cost=0.00..5512.02 rows=336702 width=12)
(actual time=6.83..5012.23 rows=336702 loops=1)
-> Hash (cost=44.50..44.50 rows=11 width=4)
(actual time=186.52..186.52 rows=0 loops=1)
-> Index Scan using phone_cat_nameftx_idx
on phone_cat pc
(cost=0.00..44.50 rows=11 width=4)
(actual time=67.38..186.51 rows=8 loops=1)
-> Index Scan using phone_address_aid_key on phone_address
p
(cost=0.00..6.02 rows=1 width=82)
(actual time=2.57..2.58 rows=0 loops=4217)
Total runtime: 19258.94 msec