Re: help with getting index scan

On Mon, 25 Feb 2002 10:51:15 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com> wrote:

>
> it does seem that the index scan is slightly faster, but the overall
> results feels roughly about the same. here are the explains:
>
> explain SELECT *
> yellowpages-# FROM
> yellowpages-#   (SELECT p.name,p.address,p.city,p.state,
> yellowpages(#
> geo_distance(point(z.longitude,z.latitude),point(p.long,p.lat)) as dist
> yellowpages(#    FROM phone_address AS p, phone_cat AS pc,
> phone_cat_address AS pca, zipcodes AS
> z
> yellowpages(#    WHERE z.zip_code='55404'
> yellowpages(#      AND (pc.nameftx ## 'salon' AND pc.cid=pca.cid AND
> pca.aid=p.aid)
> yellowpages(#   ) AS ss
> yellowpages-# WHERE dist < 35
> yellowpages-# ORDER BY dist LIMIT 20;


Does a setting of the sort_mem still have a default value  ?
Could you try a series of your challenges again after rewriting the
postgresql.conf if so ?

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


 set enable_seqscan to on;
explain analyze   --- (1)
SELECT *
  FROM (SELECT p.name, p.address, p.city, p.state,
               geo_distance(point(z.longitude, z.latitude),
                                                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,
               zipcodes AS z
         WHERE z.zip_code='55404' AND pc.cid=pca.cid AND pca.aid=p.aid
       ) AS ss
 WHERE ss.dist < 35
 ORDER BY ss.dist
LIMIT 20;


explain analyze   --- (2)
SELECT *
  FROM (SELECT p.name, p.address, p.city, p.state,
               geo_distance(point(z.longitude, z.latitude),
                                                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,
               zipcodes AS z
         WHERE z.zip_code='55404' AND pc.cid=pca.cid AND pca.aid=p.aid
       ) AS ss
 WHERE ss.dist < 35
 ORDER BY ss.dist
LIMIT


Regards,
Masaru Sugawara