Re: help with getting index scan - Mailing list pgsql-general

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@minnesota.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