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

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