Re: help with getting index scan
От | Jean-Luc Lachance |
---|---|
Тема | Re: help with getting index scan |
Дата | |
Msg-id | 3C7AAC62.5A127420@nsd.ca обсуждение исходный текст |
Ответ на | Re: help with getting index scan ("Thomas T. Thai" <tom@minnesota.com>) |
Ответы |
Re: help with getting index scan
|
Список | 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
В списке pgsql-general по дате отправления: