pg.ddx.io pgsql-benchmarks@postgresql.org mailing list archive help / color / mirror / Atom feed From: ALÝ ÇELÝK <ali@verus.com.tr>
To: pgsql-benchmarks@postgresql.org
To: pgsql-general@postgresql.org
To: pgsql-performance@postgresql.org
To: pgsql-sql@postgresql.org
Subject: more execution time
Date: Fri, 11 Mar 2005 13:54:56 +0200
Message-ID: <d0s0uj$17l$2@news.hub.org> (raw )
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)
view thread (2+ messages) latest in thread
Message-ID: <d0s0uj$17l$2@news.hub.org>
Permalink: ../d0s0uj$17l$2@news.hub.org/
Also on: postgresql.org/message-id/d0s0uj$17l$2@news.hub.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-benchmarks@postgresql.org
Cc: ali@verus.com.tr, pgsql-general@postgresql.org, pgsql-performance@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: more execution time
In-Reply-To: <d0s0uj$17l$2@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox