RE: Why is this doing a seq scan?
| От | Ingram, Bryan |
|---|---|
| Тема | RE: Why is this doing a seq scan? |
| Дата | |
| Msg-id | 01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com обсуждение исходный текст |
| Ответ на | Why is this doing a seq scan? ("Ingram, Bryan" <BIngram@sixtyfootspider.com>) |
| Ответы |
Re: Why is this doing a seq scan?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-sql |
> Hmm. Have you VACUUM ANALYZED the tables? If so, what do > you get from > these queries: Tom, thanks for the reply, and here is all the info you asked for. > select attname,attdisbursion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'zips'; attname |attdisbursion|starelid|staattnum|staop|stanullfrac|stacommonfrac|stacommonv al|staloval |stahival ---------+-------------+--------+---------+-----+-----------+-------------+- -----------+----------+-------- zip | -1| 93920| 1| 1066| 0| 7.13394e-07|01226 |00401 |Y1A6A1 state | 0.165522| 93920| 2| 1066| 0| 0.346728|ON |AB |YT city | 0.00729095| 93920| 3| 1066| 0| 0.0322854|TORONTO | |ZWOLLE lat | 0.00326189| 93920| 4| 672| 0| 0.0153651|51.05 |-123.176 |79.989 lon | 0.00326061| 93920| 5| 672| 0| 0.0153594|-114.083333 |-176.31005|144.445 bestbound| 0.997491| 93920| 6| 672| 0.998605| 0.00107366|2 |2 |98 (6 rows) > select attname,attdisbursion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'atms'; attname |attdisbursion|starelid|staattnum|staop|stanullfrac|stacommonfrac|stacommonv al |staloval |stahival ----------------+-------------+--------+---------+-----+-----------+-------- -----+---------------+------------------------------+---------------- terminal | -1| 50904| 1| 97| 0| 0.000433463|6000 |55 |9433 district | 0.0679035| 50904| 2| 664| 0| 0.192024|ARCO California|ARCO Arizona |Western New York name | 0.000261431| 50904| 3| 664| 0| 0.00130039|Gateway Center |11th & Conger |Zionsville address | 0.000261431| 50904| 4| 664| 0| 0.00130039|215 Tecumseh Rd|"402 E Yakima Ave, Suite 1400"|Windham Mall city | 0.00522279| 50904| 5| 664| 0| 0.0238405|Seattle |Aberdeen |Zionsville state | 0.0687854| 50904| 6| 1058| 0| 0.193758|CA |AK |WA zip | 0.000614214| 50904| 7| 664| 0| 0.00303424|92392 | |99901 access | 0.385091| 50904| 8| 664| 0| 0.579974|WU | |WU function | 0.396416| 50904| 9| 664| 0| 0.589944|FF |CD |FF location | 0.414461| 50904| 10| 664| 0| 0.605548|BR |BR |Rem language | 0.431861| 50904| 11| 664| 0| 0.620286|E | |E restricted_hours| 0.886758| 50904| 12| 664| 0| 0.939749|FALSE |FALSE |TRUE seasonal | 0.994812| 50904| 13| 664| 0| 0.997399|FALSE |FALSE |TRUE stamps | 0.621877| 50904| 14| 664| 0| 0.746857|FALSE |FALSE |TRUE (14 rows) > Also it would be useful to see the full declarations of the tables > and their indexes; I'm wondering what datatype the zip columns are, > for example. Table = atms +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | terminal | int4 | 4 | | district | text | var | | name | text | var | | address | text | var | | city | text | var | | state | char() | 2 | | zip | text | var | | access | text | var | | function | text | var | | location | text | var | | language | text | var | | restricted_hours | text | var | | seasonal | text | var | | stamps | text | var | +----------------------------------+----------------------------------+----- --+ Index: atms_zip thirdfed=> \d zips Table = zips +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | zip | varchar() | 10 | | state | varchar() | 3 | | city | varchar() | 100 | | lat | float8 | 8 | | lon | float8 | 8 | | bestbound | float8 | 8 | +----------------------------------+----------------------------------+----- --+ Indices: zips_latindex zips_lonindex zips_pkey thirdfed=> \d zips_pkey Table = zips_pkey +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | zip | varchar() | 10 | +----------------------------------+----------------------------------+----- --+ thirdfed=> \d atms_zip Table = atms_zip +----------------------------------+----------------------------------+----- --+ | Field | Type | Length| +----------------------------------+----------------------------------+----- --+ | zip | text | var | +----------------------------------+----------------------------------+----- --+ Thanks, Bryan
В списке pgsql-sql по дате отправления: