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

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