Skip site navigation (1) Skip section navigation (2)

Re: why group expressions cause query to run forever

From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: why group expressions cause query to run forever
Date: 2006-06-27 17:50:23
Message-ID: e7rrqn$q96$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
> 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



In response to

pgsql-performance by date

Next:From: Simon RiggsDate: 2006-06-27 22:04:17
Subject: Re: Large index scan perfomance and indexCorrelation (PG
Previous:From: Leandro GuimarĂ£es dos SantosDate: 2006-06-27 16:46:56
Subject: unregister

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group