Re: help with getting index scan

Thomas,

Can you try:

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

you may have to replace dist in dist < 35 by the whole thing
geo_distance(...);

JLL

"Thomas T. Thai" wrote:
>
> On Mon, 25 Feb 2002, Tom Lane wrote:
>
> > "Thomas T. Thai" <tom(at)minnesota(dot)com> writes:
> > > On Mon, 25 Feb 2002, Tom Lane wrote:
> > >> How many distinct cid values do you have? Also, which PG version is
> > >> this?
> >
> > > 5139
> >
> > Hmm, seems like that ought to be selective enough. What does pg_stats
> > show for phone_cat_address? (And phone_cat, for that matter.)
> >
> > If you set enable_seqscan to off, do you get a plan you like better?
> > If so, what is it?
>
> it does seem that the index scan is slightly faster, but the overall
> results feels roughly about the same. here are the explains:
>
> explain SELECT *
> yellowpages-# FROM
> yellowpages-# (SELECT p.name,p.address,p.city,p.state,
> yellowpages(#
> geo_distance(point(z.longitude,z.latitude),point(p.long,p.lat)) as dist
> yellowpages(# FROM phone_address AS p, phone_cat AS pc,
> phone_cat_address AS pca, zipcodes AS
> z
> yellowpages(# WHERE z.zip_code='55404'
> yellowpages(# AND (pc.nameftx ## 'salon' AND pc.cid=pca.cid AND
> pca.aid=p.aid)
> yellowpages(# ) AS ss
> yellowpages-# WHERE dist < 35
> yellowpages-# ORDER BY dist LIMIT 20;
> NOTICE: QUERY PLAN:
>
> Limit (cost=10799.67..10799.67 rows=20 width=115)
> -> Sort (cost=10799.67..10799.67 rows=112 width=115)
> -> Nested Loop (cost=0.00..10795.85 rows=112 width=115)
> -> Index Scan using zipcodes_zc_idx on zipcodes z
> (cost=0.00..3.01 rows=1 width=1
> 6)
> -> Materialize (cost=10786.10..10786.10 rows=337 width=99)
> -> Nested Loop (cost=0.00..10786.10 rows=337
> width=99)
> -> Nested Loop (cost=0.00..8757.20 rows=337
> width=16)
> -> Index Scan using phone_cat_nameftx_idx
> on phone_cat pc (cost
> =0.00..44.09 rows=11 width=4)
> -> Index Scan using
> phone_cat_address_cid_key on phone_cat_addre
> ss pca (cost=0.00..812.56 rows=286 width=12)
> -> Index Scan using phone_address_aid_key on
> phone_address p (cost=0.
> 00..6.01 rows=1 width=83)
>
> EXPLAIN
> yellowpages=# set enable_seqscan to on;
> SET VARIABLE
> yellowpages=# explain SELECT *
> yellowpages-# FROM
> yellowpages-# (SELECT p.name,p.address,p.city,p.state,
> yellowpages(#
> geo_distance(point(z.longitude,z.latitude),point(p.long,p.lat)) as dist
> yellowpages(# FROM phone_address AS p, phone_cat AS pc,
> phone_cat_address AS pca, zipcodes AS
> z
> yellowpages(# WHERE z.zip_code='55404'
> yellowpages(# AND (pc.nameftx ## 'salon' AND pc.cid=pca.cid AND
> pca.aid=p.aid)
> yellowpages(# ) AS ss
> yellowpages-# WHERE dist < 35
> yellowpages-# ORDER BY dist LIMIT 20;
> NOTICE: QUERY PLAN:
>
> Limit (cost=9286.33..9286.33 rows=20 width=115)
> -> Sort (cost=9286.33..9286.33 rows=112 width=115)
> -> Nested Loop (cost=44.12..9282.51 rows=112 width=115)
> -> Index Scan using zipcodes_zc_idx on zipcodes z
> (cost=0.00..3.01 rows=1 width=1
> 6)
> -> Materialize (cost=9272.76..9272.76 rows=337 width=99)
> -> Nested Loop (cost=44.12..9272.76 rows=337
> width=99)
> -> Hash Join (cost=44.12..7243.86 rows=337
> width=16)
> -> Seq Scan on phone_cat_address pca
> (cost=0.00..5512.02 rows=3
> 36702 width=12)
> -> Hash (cost=44.09..44.09 rows=11
> width=4)
> -> Index Scan using
> phone_cat_nameftx_idx on phone_cat pc
> (cost=0.00..44.09 rows=11 width=4)
> -> Index Scan using phone_address_aid_key on
> phone_address p (cost=0.
> 00..6.01 rows=1 width=83)
>
> EXPLAIN
> yellowpages=#
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org