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

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=#