X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E125947580B for ; Wed, 19 Feb 2003 05:34:35 -0500 (EST) Received: from email04.aon.at (WARSL402PIP1.highway.telekom.at [195.3.96.90]) by postgresql.org (Postfix) with SMTP id 0A44A475458 for ; Wed, 19 Feb 2003 05:34:35 -0500 (EST) Received: (qmail 493298 invoked from network); 19 Feb 2003 10:34:35 -0000 Received: from m148p005.dipool.highway.telekom.at (HELO cantor) ([62.46.8.101]) (envelope-sender ) by qmail4rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 19 Feb 2003 10:34:35 -0000 From: Manfred Koizar To: Chantal Ackermann Cc: Josh Berkus , pgsql-performance@postgresql.org, tgl@sss.pgh.pa.us Subject: Re: cost and actual time Date: Wed, 19 Feb 2003 11:34:03 +0100 Message-ID: References: <3E50BECC.9090107@biomax.de> <3E520AD8.2000304@biomax.de> <3E5350AE.7080408@biomax.de> In-Reply-To: <3E5350AE.7080408@biomax.de> X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/127 X-Sequence-Number: 1183 Chantal, I'm short of time right now. So just a few quick notes and a request for more information. Next round tomorrow ... On Wed, 19 Feb 2003 10:38:54 +0100, Chantal Ackermann wrote: >yeah, I noticed the difference between the two queries. actually, I am >afraid of dropping the distinct cause I had results with duplicate rows >(though I shall recheck when this is really the case). These are the >table declarations and constraints: [...] AFAICS there's no way to get duplicates, so no need for DISTINCT. > we have to use bigint as type. is the join therefore >such expensive? No. >we had a primary key occurrence_id on the occurrences tables but we >noticed that we don't use it, so we didn't recreate it in the new >database. is it possible that the postgres could work with it internally? No. > Nested Loop Total runtime: 53508.86 msec >Merge Join: Total runtime: 113066.81 msec >Hash Join: Total runtime: 439344.44 msec >I changed the gene name at every run to avoid the caching. You can't compare the runtimes unless you query for the same data. Either run each query twice to make sure everything is cached or do something like tar cf /dev/null /some/big/directory before each query to empty your disk cache. BTW, what is your shared_buffers setting. >disabling the hash join results again in a Nested Loop with very high >cost but low runtime - I'm not sure if the latter is the consequence of >caching. Please send EXPLAIN ANALYZE results for all queries. Send it to me off-list if you think its too long. Servus Manfred