Re: help with getting index scan

On Mon, 25 Feb 2002 10:51:15 -0600 (CST)
"Thomas T. Thai" <tom(at)minnesota(dot)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