X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 566C29FA5BA for ; Thu, 22 Jun 2006 16:30:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 30881-09 for ; Thu, 22 Jun 2006 16:30:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8EAB99FA2F0 for ; Thu, 22 Jun 2006 16:30:29 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5MJUOIg002852; Thu, 22 Jun 2006 15:30:24 -0400 (EDT) To: "Andrus" cc: pgsql-performance@postgresql.org Subject: Re: why group expressions cause query to run forever In-reply-to: References: Comments: In-reply-to "Andrus" message dated "Thu, 22 Jun 2006 21:22:44 +0300" Date: Thu, 22 Jun 2006 15:30:24 -0400 Message-ID: <2851.1151004624@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/455 X-Sequence-Number: 19812 "Andrus" writes: > How to speed the following query? It seems to run forever. > explain SELECT > bilkaib.DB, > CASE WHEN dbkonto.objekt1='+' THEN bilkaib.DBOBJEKT ELSE '' END AS dbobjekt, > CASE WHEN dbkonto.objekt2='+' THEN bilkaib.DB2OBJEKT ELSE '' END AS > db2objekt, > CASE WHEN dbkonto.objekt3='+' THEN bilkaib.DB3OBJEKT ELSE '' END AS > db3objekt, > ... > GROUP BY > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26 I think the problem is probably that you're sorting two dozen CHAR columns, and that in many of the rows all these entries are '' forcing the sort code to compare all two dozen columns (not so)? So the sort ends up doing lots and lots and lots of CHAR comparisons. Which can be slow, especially in non-C locales. What's your locale setting? regards, tom lane