Re: help with getting index scan

On Wed, 6 Mar 2002 00:51:15 -0600 (CST)
"Thomas T. Thai" <tom(at)minnesota(dot)com> wrote:

> On Wed, 6 Mar 2002, Thomas T. Thai wrote:
>
> > ERROR: parser: parse error at or near "WHERE"
>
> ok found it. comma before where. see explain below.
>
> > > 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;
>
> Limit (cost=9537.20..9537.20 rows=20 width=93)
> (actual time=5849.67..5849.73 rows=20 loops=1)
> InitPlan
> -> Index Scan using zipcodes_zc_idx on zipcodes z
> (cost=0.00..3.01 rows=1width=16)
> (actual time=0.00..0.00 rows=1 loops=1)
> -> Index Scan using zipcodes_zc_idx on zipcodes z
> (cost=0.00..3.01 rows=1 width=16)
> (actual time=0.00..0.93 rows=1 loops=1)
> -> Sort (cost=9537.20..9537.20 rows=112 width=93)
> (actual time=5849.66..5849.68 rows=21 loops=1)
> -> Nested Loop (cost=43.61..9533.38 rows=112 width=93)
> (actual time=383.80..5798.66 rows=1745 loops=1)
> -> Hash Join (cost=43.61..7574.33 rows=337 width=12)
> (actual time=382.86..5214.98 rows=4217 loops=1)
> -> Seq Scan on phone_cat_address pca
> (cost=0.00..5843.01 rows=336701 width=8)
> (actual time=0.00..3441.76 rows=336701 loops=1)
> -> Hash (cost=43.58..43.58 rows=11 width=4)
> (actual time=3.91..3.91 rows=0 loops=1)
> -> Index Scan using phone_cat_name_fts_idx
> on phone_cat (cost=0.00..43.58 rows=11 width=4)
> (actual time=0.00..3.90 rows=8 loops=1)
> -> Index Scan using phone_address_aid_key
> on phone_address p (cost=0.00..5.81 rows=1 width=81)
> (actual time=0.10..0.11 rows=0 loops=4217)
> Total runtime: 5851.62 msec

That's not bat, I guess. The query below is a try to manage to get
a pattern like this:

-> Nested Loop
-> Index Scan using phone_cat_nameftx_idx on phone_cat pc
-> Index Scan using phone_cat_address_cid_key on phone_cat_address pca

But I have no idea any more.

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 pca.aid
FROM (SELECT * FROM phone_cat
WHERE nameftx ## 'salon') AS pc,
phone_cat_address AS pca
WHERE pc.cid = pca.cid
ORDER BY 1
) AS pc_pca
WHERE pc_pca.aid = p.aid
) AS ss
WHERE ss.dist < 35
ORDER BY ss.dist
LIMIT 20;

Regards,
Masaru Sugawara