Re: help with getting index scan

On Mon, 4 Mar 2002 00:33:10 -0600 (CST)
"Thomas T. Thai" <tom(at)minnesota(dot)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