Re: help with getting index scan

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