Query optimisation on two machines is different.

I have Redhat 6.2 installed on two very different machines. One is a Dual
processor xeon with 512Mb RAM and RAID disks, the other is for development and
is a PII-266 with an IDE disk. I have PostgreSQL 7.0 installed on both from
the same RPMS and two identical databases (I used pg_dump on the production
machine and psql -e to read it into the development machine). However there
is one query which uses an index on the Big Machine but not on the Small
Machine.

This is the explanation on the Big One...

db=# explain verbose SELECT tagid, branchno, highest FROM revtag WHERE revid=127056;
NOTICE: QUERY DUMP:

{ INDEXSCAN :startup_cost 0.00 :total_cost 1538.23 :rows 391 :width 12 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname tagid :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 23 :restypmod -1 :resname branchno :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 23 :restypmod -1 :resname highest :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 4}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 93751285) :indxqual (({ 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 2} { CONST :consttype 23 :constlen 4 :constisnull false :constvalue 4 [ 80 -16 1 0 ] :constbyval true })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST :consttype 23 :constlen 4 :constisnull false :constvalue 4 [ 80 -16 1 0 ] :constbyval true })})) :indxorderdir 1 }
NOTICE: QUERY PLAN:

Index Scan using revtag_revid on revtag (cost=0.00..1538.23 rows=391 width=12)

EXPLAIN
db=# \d revtag
Table "revtag"
Attribute | Type | Modifier
-----------+---------+----------
tagid | integer | not null
revid | integer | not null
branchno | integer | not null
highest | integer |
Indices: revtag_revid,
revtag_tagid

And this is the explanation on the Little One...

db=# explain verbose SELECT tagid, branchno, highest FROM revtag WHERE revid=127056;
NOTICE: QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 95980.51 :rows 50865 :width 12 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname tagid :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 23 :restypmod -1 :resname branchno :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 23 :restypmod -1 :resname highest :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 4}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup!
!
0 :varnoold 1 :varoattno 2} { CONST :consttype 23 :constlen 4 :constisnull false :constvalue 4 [ 80 -16 1 0 ] :constbyval true })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 }
NOTICE: QUERY PLAN:

Seq Scan on revtag (cost=0.00..95980.51 rows=50865 width=12)

EXPLAIN
db=# \d revtag
Table "revtag"
Attribute | Type | Modifier
-----------+---------+----------
tagid | integer | not null
revid | integer | not null
branchno | integer | not null
highest | integer |
Indices: revtag_revid,
revtag_tagid

I cannot understand why there is a difference, could someone enlighten me? I
should add that I did have 7.0.2 on the Little One, but downgraded to 7.0 so
that both were as near the same as possible.

--
Regards,

Martin Tomes

Martin(dot)Tomes(at)controls(dot)eurotherm(dot)co(dot)uk