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

why group expressions cause query to run forever

From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-performance(at)postgresql(dot)org
Subject: why group expressions cause query to run forever
Date: 2006-06-22 18:22:44
Message-ID: e7en5r$gm1$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
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,
CASE WHEN dbkonto.objekt4='+' THEN bilkaib.DB4OBJEKT ELSE '' END AS 
db4objekt,
CASE WHEN dbkonto.objekt5='+' THEN bilkaib.DB5OBJEKT ELSE '' END AS 
db5objekt,
CASE WHEN dbkonto.objekt6='+' THEN bilkaib.DB6OBJEKT ELSE '' END AS 
db6objekt,
CASE WHEN dbkonto.objekt7='+' THEN bilkaib.DB7OBJEKT ELSE '' END AS 
db7objekt,
CASE WHEN dbkonto.objekt8='+' THEN bilkaib.DB8OBJEKT ELSE '' END AS 
db8objekt,
CASE WHEN dbkonto.objekt9='+' THEN bilkaib.DB9OBJEKT ELSE '' END AS 
db9objekt,
bilkaib.CR,
CASE WHEN crkonto.objekt1='+' THEN bilkaib.crOBJEKT ELSE '' END AS crobjekt,
CASE WHEN crkonto.objekt2='+' THEN bilkaib.cr2OBJEKT ELSE '' END AS 
cr2objekt,
CASE WHEN crkonto.objekt3='+' THEN bilkaib.cr3OBJEKT ELSE '' END AS 
cr3objekt,
CASE WHEN crkonto.objekt4='+' THEN bilkaib.cr4OBJEKT ELSE '' END AS 
cr4objekt,
CASE WHEN crkonto.objekt5='+' THEN bilkaib.cr5OBJEKT ELSE '' END AS 
cr5objekt,
CASE WHEN crkonto.objekt6='+' THEN bilkaib.cr6OBJEKT ELSE '' END AS 
cr6objekt,
CASE WHEN crkonto.objekt7='+' THEN bilkaib.cr7OBJEKT ELSE '' END AS 
cr7objekt,
CASE WHEN crkonto.objekt8='+' THEN bilkaib.cr8OBJEKT ELSE '' END AS 
cr8objekt,
CASE WHEN crkonto.objekt9='+' THEN bilkaib.cr9OBJEKT ELSE '' END AS 
cr9objekt,
bilkaib.RAHA,
CASE WHEN crkonto.klienkaupa OR dbkonto.klienkaupa
  OR crkonto.tyyp IN ('K','I') OR dbkonto.tyyp IN ('K','I')
THEN
  bilkaib.KLIENT ELSE '' END AS klient,

bilkaib.EXCHRATE,

CASE WHEN crkonto.klienkaupa OR dbkonto.klienkaupa
  OR crkonto.tyyp IN ('K','I') OR dbkonto.tyyp IN ('K','I')
THEN
  '' ELSE '' END AS kliendinim,  -- 24.

CASE WHEN crkonto.arvekaupa OR dbkonto.arvekaupa
  OR (bilkaib.cr<>'00' AND crkonto.tyyp='K')
  OR (bilkaib.db<>'00' AND dbkonto.tyyp='K')
THEN bilkaib.doknr ELSE CAST('' AS CHAR(25) ) END AS doknr

,CASE WHEN bilkaib.raha='EEK' THEN CAST('20060101' AS DATE) ELSE 
bilkaib.kuupaev END AS kuupaev
,SUM(bilkaib.summa) AS summa
,CAST( 0 as numeric(12,2)) as rhsumma
  from BILKAIB join KONTO CRKONTO ON bilkaib.cr=crkonto.kontonr AND
      crkonto.iseloom='A'
    join KONTO DBKONTO ON bilkaib.db=dbkonto.kontonr AND
      dbkonto.iseloom='A'
     where
 bilkaib.kuupaev BETWEEN '2006-01-01' AND '2006-12-31'
  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

"GroupAggregate  (cost=83038.02..103020.42 rows=124890 width=759)"
"  ->  Sort  (cost=83038.02..83350.25 rows=124890 width=759)"
"        Sort Key: bilkaib.db, CASE WHEN (dbkonto.objekt1 = '+'::bpchar) 
THEN bilkaib.dbobjekt ELSE ''::bpchar END, CASE WHEN (dbkonto.objekt2 = 
'+'::bpchar) THEN bilkaib.db2objekt ELSE ''::bpchar END, CASE WHEN 
(dbkonto.objekt3 = '+'::bpchar) THEN bilkaib. (..)"
"        ->  Hash Join  (cost=41.71..23348.23 rows=124890 width=759)"
"              Hash Cond: ("outer".cr = "inner".kontonr)"
"              ->  Hash Join  (cost=20.86..11676.02 rows=144696 width=707)"
"                    Hash Cond: ("outer".db = "inner".kontonr)"
"                    ->  Seq Scan on bilkaib  (cost=0.00..9369.99 
rows=167643 width=655)"
"                          Filter: ((kuupaev >= '2006-01-01'::date) AND 
(kuupaev <= '2006-12-31'::date))"
"                    ->  Hash  (cost=20.29..20.29 rows=227 width=66)"
"                          ->  Seq Scan on konto dbkonto  (cost=0.00..20.29 
rows=227 width=66)"
"                                Filter: (iseloom = 'A'::bpchar)"
"              ->  Hash  (cost=20.29..20.29 rows=227 width=66)"
"                    ->  Seq Scan on konto crkonto  (cost=0.00..20.29 
rows=227 width=66)"
"                          Filter: (iseloom = 'A'::bpchar)"


If I only replace column expressions with constant numbers, it runs fast:

explain analyze SELECT 1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6
,SUM(bilkaib.summa) AS summa
,CAST( 0 as numeric(12,2)) as rhsumma
  from BILKAIB join KONTO CRKONTO ON bilkaib.cr=crkonto.kontonr AND
      crkonto.iseloom='A'
    join KONTO DBKONTO ON bilkaib.db=dbkonto.kontonr AND
      dbkonto.iseloom='A'
     where
 bilkaib.kuupaev BETWEEN '2006-01-01' AND '2006-12-31'
  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


"HashAggregate  (cost=22099.33..22099.34 rows=1 width=11) (actual 
time=4518.820..4518.824 rows=1 loops=1)"
"  ->  Hash Join  (cost=41.71..13669.25 rows=124890 width=11) (actual 
time=4.347..3445.650 rows=167349 loops=1)"
"        Hash Cond: ("outer".cr = "inner".kontonr)"
"        ->  Hash Join  (cost=20.86..11676.02 rows=144696 width=25) (actual 
time=2.165..2076.951 rows=167349 loops=1)"
"              Hash Cond: ("outer".db = "inner".kontonr)"
"              ->  Seq Scan on bilkaib  (cost=0.00..9369.99 rows=167643 
width=39) (actual time=0.012..725.813 rows=167349 loops=1)"
"                    Filter: ((kuupaev >= '2006-01-01'::date) AND (kuupaev 
<= '2006-12-31'::date))"
"              ->  Hash  (cost=20.29..20.29 rows=227 width=14) (actual 
time=2.112..2.112 rows=227 loops=1)"
"                    ->  Seq Scan on konto dbkonto  (cost=0.00..20.29 
rows=227 width=14) (actual time=0.011..1.126 rows=227 loops=1)"
"                          Filter: (iseloom = 'A'::bpchar)"
"        ->  Hash  (cost=20.29..20.29 rows=227 width=14) (actual 
time=2.149..2.149 rows=227 loops=1)"
"              ->  Seq Scan on konto crkonto  (cost=0.00..20.29 rows=227 
width=14) (actual time=0.022..1.152 rows=227 loops=1)"
"                    Filter: (iseloom = 'A'::bpchar)"
"Total runtime: 4519.063 ms"

Postgres 8.1 on Gentoo Linux.

Andrus. 



Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-06-22 19:30:24
Subject: Re: why group expressions cause query to run forever
Previous:From: David WheelerDate: 2006-06-22 18:18:48
Subject: Re: Performance of DOMAINs

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