Query optimisation on two machines is different. - Mailing list pgsql-general

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
:varnoold1 :varoattno 2} { CONST :consttype 23 :constlen 4 :constisnull false :constvalue  4 [ 80 -16 1 0 ]
:constbyvaltrue })})) :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
:ressortgroupref0 :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
:varno1 :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 ]
:constbyvaltrue })}) :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.Tomes@controls.eurotherm.co.uk