Skip site navigation (1) Skip section navigation (2)

RE: Why is this doing a seq scan?

From: "Ingram, Bryan" <BIngram(at)sixtyfootspider(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: RE: Why is this doing a seq scan?
Date: 2000-11-17 20:55:04
Message-ID: 01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com (view raw or flat)
Thread:
Lists: 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



Responses

pgsql-sql by date

Next:From: Max FoninDate: 2000-11-17 21:17:34
Subject: Re: is there a mysql to postgresql sql converter?
Previous:From: Tom LaneDate: 2000-11-17 20:06:13
Subject: Re: Why is this doing a seq scan?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group