Re: help with getting index scan - Mailing list pgsql-general

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;

JLL


"Thomas T. Thai" wrote:
>
[...]
> 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