Re: help with getting index scan

On Tue, 26 Feb 2002, Jean-Luc Lachance wrote:

> Thomas,
>
> Maybe the where clause logical expression is not being reduced.
> If there is only one pca record that will satisfy
> pc.nameftx ## 'salon' AND pc.cid=pca.cid
>
> try:
>
> WHERE aid = ( SELECT aid FROM phone_cat_address AS pca, phone_cat AS pc
> WHERE pc.nameftx ## 'salon' AND pc.cid=pca.cid)

SELECT p.name, p.address, p.city, p.state,
geo_distance(
( SELECT point( longitude, latitude)
FROM zipcodes WHERE zip_code ='55404'),
point(long, lat)
) AS dist
FROM phone_address p, phone_cat AS pc, phone_cat_address AS pca
WHERE p.aid = (SELECT aid
FROM phone_cat_address AS pca, phone_cat AS pc
WHERE pc.nameftx ## 'salon' AND pc.cid=pca.cid
) AND
geo_distance(
(SELECT point( longitude, latitude)
FROM zipcodes WHERE zip_code ='55404'),
point(long, lat)
) < 35
ORDER BY dist LIMIT 20;

ERROR: More than one tuple returned by a subselect used as an expression.
ERROR: More than one tuple returned by a subselect used as an expression.

> > > SELECT p.name, p.address, p.city, p.state,
> > > geo_distance(
> > > ( SELECT point( longitude, latitude)
> > > FROM zipcodes WHERE zip_code ='55404'),
> > > point(long, lat)
> > > ) AS dist
> > > FROM phone_address p, phone_cat AS pc, phone_cat_address AS pca
> > > WHERE pc.nameftx ## 'salon' AND pc.cid=pca.cid AND pca.aid=p.aid AND
> > > geo_distance(
> > > (SELECT point( longitude, latitude)
> > > FROM zipcodes WHERE zip_code ='55404'),
> > > point(long, lat)
> > > ) < 35
> > > ORDER BY dist LIMIT 20;
> >
> > this still uses seq scan if i have 'set enable_seqscan to on;'