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