pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ingram, Bryan <BIngram@sixtyfootspider.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: RE: Why is this doing a seq scan?
Date: Fri, 17 Nov 2000 14:55:04 -0600
Message-ID: <01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com> (raw)

> 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





view thread (5+ messages)  latest in thread

Message-ID: <01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com>
Permalink:  ../01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com/
Also on:    postgresql.org/message-id/01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: BIngram@sixtyfootspider.com, tgl@sss.pgh.pa.us
  Subject: Re: Why is this doing a seq scan?
  In-Reply-To: <01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com>
  References: <01CCE949D2717845BA2E573DC081167E052FB0@BKMAIL.sfsinternal.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox