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(at)verisign(dot)com]
Sent: Thursday, March 14, 2002 3:34 PM
To: 'pgsql-admin(at)postgresql(dot)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