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

Re: why group expressions cause query to run forever

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Andrus" <eetasoft(at)online(dot)ee>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: why group expressions cause query to run forever
Date: 2006-06-22 19:30:24
Message-ID: 2851.1151004624@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Andrus" <eetasoft(at)online(dot)ee> 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

In response to

Responses

pgsql-performance by date

Next:From: Jim NasbyDate: 2006-06-22 21:15:13
Subject: Re: Tuning New Server (slow function)
Previous:From: AndrusDate: 2006-06-22 18:22:44
Subject: why group expressions cause query to run forever

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