Re: help with getting index scan

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)



"Thomas T. Thai" wrote:
>
> On Tue, 26 Feb 2002, Jean-Luc Lachance wrote:
>
> > I think it is because of the SS intermediate result.
> > PG does not have an index on ss.aid to do the join, so it it has to sort
> > the result.
> >
> > Let's try it the other way around; take the ss out of the picture.
> >
> > 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;'
>