We have recently converted from MS SQLServer 7 to Postgres 7.1... The
following query runs perfectly fine in SQL Server with any where
clause attached, but in Postgres runs fine until you put a where
clause that has *no* criteria for Patients... then it runs forever...
the table sizes are approx:
Patients: 1.5M rows
Dr: 2000 rows
Dr Groups: 500 rows
Rx: 750000 rows
All of the fields that are joined and used in where clauses are
indexed, and the query that has problems is:
SELECT
Pharm.Name, Pharm.City, Pharm.Phone,
Dr.LastName, Dr.City, Dr.Phone,
DrGroup.Name,
P.LastName, P.FirstName,P.DOB,
Rx.Medication, Rx.Unit, Rx.Qty
FROM Rx
INNER JOIN Pharm ON Rx.PharmID = Pharm.PharmID
INNER JOIN Dr ON Rx.DrID = Dr.DrID
INNER JOIN Patient P ON Rx.PatientID = P.PatientID
LEFT OUTER JOIN DrGroup ON Dr.DrGroupID = DrGroup.DrGroupID
I figure that there must be some other way to get it to run...
Any help is appreciated...
JH