Hi, Here is the information that you requested.
The sub query that I am using is EXPLAIN ANALYZE SELECT doc.doc_documentid FROM document AS doc LEFT JOIN document as root ON doc.doc_internalRootXref = root.doc_documentId LEFT JOIN folder_document ON doc.doc_documentid = folder_document.doc_documentId LIMIT 500 OFFSET 0 The column doc_documentid is character varying(48) on both tables (document, folder_document). The column doc_internalRootXref is also character varying(48) doc_documentid and doc_internalRootXref are UUIDs that is 36 chars long. The document table has 58 columns. 31 columns are varchar ranging from size 8 to 80 7 booleans 4 numeric(12,2) 8 timestamp with time zone 1 integer 1 bigint 5 text The folder_documen table has 6 columns 4 varchar (2 of length 16 2 of length 48) The following indexes are on the document table pk_document primary key btree (doc_documentid), document_pk unique btree (doc_documentid), doc_deliverydate_index btree (doc_deliverydate), doc_externalxref_index btree (doc_externalxref), doc_internalparentomxref_index btree (doc_internalparentomxref), doc_internalrootxref_index btree (doc_internalrootxref) The following indexes are on the folder_document table pk_folder_document primary key btree (doc_documentid) fk_folder_document1 FOREIGN KEY (fld_folderid) REFERENCES folder(fld_folderid) ON UPDATE RESTRICT ON DELETE CASCADE, fk_folder_document2 FOREIGN KEY (doc_documentid) REFERENCES document(doc_documentid) ON UPDATE RESTRICT ON DELETE CASCADE After reading your hint about locale settings, I reinstalled postgres and made sure the locale was set to C and that the encoding was SQL_ASCII. (these are the settings on the cygwin installation). I still get the same results in the last post. -----Original Message----- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: September 21, 2005 8:13 PM To: Gurpreet Aulakh Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin) "Gurpreet Aulakh" <[EMAIL PROTECTED]> writes: > What is really interesting is the time it takes for the Hash to occur. For > the first hash, on the 7.3 it takes only 12ms while on the 8.0 it takes > 47ms. You haven't told us a thing about the column datatypes involved (much less what the query actually is) ... but I wonder if this is a textual datatype and the 8.0 installation is using a non-C locale where the 7.3 installation is using C locale. That could account for a considerable slowdown in text comparison speeds. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings