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$ (view raw, whole thread or download thread mbox)
Lists: pgsql-benchmarkspgsql-generalpgsql-performancepgsql-sql
why this query needs more time?  Its very slow


      coalesce(personaldetails.masterid::numeric, +
(coalesce(,0)/1000000) as sorting,
      floor(coalesce(personaldetails.masterid::numeric, +
(coalesce(,0)/1000000)) as ppid, as pid,
      coalesce(personaldetails.prefix,'') || '' ||
coalesce(personaldetails.firstname,' ') || ' ' ||
coalesce(personaldetails.lastname,'''') as fullname,
      coalesce(regtypes.regtype,' ') || ' ' ||
coalesce(regtypes.subregtype,' ') as regtypetitle,
      balance('MASTER-REGISTRATION', as balance,
           coalesce(pd2.prefix,' ') || ' ' || coalesce(pd2.firstname,' ') ||
' ' || coalesce(pd2.lastname,' ') as accfullname,
      coalesce(rt2.regtype,'''') || ' ' || coalesce(rt2.subregtype,' ') as
accregtypetitle, 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', as accbalance,
      case when coalesce(balance('REGISTRATION',,0)<=0
then 1 else 0 end as balancestatus

from personaldetails
left outer join regtypes on
left outer join regtypedates on
left outer join personaldetails pd2 on
left outer join regtypes rt2 on
left outer join regtypedates rd2 on
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
                                Merge Cond: ("outer".regtypeid = "inner".id)
                                ->  Sort  (cost=42.36..42.37 rows=5
                                      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:
                                      ->  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:
              ->  Seq Scan on regtypes rt2  (cost=0.00..1.31 rows=31
  ->  Hash  (cost=1.04..1.04 rows=4 width=33)
        ->  Seq Scan on regtypedates rd2  (cost=0.00..1.04 rows=4 width=33)


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-2017 The PostgreSQL Global Development Group