We have a MS SQL server db that we successfully migrated to pgsql 8.2
and I am now working on some views and notice select queries being very
slow. I have vacuumed last night and running the exact same query (with
minor changes to syntax of course), it runs in just a few seconds
compared to several minutes in pgsql. Since I've never dealt with MS
SQL, I wanted to ask here if this is expected performance for the type
of query *or* do I need to just learn how to properly tune my
performance on the pgsql server? Perhaps some commands or tests may help
me determine where issues may lie?
I am running the following query on a linux server with comparable
processor and memory as the windows server. The query was just taken
from the SQL server as is and adjusted teh syntax...the query only
returns 3 records, but several tables have tens of thousands of records,
the tblactivitytag table has over 100K...
SELECT distinct A.fldClientNumber as cNumber, A.fldClientName as cName,
B.fldContactNumber as contactNumber, B.fldContactCity as cCity,
B.fldContactState as cState, B.fldContactFirstName as contactFName,
B.fldContactLastName as contactLName, B.fldContactEmail as ContactEmail,
B.fldContactTitle as cTitle, B.fldContactPhone1_Num as B1Phonenumber,
B.fldContactPhone4_Type as Num4Type, B.fldContactPhone4_Num as CellNum
FROM tblClientMaster A, tblContactInfo B,tblClientProductPreference C,
tblClientRoomSize D,tblProductMaster F, tblClientProductRelation G,
tblclientcomments H, tblgeopreference E ,tblClientActivityTag
WHERE
A.fldClientNumber = B.fldClientNumber AND
A.fldClientNumber = C.fldClientNumber AND
A.fldClientNumber = D.fldClientNumber AND
A.fldClientName ilike '%ADVISOR%' AND
B.fldContactFirstName ilike '%%%' AND
A.fldClientNumber = G.fldClientNumber AND
G.fldProductNumber = F.fldProductNumber AND
F.fldProductName ilike '%%%' AND
A.fldClientNumber = H.fldClientNumber AND
H.fldenable = 't' AND
H.fldcontactnumber = b.fldcontactnumber AND
H.fldClientcomments ilike '%%%' AND
(A.fldBuyingStatus = 'Now' ) AND
(A.fldSellingStatus = 'Now' ) AND
(C.fldFullService = 't' ) AND
(D.fldSize149 = 't' ) AND
(E.fldW = 't' ) AND
A.fldClientNumber = E.fldClientNumber AND
A.fldclientnumber = tblClientActivityTag.fldclientnumber AND
tblClientActivityTag.fldcontactnumber = b.fldcontactnumber AND
tblClientActivityTag.fldcontactactivitytag like 'A%' AND
b.fldcontactnumber in (select fldcontactnumber from tblclientcomments where
tblclientcomments$
A.fldEnable = 't' AND B.fldEnable = 't'
ORDER BY A.fldClientName, B.fldContactLastName;
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
