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