Re: help with getting index scan
От | Thomas T. Thai |
---|---|
Тема | Re: help with getting index scan |
Дата | |
Msg-id | Pine.NEB.4.43.0202251052570.27000-100000@ns01.minnesota.com обсуждение исходный текст |
Ответ на | Re: help with getting index scan (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
On Mon, 25 Feb 2002, Tom Lane wrote: This is getting rather long with the explains etc. should i continue to cc: to the list? > "Thomas T. Thai" <tom@minnesota.com> writes: > > sorry tom, i'm still new to PostgreSQL. what is pg_stats and how do i use > > it in the way you've asked? > > select * from pg_stats where tablename = 'foo'; yellowpages=# select * from pg_stats where tablename = 'phone_cat_address'; tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation -------------------+---------+-----------+-----------+------------+------------------------------ -----------------------------------------------------+------------------------------------------- --------------------------------------------------------------------------------+---------------- -------------------------------------------------------------------------------+------------- phone_cat_address | cid | 0 | 4 | 1176 | {2,10,3,12,11,16,6,56,18293,7 5} | {0.035,0.0283333,0.021,0.0206667,0.0133333 ,0.0123333,0.00933333,0.00933333,0.009,0.00833333} | {4,43,98,177,43 2,1603,2076,11212,15979,18262,18775} | 1 phone_cat_address | aid | 0 | 8 | -0.526973 | {873766,1468933,3316338,39146 78,4955422,6073155,6369270,8431670,9012026,10684620} | {0.000666667,0.000666667,0.000666667,0.000 666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667} | {10755,1321415, 2288476,3469515,4445287,5650291,7029439,8344730,9662520,11016908,100000851228} | 0.011702 (2 rows) yellowpages=# select * from pg_stats where tablename = 'phone_cat'; tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation -----------+---------+-----------+-----------+------------+------------------+------------------- +------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------- ------------------------+------------- phone_cat | id | 0 | 4 | -1 | | | {2,543,1050,1594,2106,2641,3892,5566,7244,8979,10673} | 0.861298 phone_cat | cid | 0 | 4 | -1 | | | {3,1189,2182,10960,12145,13215,14410,15649,16799,18058,19247} | 0.00397399 phone_cat | name | 0 | 29 | -1 | | | {"ABRASIVE CUTTING",Auditors,"Boat Covers Tops & Upholstery-Wholesale","Cash Registers & Suppli es (Wholesale)","Communication Equipment-Manufacturers","Decoration Supplies-Wholesale","Framing Contractors-Buildings","Livestock Commission",Plants-Horticultural,"State Government-General Offi ces",Zippers-Repairing} | 0.860659 phone_cat | popular | 0 | 5 | 2 | {N,Y} | {0.988,0.012} | | 0.996101 (4 rows) > > >> If you set enable_seqscan to off, do you get a plan you like better? > >> If so, what is it? > > > with seqscan off, the query still takes about the same about of time > > (around 8 secs). i'd like to get it down to 1 if possible. > > I wanted to know what the plan and cost estimates are. Also, it'd be > good to show EXPLAIN ANALYZE results, so that we can compare reality > to planner cost estimates ... yellowpages=# set enable_seqscan to on; SET VARIABLE yellowpages=# explain analyze 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.a id) yellowpages(# ) AS ss yellowpages-# WHERE dist < 35 yellowpages-# ORDER BY dist LIMIT 20;ORDER BY dist LIMIT 20; NOTICE: QUERY PLAN: Limit (cost=9286.33..9286.33 rows=20 width=115) (actual time=6748.11..6748.17 r ows=20 loops=1) -> Sort (cost=9286.33..9286.33 rows=112 width=115) (actual time=6748.10..674 8.12 rows=21 loops=1) -> Nested Loop (cost=44.12..9282.51 rows=112 width=115) (actual time=6 505.91..6701.65 rows=1745 loops=1) -> Index Scan using zipcodes_zc_idx on zipcodes z (cost=0.00..3. 01 rows=1 width=16) (actual time=0.97..0.97 rows=1 loops=1) -> Materialize (cost=9272.76..9272.76 rows=337 width=99) (actual time=6504.94..6558.83 rows=4217 loops=1) -> Nested Loop (cost=44.12..9272.76 rows=337 width=99) (ac tual time=555.67..6398.70 rows=4217 loops=1) -> Hash Join (cost=44.12..7243.86 rows=337 width=16) (actual time=555.67..5440.17 rows=4217 loops=1) -> Seq Scan on phone_cat_address pca (cost=0.0 0..5512.02 rows=336702 width=12) (actual time=0.00..3376.45 rows=336702 loops=1) -> Hash (cost=44.09..44.09 rows=11 width=4) (a ctual time=5.86..5.86 rows=0 loops=1) -> Index Scan using phone_cat_nameftx_idx on phone_cat pc (cost=0.00..44.09 rows=11 width=4) (actual time=2.93..5.85 row s=8 loops=1) -> Index Scan using phone_address_aid_key on phone_ad dress p (cost=0.00..6.01 rows=1 width=83) (actual time=0.16..0.18 rows=1 loops= 4217) Total runtime: 6786.19 msec EXPLAIN yellowpages=# ---- yellowpages=# set enable_seqscan to off; SET VARIABLE yellowpages=# explain analyze -> Index Scan using pho ne_address_aid_key on phone_ad yellowpages-# dress p (cost=0.00..6.01 rows=1 width=83) (actual time=0.16..0.18 rows=1 loops= yellowpages(# 4217) yellowpages-# Total runtime: 6786.19 msec yellowpages-# yellowpages-# EXPLAIN yellowpages-# yellowpages=# yellowpages-# ; ERROR: parser: parse error at or near "->" yellowpages=# set enable_seqscan to off; SET VARIABLE yellowpages=# explain analyze 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.a id) 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) (actual time=1564.47..1564.53 rows=20 loops=1) -> Sort (cost=10799.67..10799.67 rows=112 width=115) (actual time=1564.47..1 564.49 rows=21 loops=1) -> Nested Loop (cost=0.00..10795.85 rows=112 width=115) (actual time=1 323.26..1517.14 rows=1745 loops=1) -> Index Scan using zipcodes_zc_idx on zipcodes z (cost=0.00..3. 01 rows=1 width=16) (actual time=0.00..0.00 rows=1 loops=1) -> Materialize (cost=10786.10..10786.10 rows=337 width=99) (actu al time=1322.28..1372.30 rows=4217 loops=1) -> Nested Loop (cost=0.00..10786.10 rows=337 width=99) (ac tual time=3.90..1209.10 rows=4217 loops=1) -> Nested Loop (cost=0.00..8757.20 rows=337 width=16 ) (actual time=2.93..245.72 rows=4217 loops=1) -> Index Scan using phone_cat_nameftx_idx on ph one_cat pc (cost=0.00..44.09 rows=11 width=4) (actual time=2.92..5.79 rows=8 lo ops=1) -> Index Scan using phone_cat_address_cid_key o n phone_cat_address pca (cost=0.00..812.56 rows=286 width=12) (actual time=0.60 ..21.64 rows=527 loops=8) -> Index Scan using phone_address_aid_key on phone_ad dress p (cost=0.00..6.01 rows=1 width=83) (actual time=0.17..0.19 rows=1 loops= 4217) Total runtime: 1596.69 msec EXPLAIN
В списке pgsql-general по дате отправления: