Re: help with getting index scan

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

> > 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;

Limit (cost=20934.34..20934.34 rows=20 width=93)
(actual time=12176.87..12177.84 rows=20 loops=1)
InitPlan
-> Index Scan using zipcodes_zc_idx on zipcodes z
(cost=0.00..3.01 rows=1 width=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.00 rows=1 loops=1)
-> Sort (cost=20934.34..20934.34 rows=959 width=93)
(actual time=12176.87..12177.80 rows=21 loops=1)
-> Hash Join (cost=8455.23..20886.82 rows=959 width=93)
(actual time=5344.78..12127.58 rows=1745 loops=1)
-> Seq Scan on phone_address p
(cost=0.00..11207.20 rows=69280 width=81)
(actual time=0.01..5821.06 rows=105214 loops=1)
-> Hash (cost=8448.04..8448.04 rows=2878 width=12)
(actual time=5336.00..5336.00 rows=0 loops=1)
-> Hash Join (cost=43.79..8448.04 rows=2878
width=12)
(actual time=388.78..5305.09 rows=4217 loops=1)
-> Seq Scan on phone_cat_address pca
(cost=0.00..5843.01 rows=336701 width=8)
(actual time=0.00..3475.08 rows=336701
loops=1)
-> Hash (cost=43.77..43.77 rows=11 width=74)
(actual time=4.92..4.92 rows=0 loops=1)
-> Subquery Scan pc
(cost=43.77..43.77 rows=11 width=74)
(actual time=4.88..4.91 rows=8 loops=1)
-> Sort (cost=43.77..43.77
rows=11 width=74)
(actual time=4.88..4.89
rows=8 loops=1)
-> Index Scan using
phone_cat_name_fts_idx on phone_cat
(cost=0.00..43.58 rows=11 width=74)
(actual time=0.97..3.90 rows=8
loops=1)
Total runtime: 12178.84 msec

--
Thomas T. Thai
Minnesota.com, Inc.