Re: help with getting index scan

Thomas,

Obviously, my "If" statement was not satisfied...
Any chance that "SELECT DISTINCT aid..." might work?

JLL

P.S. I can't explain why you get the error twice.

"Thomas T. Thai" wrote:
>
> 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;'