Re: help with getting index scan

On Mon, 25 Feb 2002, Jean-Luc Lachance wrote:

> Thomas,
>
> Can you try:
>
> SELECT name, address, city, state, dist
> FROM
> (SELECT aid, name, address, city, state,
> geo_distance(
> (select point( longitude, latitude) from zipcodes WHERE zip_code =
> '55404'),
> point(long, lat)) as dist
> FROM phone_address WHERE dist < 35) AS ss, phone_cat AS pc,
> phone_cat_address AS pca
> WHERE pc.nameftx ## 'salon' AND pc.cid=pca.cid AND pca.aid=ss.aid
> ORDER BY dist LIMIT 20;
>
> you may have to replace dist in dist < 35 by the whole thing
> geo_distance(...);

to get it to work, the query had to be changed to:
SELECT ss.name, address, city, state, dist
FROM
(SELECT aid, name, address, city, state,
geo_distance(
(SELECT point( longitude, latitude)
FROM zipcodes WHERE zip_code ='55404'),
point(long, lat)
) AS dist
FROM phone_address
WHERE geo_distance(
(SELECT point( longitude, latitude)
FROM zipcodes WHERE zip_code ='55404'),
point(long, lat)
) < 35
) AS ss,
phone_cat AS pc,
phone_cat_address AS pca
WHERE pc.nameftx ## 'salon' AND pc.cid=pca.cid AND pca.aid=ss.aid
ORDER BY dist LIMIT 20;

Summary: not much difference from the original query. I'm still not
understanding why they plan chose to use seqscan. it takes 6 times longer.

---
SET enable_seqscan TO on:

Limit (cost=9279.11..9279.11 rows=20 width=99)
(actual time=6518.61..6518.67 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=0.00..0.00 rows=1 loops=1)
-> Sort (cost=9279.11..9279.11 rows=112 width=99)
(actual time=6518.61..6518.63 rows=21 loops=1)
-> Nested Loop (cost=44.12..9275.29 rows=112 width=99)
(actual time=556.65..6470.21 rows=1745 loops=1)
-> Hash Join (cost=44.12..7243.86 rows=337 width=16)
(actual time=554.75..5418.58 rows=4217 loops=1)
-> Seq Scan on phone_cat_address pca
(cost=0.00..5512.02 rows=336702 width=12)
(actual time=0.00..3329.21 rows=336702 loops=1)
-> Hash (cost=44.09..44.09 rows=11 width=4)
(actual time=4.88..4.88 rows=0 loops=1)
-> Index Scan
using phone_cat_nameftx_idx on phone_cat pc
(cost=0.00..44.09 rows=11 width=4)
(actual time=1.95..4.87 rows=8 loops=1)
-> Index Scan using phone_address_aid_key on phone_address
(cost=0.00..6.02 rows=1 width=83)
(actual time=0.20..0.21 rows=0 loops=4217)
Total runtime: 6521.54 msec

---
SET enable_seqscan TO off:

Limit (cost=10792.45..10792.45 rows=20 width=99)
(actual time=1316.42..1316.48rows=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=0.97..0.98 rows=1 loops=1)
-> Sort (cost=10792.45..10792.45 rows=112 width=99)
(actual time=1316.42..1316.44 rows=21 loops=1)
-> Nested Loop (cost=0.00..10788.63 rows=112 width=99)
(actual time=6.84..1263.21 rows=1745 loops=1)
-> Nested Loop (cost=0.00..8757.20 rows=337 width=16)
(actual time=2.93..239.25 rows=4217 loops=1)
-> Index Scan using phone_cat_nameftx_idx
on phone_cat pc
(cost=0.00..44.09 rows=11 width=4)
(actual time=2.93..6.75 rows=8 loops=1)
-> Index Scan using phone_cat_address_cid_key
on phone_cat_address pca
(cost=0.00..812.56 rows=286 width=12)
(actual time=0.36..21.94 rows=527 loops=8)
-> Index Scan using phone_address_aid_key
on phone_address (cost=0.00..6.02 rows=1 width=83)
(actual time=0.20..0.21 rows=0 loops=4217)
Total runtime: 1318.37 msec