Re: 7.1.3 not using index

>>>Tom Lane said:
> Daniel Kalchev <daniel(at)digsys(dot)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" );
>
> > (part of 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 thing is well aware that the query is
> selective (note the rows estimate), so why is it not using the index?
>
> Do you get the 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 configure and build, *exactly*?
> Did you make any midcourse corrections (like building some of the files
> with different compiler switches 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