From: "ALİ ÇELİK" <ali@verus.com.tr>
Subject: more execution time
Date: Fri, 11 Mar 2005 13:54:56 +0200
Organization: Hub.Org Networking Services
Lines: 90
Message-ID: <d0s0uj$17l$2@news.hub.org>
To: pgsql-benchmarks@postgresql.org, pgsql-general@postgresql.org,
	pgsql-performance@postgresql.org, pgsql-sql@postgresql.org

why this query needs more time?  Its very slow

thx

//////////////////////////////////QUERY
select
      coalesce(personaldetails.masterid::numeric,personaldetails.id) +
(coalesce(personaldetails.id::numeric,0)/1000000) as sorting,
      floor(coalesce(personaldetails.masterid::numeric,personaldetails.id) +
(coalesce(personaldetails.id::numeric,0)/1000000)) as ppid,
      personaldetails.id as pid,
      personaldetails.masterid,
      coalesce(personaldetails.prefix,'') || '' ||
coalesce(personaldetails.firstname,' ') || ' ' ||
coalesce(personaldetails.lastname,'''') as fullname,
      personaldetails.regtypeid,
      personaldetails.regdate,
      personaldetails.regprice,
      coalesce(regtypes.regtype,' ') || ' ' ||
coalesce(regtypes.subregtype,' ') as regtypetitle,
      regtypes.regtype,
      regtypes.subregtype,
      regtypedates.title,
      balance('MASTER-REGISTRATION',personaldetails.id) as balance,
           coalesce(pd2.prefix,' ') || ' ' || coalesce(pd2.firstname,' ') ||
' ' || coalesce(pd2.lastname,' ') as accfullname,
      coalesce(rt2.regtype,'''') || ' ' || coalesce(rt2.subregtype,' ') as
accregtypetitle,
      pd2.id as accid,
      pd2.regtypeid as accregtypeid,
      pd2.regdate as accregdate,
      pd2.regprice as accregprice,
      rt2.regtype as accregtype,
      rt2.subregtype as accsubregtype,
      rd2.title as acctitle,
      balance('MASTER-REGISTRATION',pd2.id) as accbalance,
      case when coalesce(balance('REGISTRATION',personaldetails.id),0)<=0
then 1 else 0 end as balancestatus

from personaldetails
left outer join regtypes on regtypes.id=personaldetails.regtypeid
left outer join regtypedates on regtypes.dateid=regtypedates.id
left outer join personaldetails pd2 on personaldetails.id=pd2.masterid
left outer join regtypes rt2 on rt2.id=pd2.regtypeid
left outer join regtypedates rd2 on rt2.dateid=rd2.id
where personaldetails.masterid is null
///////////////////////////////////////////////////// RESULT STATISTICS
Total query runtime: 348892 ms.
Data retrieval runtime: 311 ms.
763 rows retrieved.
////////////////////////////////////////////////////  EXPLAIN QUERY

Hash Left Join  (cost=109.32..109.95 rows=5 width=434)
  Hash Cond: ("outer".dateid = "inner".id)
  ->  Merge Left Join  (cost=108.27..108.46 rows=5 width=409)
        Merge Cond: ("outer".regtypeid = "inner".id)
        ->  Sort  (cost=106.19..106.20 rows=5 width=347)
              Sort Key: pd2.regtypeid
              ->  Hash Left Join  (cost=90.11..106.13 rows=5 width=347)
                    Hash Cond: ("outer".id = "inner".masterid)
                    ->  Hash Left Join  (cost=45.49..45.71 rows=5 width=219)
                          Hash Cond: ("outer".dateid = "inner".id)
                          ->  Merge Left Join  (cost=44.44..44.63 rows=5
width=194)
                                Merge Cond: ("outer".regtypeid = "inner".id)
                                ->  Sort  (cost=42.36..42.37 rows=5
width=132)
                                      Sort Key: personaldetails.regtypeid
                                      ->  Seq Scan on personaldetails
(cost=0.00..42.30 rows=5 width=132)
                                            Filter: (masterid IS NULL)
                                ->  Sort  (cost=2.08..2.16 rows=31 width=66)
                                      Sort Key: regtypes.id
                                      ->  Seq Scan on regtypes
(cost=0.00..1.31 rows=31 width=66)
                          ->  Hash  (cost=1.04..1.04 rows=4 width=33)
                                ->  Seq Scan on regtypedates
(cost=0.00..1.04 rows=4 width=33)
                    ->  Hash  (cost=42.30..42.30 rows=930 width=132)
                          ->  Seq Scan on personaldetails pd2
(cost=0.00..42.30 rows=930 width=132)
        ->  Sort  (cost=2.08..2.16 rows=31 width=66)
              Sort Key: rt2.id
              ->  Seq Scan on regtypes rt2  (cost=0.00..1.31 rows=31
width=66)
  ->  Hash  (cost=1.04..1.04 rows=4 width=33)
        ->  Seq Scan on regtypedates rd2  (cost=0.00..1.04 rows=4 width=33)