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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gaulakh@ecmarket.com
Subject: Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
In-Reply-To: <IGEGINLEEDLANNHAAKCMOECJCCAA.gaulakh@ecmarket.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox