Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
Thanks for your help Tom. While testing 8.1, I found that simple joins take longer in 8.1 than 8.0. For example the sub query SELECT doc.doc_documentid FROM document AS doc LEFT JOIN folder_document ON doc.doc_documentid = folder_document.doc_documentId LEFT JOIN document as root ON doc.doc_internalRootXref = root.doc_documentId is actually slower on 8.1 than 8.0. However, the full query that I will be running is much faster. In my evaluation I found the same pattern. That simple joins were slower but complex joins were faster. Overall though, 8.1 is faster and we will probably be moving to it when it's officially released. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: September 23, 2005 2: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: After further investigation I have found that the reason why the query is slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3. So the question comes down to : Why are hash and hash joins slower? I looked into this a bit and determined that the problem seems to have been introduced here: 2002-12-30 10:21 tgl * src/: backend/executor/nodeHash.c, backend/executor/nodeHashjoin.c, backend/optimizer/path/costsize.c, include/executor/nodeHash.h: Better solution to integer overflow problem in hash batch-number computation: reduce the bucket number mod nbatch. This changes the association between original bucket numbers and batches, but that doesn't matter. Minor other cleanups in hashjoin code to help centralize decisions. (which means it's present in 7.4 as well as 8.0). The code now groups tuples into hash batches according to (hashvalue % totalbuckets) % nbatch When a tuple that is not in the first batch is reloaded, it is placed into a bucket according to (hashvalue % nbuckets) This means that if totalbuckets, nbatch, and nbuckets have a common factor F, the buckets won't be evenly used; in fact, only one in every F buckets will be used at all, the rest remaining empty. The ones that are used accordingly will contain about F times more tuples than intended. The slowdown comes from having to compare these extra tuples against the outer-relation tuples. 7.3 uses a different algorithm for grouping tuples that avoids this problem, but it has performance issues of its own (in particular, to avoid integer overflow we have to limit the number of batches we can have). So just reverting this patch doesn't seem very attractive. The problem no longer exists in 8.1 because of rewrites undertaken for another purpose, so I'm sort of tempted to do nothing. To fix this in the back branches we'd have to develop new code that won't ever go into CVS tip and thus will never get beta-tested. The risk of breaking things seems higher than I'd like. If we did want to fix it, my first idea is to increment nbatch looking for a value that has no common factor with nbuckets. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
Gurpreet Aulakh [EMAIL PROTECTED] writes: While testing 8.1, I found that simple joins take longer in 8.1 than 8.0. For example the sub query SELECT doc.doc_documentid FROM document AS doc LEFT JOIN folder_document ON doc.doc_documentid = folder_document.doc_documentId LEFT JOIN document as root ON doc.doc_internalRootXref = root.doc_documentId is actually slower on 8.1 than 8.0. With no more detail than that, this report is utterly unhelpful. Let's see the table schemas and the EXPLAIN ANALYZE results in both cases. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
After further investigation I have found that the reason why the query is slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3. So the question comes down to : Why are hash and hash joins slower? Is this a postgres configuration setting that I am missing? Is the locale still screwing me up? I have set the locale to 'C' without any improvements. Is it because the column type is a varchar that the hash is slower? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
Gurpreet Aulakh [EMAIL PROTECTED] writes: After further investigation I have found that the reason why the query is slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3. So the question comes down to : Why are hash and hash joins slower? I looked into this a bit and determined that the problem seems to have been introduced here: 2002-12-30 10:21 tgl * src/: backend/executor/nodeHash.c, backend/executor/nodeHashjoin.c, backend/optimizer/path/costsize.c, include/executor/nodeHash.h: Better solution to integer overflow problem in hash batch-number computation: reduce the bucket number mod nbatch. This changes the association between original bucket numbers and batches, but that doesn't matter. Minor other cleanups in hashjoin code to help centralize decisions. (which means it's present in 7.4 as well as 8.0). The code now groups tuples into hash batches according to (hashvalue % totalbuckets) % nbatch When a tuple that is not in the first batch is reloaded, it is placed into a bucket according to (hashvalue % nbuckets) This means that if totalbuckets, nbatch, and nbuckets have a common factor F, the buckets won't be evenly used; in fact, only one in every F buckets will be used at all, the rest remaining empty. The ones that are used accordingly will contain about F times more tuples than intended. The slowdown comes from having to compare these extra tuples against the outer-relation tuples. 7.3 uses a different algorithm for grouping tuples that avoids this problem, but it has performance issues of its own (in particular, to avoid integer overflow we have to limit the number of batches we can have). So just reverting this patch doesn't seem very attractive. The problem no longer exists in 8.1 because of rewrites undertaken for another purpose, so I'm sort of tempted to do nothing. To fix this in the back branches we'd have to develop new code that won't ever go into CVS tip and thus will never get beta-tested. The risk of breaking things seems higher than I'd like. If we did want to fix it, my first idea is to increment nbatch looking for a value that has no common factor with nbuckets. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 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:[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
Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
I have started to break my query down and analyze each piece. What I have discovered is very interesting. First here is a small piece of my query. 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 When I run this on Postgres 8.0.3 running under windows this is the result QUERY PLAN Limit (cost=183.49..753.41 rows=500 width=40) (actual time=47.000..79.000 rows=500 loops=1) - Hash Left Join (cost=183.49..6702.23 rows=5719 width=40) (actual time=47.000..79.000 rows=500 loops=1) Hash Cond: ((outer.doc_documentid)::text = (inner.doc_documentid)::text) - Merge Left Join (cost=0.00..6432.96 rows=5719 width=40) (actual time=0.000..16.000 rows=500 loops=1) Merge Cond: ((outer.doc_internalrootxref)::text = (inner.doc_documentid)::text) - Index Scan using doc_internalrootxref_index on document doc (cost=0.00..3172.64 rows=5719 width=80) (actual time=0.000..0.000 rows=500 loops=1) - Index Scan using pk_document on document root (cost=0.00..3174.53 rows=5719 width=40) (actual time=0.000..0.000 rows=863 loops=1) - Hash (cost=169.19..169.19 rows=5719 width=40) (actual time=47.000..47.000 rows=0 loops=1) - Seq Scan on folder_document (cost=0.00..169.19 rows=5719 width=40) (actual time=0.000..16.000 rows=5719 loops=1) Total runtime: 79.000 ms Here is the result of running the same query on the Postgres 7.3 running under Cygwin QUERY PLAN Limit (cost=183.49..775.31 rows=500 width=160) (actual time=13.00..44.00 rows=500 loops=1) - Hash Join (cost=183.49..6952.79 rows=5719 width=160) (actual time=13.00..44.00 rows=501 loops=1) Hash Cond: (outer.doc_documentid = inner.doc_documentid) - Merge Join (cost=0.00..6612.03 rows=5719 width=120) (actual time=0.00..29.00 rows=775 loops=1) Merge Cond: (outer.doc_internalrootxref = inner.doc_documentid) - Index Scan using doc_internalrootxref_index on document doc (cost=0.00..3254.39 rows=5719 width=80) (actual time=0.00..7.00 rows=775 loops=1) - Index Scan using pk_document on document root (cost=0.00..3257.88 rows=5719 width=40) (actual time=0.00..15.00 rows=1265 loops=1) - Hash (cost=169.19..169.19 rows=5719 width=40) (actual time=12.00..12.00 rows=0 loops=1) - Seq Scan on folder_document (cost=0.00..169.19 rows=5719 width=40) (actual time=0.00..9.00 rows=5719 loops=1) Total runtime: 45.00 msec 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. Now the databases are created from the same data and I have run vacuumdb -f -z on the databases. Now I have read something on the archives that stated that perhaps the data is in the filesystem (not database) cache. Would this be the case?. If so how would I improve the performance under WIN2K? Anyone have any ideas? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Gurpreet Aulakh Sent: September 21, 2005 12:38 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin) I currently have a Postgres 7.3 database running under WIN2K using cygwin and want to move to Postgres 8.0.3 (native windows version). I am finding most simple queries are significantly faster on the native windows version compared to 7.3 (under cygwin). However, for a complex query, that involve multiple JOINs, the 7.3 version is actually faster (about 2X faster). The query that I am running was optimized to run under 7.3. It was specifically modified to control the planner with explicit JOINs. When I run the same query on the 8.0.3 version with the join_collapse_limit set to 1 the query is slower. Can someone tell me why setting the join_collapse_limit to 1 in the 8.0 version does not produce similar results to the 7.3 version? Does anyone have any suggestions on what I can do? Do I have to rewrite the query? Here are the results of an explain analyze on the query. Explain analyze Postgres 7.3 running on WIN2K using cygwin. Hash Join (cost=21808.27..1946264.80 rows=2982 width=1598) (actual time=2186.00..2320.00 rows=50 loops=1) Hash Cond: (outer.doc_internalparentomxref = inner.doc_documentid) - Hash Join (cost=20948.78..1945323.29 rows=2982 width=1534) (actual time=2110.00..2227.00 rows=50 loops=1) Hash Cond: (outer.doc_internalrootxref = inner.doc_documentid) - Hash Join (cost=20089.29..1944381.79 rows=2982 width=1484) (actual time=2067.00..2179.00 rows=50 loops=1) Hash Cond: (outer.doc_documentid = inner.doc_documentid) Join Filter: (inner.dc_doccontacttype = 'FROM'::character varying) - Hash Join
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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
