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 B661E9FA437
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 27 Jun 2006 15:02:09 -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 85765-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 27 Jun 2006 15:02:04 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2])
	by postgresql.org (Postfix) with ESMTP id 016239FA10A
	for <pgsql-performance@postgresql.org>;
	Tue, 27 Jun 2006 15:02:03 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id C844930925; Tue, 27 Jun 2006 20:02:02 +0200 (MET DST)
From: "Andrus" <eetasoft@online.ee>
X-Newsgroups: pgsql.performance
Subject: Re: why group expressions cause query to run forever
Date: Tue, 27 Jun 2006 20:50:23 +0300
Organization: Hub.Org Networking Services
Lines: 41
Message-ID: <e7rrqn$q96$1@news.hub.org>
References: <e7en5r$gm1$1@news.hub.org> <2851.1151004624@sss.pgh.pa.us>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
X-RFC2646: Format=Flowed; Original
To: pgsql-performance@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/482
X-Sequence-Number: 19839

> 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)?

Yes, most of columns return empty strings.

I changed empty strings to null, casted to varchar and simplyfied the 
statment.
However, this select statement runs forever.

Any idea how to speed it up ?

Andrus.

SELECT
bilkaib.DB,
CASE WHEN dbkonto.objekt1='+' THEN bilkaib.DBOBJEKT ELSE null 
END::VARCHAR(10) AS dbobjekt,
CASE WHEN dbkonto.objekt2='+' THEN bilkaib.DB2OBJEKT ELSE null 
END::VARCHAR(10) AS db2objekt,
CASE WHEN dbkonto.objekt3='+' THEN bilkaib.DB3OBJEKT ELSE null 
END::VARCHAR(10) AS db3objekt,
CASE WHEN dbkonto.objekt4='+' THEN bilkaib.DB4OBJEKT ELSE null 
END::VARCHAR(10) AS db4objekt,
CASE WHEN dbkonto.objekt5='+' THEN bilkaib.DB5OBJEKT ELSE null 
END::VARCHAR(10) AS db5objekt,
CASE WHEN dbkonto.objekt6='+' THEN bilkaib.DB6OBJEKT ELSE null 
END::VARCHAR(10) AS db6objekt,
CASE WHEN dbkonto.objekt7='+' THEN bilkaib.DB7OBJEKT ELSE null 
END::VARCHAR(10) AS db7objekt,
CASE WHEN dbkonto.objekt8='+' THEN bilkaib.DB8OBJEKT ELSE null 
END::VARCHAR(10) AS db8objekt,
CASE WHEN dbkonto.objekt9='+' THEN bilkaib.DB9OBJEKT ELSE null 
END::VARCHAR(10) AS db9objekt
  from BILKAIB join KONTO CRKONTO ON bilkaib.cr=crkonto.kontonr
  join KONTO DBKONTO ON bilkaib.db=dbkonto.kontonr
     where
 bilkaib.kuupaev BETWEEN '2006-01-01' AND '2006-12-31'
  GROUP BY 1,2,3,4,5,6,7,8,9,10