We have two different instances of Postgresql running on two different
Redhat boxes:
OS: DB1 - Red Hat Linux release 7.1sbe (Seawolf)
DB2 - Red Hat Linux release 7.2 (Enigma)
The postgres version for each box is 7.2.1.
When we run a simple query:
SELECT ord_contact_name.l_name
FROM ord_contact_name
JOIN ord_dv_job_num
USING (ord_obj_guid)
WHERE (ord_dv_job_num.dv_job_number ='1334298')
These are the EXPLAIN results on DB1:
NOTICE: QUERY PLAN:
Merge Join (cost=152183.00..271426.66 rows=9416137 width=105)
-> Sort (cost=141295.60..141295.60 rows=613709 width=69)
-> Seq Scan on ord_contact_name (cost=0.00..18568.09
rows=613709 width=69)
-> Sort (cost=10887.40..10887.40 rows=3069 width=36)
-> Index Scan using dv_job_number_ord_dv_job_num_ke on
ord_dv_job_num (cost=0.00..10709.67 rows=3069 width=36)
And the EXPLAIN results on DB2:
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..9.91 rows=1 width=90)
-> Index Scan using dv_job_number_ord_dv_job_num_ke on
ord_dv_job_num (cost=0.00..3.89 rows=1 width=40)
-> Index Scan using ord_obj_guid_ord_contact_name_k on
ord_contact_name (cost=0.00..6.01 rows=1 width=50)
Usually, this means that the indices are corrupt, but we have reindexed
many times. Does anyone know why DB1 is so much slower than DB2?
Memory is the same on both machines - over a Gig of RAM.
DB1 and DB2 both are SMP and DB1 uses hardware RAID. However, DB1 is
running on two different nodes (if one ethernet card shuts down, the
other can still be accessed).
Does anyone have any suggestions?
Thank you
Afra
