X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C0ED5568B7 for ; Tue, 15 Mar 2005 19:53:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92330-09 for ; Tue, 15 Mar 2005 19:53:15 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id C9408568A5 for ; Tue, 15 Mar 2005 19:53:14 +0000 (GMT) Received: (qmail 9887 invoked from network); 15 Mar 2005 20:53:19 +0100 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 15 Mar 2005 20:53:19 +0100 Date: Tue, 15 Mar 2005 20:53:13 +0100 To: "Tom Lane" , pgsql-hackers@postgresql.org Subject: Re: [PERFORM] Avoiding tuple construction/deconstruction during joining Cc: =?iso-8859-15?B?TWlyb3NsYXYgpnVsYw==?= , pgsql-performance@postgresql.org References: <42345D24.8070104@startnet.cz> <14577.1110821062@sss.pgh.pa.us> From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <14577.1110821062@sss.pgh.pa.us> User-Agent: Opera M2/7.54 (Linux, build 955) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/723 X-Sequence-Number: 65525 On my machine (Laptop with Pentium-M 1.6 GHz and 512MB DDR333) I get the following timings : Big Joins Query will all the fields and no order by (I just put a SELECT * in the first table) yielding about 6k rows : => 12136.338 ms Replacing the SELECT * from the table with many fields by just a SELECT of the foreign key columns : => 1874.612 ms I felt like playing a bit so I implemented a hash join in python (download the file, it works on Miroslav's data) : All timings do not include time to fetch the data from the database. Fetching all the tables takes about 1.1 secs. * With something that looks like the current implementation (copying tuples around) and fetching all the fields from the big table : => Fetching all the tables : 1.1 secs. => Joining : 4.3 secs * Fetching only the integer fields => Fetching all the tables : 0.4 secs. => Joining : 1.7 secs * A smarter join which copies nothing and updates the rows as they are processed, adding fields : => Fetching all the tables : 1.1 secs. => Joining : 0.4 secs With the just-in-time compiler activated, it goes down to about 0.25 seconds. First thing, this confirms what Tom said. It also means that doing this query in the application can be a lot faster than doing it in postgres including fetching all of the tables. There's a problem somewhere ! It should be the other way around ! The python mappings (dictionaries : { key : value } ) are optimized like crazy but they store column names for each row. And it's a dynamic script language ! Argh. Note : run the program like this : python test.py |less -S So that the time spent scrolling your terminal does not spoil the measurements. Download test program : http://boutiquenumerique.com/pf/miroslav/test.py