FW: seq scan on indexed column

Haven't get any reply yet. I like to post more information:

select * from pg_statistic where starelid=(select oid from
pg_class where relname='gtld_owner');

 starelid | staattnum | stanullfrac | stawidth | stadistinct | stakind1 |
stakind2 | stakind3 | stakind4 | staop1 | staop2 | staop3 | staop4 |
stanumbers1                                        | stanumbers2 |
stanumbers3 | stanumbers4 |
stavalues1                                                            |
stavalues2                                                  | stavalues3 |
stavalues4
----------+-----------+-------------+----------+-------------+----------+---
-------+----------+----------+--------+--------+--------+--------+----------
----------------------------------------------------------------------------
----+-------------+-------------+-------------+-----------------------------
----------------------------------------------------------------------------
-------------------------+--------------------------------------------------
-----------------------------------------------------------+------------+---
---------
 13448623 |         2 |           0 |        4 |          -1 |        2 |
3 |        0 |        0 |     97 |     97 |      0 |      0 |
| {0.347379}  |             |             |
{-2147483648,-2080373222,-2080321278,-2080278168,-2080169190,-1115939844,-10
54754368,716046336,1208026624,1358111104,1521483776} |
|            |
 13448623 |         3 |           0 |        4 |          -1 |        2 |
3 |        0 |        0 |     97 |     97 |      0 |      0 |
| {0.347379}  |             |             |
{-2130706433,-2080373221,-2080321025,-2080278168,-2080169187,-1115939843,-10
54754368,716111871,1208027135,1358111135,1522008063} |
|            |
 13448623 |         4 |   0.0516667 |        7 |          74 |        1 |
2 |        3 |        0 |   1062 |   1066 |   1066 |      0 |
{0.710667,0.0353333,0.035,0.0256667,0.0196667}
|             | {0.519294}  |             | {usa,deu,can,gbr,aus}
| {alb,bra,cze,esp,fra,ita,jpn,mng,nor,sgp,zaf}
|            |
 13448623 |         5 |           0 |        2 |           4 |        1 |
2 |        3 |        0 |     94 |     95 |     95 |      0 | {0.961}
|             | {0.997345}  |             | {5}
| {0,0,3}
|            |
 13448623 |         6 |    0.166667 |        6 |         179 |        1 |
2 |        3 |        0 |   1062 |   1066 |   1066 |      0 |
{0.111333,0.0706667,0.0606667,0.038,0.036,0.0343333,0.031,0.0306667,0.027333
3,0.026}     |             | {0.0767722} |             |
{ca,ma,tx,ny,wa,dc,in,il,ga,va}
| {01,az,ct,fl,md,mn,nj,oh,or,si,zp}
|            |
 13448623 |         7 |           0 |        2 |           6 |        1 |
3 |        0 |        0 |     94 |     95 |      0 |      0 |
{0.385,0.208333,0.176333,0.153,0.0743333,0.003}
| {0.728691}  |             |             | {4,5,3,0,2,1}
|
|            |
 13448623 |         8 |    0.213333 |       12 |   -0.158388 |        1 |
2 |        3 |        0 |   1062 |   1066 |   1066 |      0 |
{0.0343333,0.0306667,0.0286667,0.0283333,0.0253333,0.024,0.023,0.0213333,0.0
2,0.0176667} |             | {0.153757}  |             |
{washington,burlington,everett,indianapolis,"new
york",atlanta,chicago,carrollton,"palo alto",cleveland}
| {aachen,berlin,cheyenne,dusseldorf,hicksville,"los
angeles",nashville,phoenix,"san francisco",tampa,zurich} |            |
 13448623 |         9 |           0 |        2 |           6 |        1 |
3 |        0 |        0 |     94 |     95 |      0 |      0 |
{0.356333,0.213333,0.19,0.127,0.108333,0.005}
| {0.714766}  |             |             | {4,0,3,2,5,1}
|
|            |
 13448623 |        10 |           0 |       12 |   -0.129823 |        1 |
2 |        3 |        0 |   1752 |   1754 |   1754 |      0 |
{0.241667,0.0343333,0.03,0.0286667,0.0283333,0.0256667,0.0246667,0.023,0.021
3333,0.021}  |             | {0.4584}    |             |
{0.0000,38.8950,42.5000,47.9670,39.7670,40.7000,33.7330,41.8500,32.9500,34.0
500}                                                 |
{-45.8670,31.5670,35.6850,37.4330,38.3330,39.7330,41.1170,42.3170,42.7500,48
.7670,61.3330}                  |            |
 13448623 |        11 |           0 |       11 |   -0.142614 |        1 |
2 |        3 |        0 |   1752 |   1754 |   1754 |      0 |
{0.241667,0.0343333,0.0293333,0.029,0.0283333,0.0253333,0.024,0.0233333,0.02
13333,0.02}  |             | {-0.18736}  |             |
{0.0000,-77.0370,-71.1830,-122.2000,-86.1500,-74.0000,-84.3830,-87.6500,-96.
8830,-122.1330}                                      |
{-157.8580,-121.8830,-117.2000,-96.8000,-86.7840,-81.6830,-77.4170,-74.6670,
-71.1000,6.7670,174.7830}       |            |
(10 rows)


Stephan Szabo, Could you please take a look, I know you can help!!!


Anna Zhang

-----Original Message-----
From: Zhang, Anna [mailto:azhang@verisign.com]
Sent: Thursday, March 14, 2002 3:34 PM
To: 'pgsql-admin@postgresql.org'
Subject: [ADMIN] seq scan on indexed column


Hi,
I always have questions on sql tunning, here is the one:

gtld_analysis=# \d gtld_owner

                  Table "gtld_owner"
   Attribute    |          Type          |  Modifier
----------------+------------------------+------------
 owner_name     | character varying(100) |
 netblock_start | integer                | not null    /* primary key */
 netblock_end   | integer                | not null
 country        | character varying(100) |
 country_c      | smallint               | default -1
 region         | character varying(100) |
 region_c       | smallint               | default -1
 city           | character varying(100) |
 city_c         | smallint               | default -1
 lat            | numeric(9,4)           |
 long           | numeric(9,4)           |
Indices: gtld_owner_pkey,
         owner_nb_end

gtld_analysis=# select count(*) from gtld_owner;
  count
---------
 2174335
(1 row)

gtld_analysis=# explain SELECT NETBLOCK_START
gtld_analysis-# FROM GTLD_OWNER
gtld_analysis-# WHERE NETBLOCK_START = -2147483648;
NOTICE:  QUERY PLAN:

Seq Scan on gtld_owner  (cost=0.00..80021.37 rows=23 width=4)

EXPLAIN


Why it didn't use index scan? what rows=23 means? We have an application
which loops each row and select netblock_start, and it is slow overall.

Anyone can give me a clue? Thanks!

Anna Zhang



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html