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

more execution time

From: "AL ELK" <ali(at)verus(dot)com(dot)tr>
To: pgsql-benchmarks(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org,pgsql-performance(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: more execution time
Date: 2005-03-11 11:54:56
Message-ID: d0s0uj$17l$2@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-benchmarkspgsql-generalpgsql-performancepgsql-sql
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)




Responses

pgsql-performance by date

Next:From: Oleg BartunovDate: 2005-03-11 12:03:15
Subject: Re: Statistics not working??
Previous:From: Hugo FerreiraDate: 2005-03-11 11:53:18
Subject: Statistics not working??

pgsql-sql by date

Next:From: John DeSoiDate: 2005-03-11 12:46:42
Subject: Re: pl/pgsql problem with return types
Previous:From: Juris ZeltinsDate: 2005-03-11 10:54:52
Subject: pl/pgsql problem with return types

pgsql-benchmarks by date

Next:From: Richard HuxtonDate: 2005-03-11 13:05:28
Subject: Re: [GENERAL] more execution time
Previous:From: Randolf RichardsonDate: 2005-01-20 16:45:02
Subject: Re: PostgreSQL vs. Oracle vs. Microsoft

pgsql-general by date

Next:From: ntinosDate: 2005-03-11 12:17:04
Subject: Re: SRF, JDBC and result info
Previous:From: Sean DavisDate: 2005-03-11 11:50:00
Subject: Re: Statement cancel or transaction cancel?

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