X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 41905329C89 for ; Wed, 8 Sep 2004 15:56:29 +0100 (BST) 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 05153-09 for ; Wed, 8 Sep 2004 14:56:14 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 45C20329CBC for ; Wed, 8 Sep 2004 15:56:17 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i88EuI6b020326; Wed, 8 Sep 2004 10:56:18 -0400 (EDT) To: Marc Cousin Cc: pgsql-performance@postgresql.org Subject: Re: Problem with large query In-reply-to: <200409081650.00228.mcousin@sigma.fr> References: <200409081549.49537.mcousin@sigma.fr> <20149.1094654443@sss.pgh.pa.us> <200409081650.00228.mcousin@sigma.fr> Comments: In-reply-to Marc Cousin message dated "Wed, 08 Sep 2004 16:49:59 +0200" Date: Wed, 08 Sep 2004 10:56:17 -0400 Message-ID: <20325.1094655377@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/51 X-Sequence-Number: 8179 Marc Cousin writes: > The query has been generated by business objects ... i'ill try to suggest to the developpers to remove this constant (if they can)... > The fields used by the sort are of type numeric(6,0) or (10,0) ... > Could it be better if the fields were integer or anything else ? integer or bigint would be a WHOLE lot faster. I'd venture that comparing two numerics is order of a hundred times slower than comparing two integers. Even if you don't want to change the fields on-disk, you might think about casting them all to int/bigint in the query. Another thing that might or might not be easy is to change the order of the GROUP BY items so that the fields with the largest number of distinct values are listed first. If two rows are distinct at the first column, the sorting comparison doesn't even have to look at the remaining columns ... regards, tom lane