From: Andrus <eetasoft@online.ee>
To: pgsql-performance@postgresql.org
Subject: Re: why group expressions cause query to run forever
Date: Tue, 27 Jun 2006 20:50:23 +0300
Message-ID: <e7rrqn$q96$1@news.hub.org> (raw)
References: <e7en5r$gm1$1@news.hub.org>
<2851.1151004624@sss.pgh.pa.us>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: eetasoft@online.ee
Subject: Re: why group expressions cause query to run forever
In-Reply-To: <e7rrqn$q96$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox