more execution time

Richard Huxton · *nix forums Guru Joined: 01 Mar 2005 Posts: 522

ALÝ ÇELÝK wrote:

ALÝ ÇELÝK · *nix forums beginner Joined: 11 Mar 2005 Posts: 3

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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Google