From: "Gurpreet Aulakh" <gaulakh@ecmarket.com>
To: <pgsql-performance@postgresql.org>
Subject: Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin) 
Date: Thu, 22 Sep 2005 11:54:11 -0700
Message-ID: <IGEGINLEEDLANNHAAKCMOECJCCAA.gaulakh@ecmarket.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <24008.1127358777@sss.pgh.pa.us>

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@sss.pgh.pa.us]
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" <gaulakh@ecmarket.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