Re: help with getting index scan

On Mon, 4 Mar 2002 00:33:10 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com> wrote:


> i changed the default values to:
>
>   shared_buffers = 15200
>   sort_mem = 32168
>   vacuum_mem = 8192
>   fsync = false
>
> > Could you, in addition, execute the following queries in stead of the
> > original and show us the explain output for them ?   But I'm not sure
> > they work faster than the original
>
> the two queries below are the same except for the 'set enable_seqscan
> to on' right? here are the results:


 No, there is a difference -- it is an "ORDER BY".


 ...

> for this next one, i assumed you wanted 'set enable_seqscan to off;'


 I didn't mean to let you set it to off. But both of them don't seem to
 become as fast as I have thought.


> Limit  (cost=107.13..107.13 rows=1 width=109)
>   ->  Sort  (cost=107.13..107.13 rows=1 width=109)
>         ->  Nested Loop  (cost=43.77..107.12 rows=1 width=109)
>               ->  Nested Loop  (cost=43.77..104.08 rows=1 width=93)
>                     ->  Merge Join  (cost=43.77..98.31 rows=1 width=12)
>                           ->  Index Scan using
>                                 phone_cat_address_cidaid_key
>                               on phone_cat_address pca
>                           ->  Sort  (cost=43.77..43.77 rows=11 width=4)
>                                 ->  Index Scan using phone_cat_nameftx
>                                     on phone_cat
>                     ->  Index Scan using phone_address_aid_key
>                         on phone_address p
>               ->  Index Scan using zipcodes_zc_idx on zipcodes z


 I would think there is obviously room for more research. To force the planner
 use the InitPlan, my two queries are changed a bit:


set enable_seqscan to on;
explain analyze   --- (1')
SELECT *
  FROM (SELECT p.name, p.address, p.city, p.state,
               geo_distance((SELECT point(z.longitude, z.latitude)
                               FROM zipcodes AS z
                              WHERE z.zip_code='55404'),
                             point(p.long, p.lat)) as dist
          FROM phone_address AS p,
               (SELECT * FROM phone_cat WHERE nameftx ## 'salon') AS pc,
               phone_cat_address AS pca,
         WHERE pc.cid = pca.cid AND pca.aid = p.aid
       ) AS ss
 WHERE ss.dist < 35
 ORDER BY ss.dist
LIMIT 20;


set enable_seqscan to on;
explain analyze   --- (2')
SELECT *
  FROM (SELECT p.name, p.address, p.city, p.state,
               geo_distance((SELECT point(z.longitude, z.latitude)
                               FROM zipcodes AS z
                              WHERE z.zip_code='55404'),
                             point(p.long, p.lat)) as dist
          FROM phone_address AS p,
               (SELECT * FROM phone_cat WHERE nameftx ## 'salon'
                ORDER BY cid) AS pc,
               phone_cat_address AS pca,
         WHERE pc.cid = pca.cid AND pca.aid = p.aid
       ) AS ss
 WHERE ss.dist < 35
 ORDER BY ss.dist
LIMIT 20;


Regards,
Masaru Sugawara