Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)

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:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: September 21, 2005 8:13 PM
To: Gurpreet Aulakh
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)

"Gurpreet Aulakh" <gaulakh(at)ecmarket(dot)com> 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