Re: 7.1.3 not using index - Mailing list pgsql-hackers

>>>Tom Lane said:> Daniel Kalchev <daniel@digsys.bg> writes:> > Here is another table:> > > CREATE TABLE "persons" (> >
       "personid" integer DEFAULT nextval('personid_seq'::text),> >         "name" text,> >         "title" text,> >
[...]>> );> > > CREATE  INDEX "persons_personid_idx" on "persons" using btree ( "personid"     > > "int4_ops" );> > >
(partof the statistics for this row)> >    attname   | attdispersion | starelid | staattnum | staop | stanullfrac     |
>> stacommonfrac |      stacommonval      |        staloval        |         > > stahival> >  personid    |
4.1328e-05|    19795 |         1 |    97 |           0     |   > > 0.000206469 | 2089                   | 1
        | 12857> > > now, EXPLAIN again gives me:> > > db=# explain select * from persons where personid = 1;> >
NOTICE: QUERY PLAN:> > > Seq Scan on persons  (cost=0.00..490.62 rows=1 width=177)> > That does seem pretty broken; the
thingis well aware that the query is> selective (note the rows estimate), so why is it not using the index?> > Do you
getthe same plan if you try to force an indexscan by doing>     set enable_seqscan to off;
 

Here is what it gives:

db=# set enable_seqscan to off;
SET VARIABLE
db=# explain select * from persons where personid = 1;
NOTICE:  QUERY PLAN:

Index Scan using persons_personid_idx on persons  (cost=0.00..nan rows=1 
width=177)

> > Also, I'd like to see the EXPLAIN VERBOSE result not just EXPLAIN.

Here it is (after turning enable_seqscan back on)


db=# explain verbose select * from persons where personid = 1;
NOTICE:  QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 490.62 :rows 1 :width 177 
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod 
-1 :resname personid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 
:restypmod -1 :resname name :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk 
false } :expr { VAR :varno 1 :varattno 2 :vartype 25 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM 
:resno 3 :restype 25 :restypmod -1 :resname title :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 
25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY 
:resdom { RESDOM :resno 4 :restype 25 :restypmod -1 :resname occupation 
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 
:varattno 4 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 
4}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 23 :restypmod -1 :resname 
person_type :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 25 :restypmod 
-1 :resname street :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 6 :vartype 25 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 25 
:restypmod -1 :resname town :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk 
false } :expr { VAR :varno 1 :varattno 7 :vartype 25 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 7}} { TARGETENTRY :resdom { RESDOM 
:resno 8 :restype 25 :restypmod -1 :resname zipcode :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 
25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY 
:resdom { RESDOM :resno 9 :restype 25 :restypmod -1 :resname phone :reskey 0 
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 
9 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 9}} { 
TARGETENTRY :resdom { RESDOM :resno 10 :restype 25 :restypmod -1 :resname fax 
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 
:varattno 10 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 
10}} { TARGETENTRY :resdom { RESDOM :resno 11 :restype 25 :restypmod -1 
:resname email :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr 
{ VAR :varno 1 :varattno 11 :vartype 25 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 11}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype 
16 :restypmod -1 :resname archived :reskey 0 :reskeyop 0 :ressortgroupref 0 
:resjunk false } :expr { VAR :varno 1 :varattno 12 :vartype 16 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom { RESDOM 
:resno 13 :restype 1043 :restypmod 20 :resname archived_by :reskey 0 :reskeyop 
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 13 
:vartype 1043 :vartypmod 20  :varlevelsup 0 :varnoold 1 :varoattno 13}} { 
TARGETENTRY :resdom { RESDOM :resno 14 :restype 1184 :restypmod -1 :resname 
archived_at :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 14 :vartype 1184 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 14}} { TARGETENTRY :resdom { RESDOM :resno 15 :restype 
1184 :restypmod -1 :resname created_at :reskey 0 :reskeyop 0 :ressortgroupref 
0 :resjunk false } :expr { VAR :varno 1 :varattno 15 :vartype 1184 :vartypmod 
-1  :varlevelsup 0 :varnoold 1 :varoattno 15}} { TARGETENTRY :resdom { RESDOM 
:resno 16 :restype 1043 :restypmod 20 :resname created_by :reskey 0 :reskeyop 
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 16 
:vartype 1043 :vartypmod 20  :varlevelsup 0 :varnoold 1 :varoattno 16}} { 
TARGETENTRY :resdom { RESDOM :resno 17 :restype 1184 :restypmod -1 :resname 
updated_at :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { 
VAR :varno 1 :varattno 17 :vartype 1184 :vartypmod -1  :varlevelsup 0 
:varnoold 1 :varoattno 17}} { TARGETENTRY :resdom { RESDOM :resno 18 :restype 
1043 :restypmod 20 :resname updated_by :reskey 0 :reskeyop 0 :ressortgroupref 
0 :resjunk false } :expr { VAR :varno 1 :varattno 18 :vartype 1043 :vartypmod 
20  :varlevelsup 0 :varnoold 1 :varoattno 18}}) :qpqual ({ EXPR :typeOid 16  
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR 
:varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull 
false :constvalue  4 [ 1 0 0 0 ] })}) :lefttree <> :righttree <> :extprm () 
:locprm () :initplan <> :nprm 0  :scanrelid 1 }
NOTICE:  QUERY PLAN:

Seq Scan on persons  (cost=0.00..490.62 rows=1 width=177)

> One thing that should be eliminated at the outset is the possibility of> a bad build of Postgres.  How did you
configureand build, *exactly*?> Did you make any midcourse corrections (like building some of the files> with different
compilerswitches than others)?
 


I will rebuild it again, re-initdb and reload the whole database, but this 
build was on vanilla BSD/OS 4.2 with the only modifications to add larger 
shared memory support in the kernel (I need to run many backends). My current 
favorite (which I copy from server to server :) is

# support for larger processes and number of childs
options         "DFLDSIZ=\(128*1024*1024\)"
options         "MAXDSIZ=\(256*1024*1024\)"   
options         "CHILD_MAX=256"
options         "OPEN_MAX=256"
options         "KMAPENTRIES=4000" # Prevents kmem malloc errors !
options         "KMEMSIZE=\(32*1024*1024\)"

options         "SHMMAXPGS=32768"
options         "SHMMNI=400"
options         "SHMSEG=204"
# More semaphores for Postgres
options         "SEMMNS=600"

PostgreSQL was build with these options

./configure  --enable-locale --with-perl --with-pythos --with-tcl 
--enable-obdc --with-krb4 --with-openssl --enable-syslog 
--with-includes=/usr/include/kerberosIV:/usr/contrib/include

I have the habbit to always clean before every build.

What I will do try to do now is to clean/rebuild/install everything again.
Then try to build with --enable-locale only. Then try to build without any 
options at all..

Hope you find some useful information to track this down.

Daniel