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:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 3:34 PM To: '[EMAIL PROTECTED]' 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org