Re: BUG #3667: Job scheduling with Greenplum fails

What if the new query has a significantly lower cost compared to the older
one?

The current query found in pgaJob.cpp:
SELECT *,
(SELECT jlgstatus FROM pgagent.pga_joblog jl WHERE jl.jlgjobid =
j.jobid ORDER BY jlgid DESC LIMIT 1) AS joblastresult
FROM pgagent.pga_job j
JOIN pgagent.pga_jobclass cl ON cl.jclid=jobjclid
LEFT OUTER JOIN pgagent.pga_jobagent ag ON ag.jagpid=jobagentid
-- + restriction +
ORDER BY jobname

"Sort (cost=5359.18..5360.33 rows=460 width=221)"
" Sort Key: j.jobname"
" -> Hash Join (cost=69.50..5338.84 rows=460 width=221)"
" Hash Cond: (j.jobjclid = cl.jclid)"
" -> Hash Left Join (cost=33.40..54.33 rows=460 width=185)"
" Hash Cond: (j.jobagentid = ag.jagpid)"
" -> Seq Scan on pga_job j (cost=0.00..14.60 rows=460
width=141)"
" -> Hash (cost=20.40..20.40 rows=1040 width=44)"
" -> Seq Scan on pga_jobagent ag (cost=0.00..20.40
rows=1040 width=44)"
" -> Hash (cost=21.60..21.60 rows=1160 width=36)"
" -> Seq Scan on pga_jobclass cl (cost=0.00..21.60 rows=1160
width=36)"
" SubPlan"
" -> Limit (cost=0.00..11.40 rows=1 width=9)"
" -> Index Scan Backward using pga_joblog_pkey on pga_joblog
jl (cost=0.00..68.38 rows=6 width=9)"
" Filter: (jlgjobid = $0)"

My revised query:

select j.*, cl.*, ag.*, sub3.jlgstatus
from pgagent.pga_job j join
pgagent.pga_jobclass cl on cl.jclid=jobjclid left outer join
pgagent.pga_jobagent ag on ag.jagpid=jobagentid
join (select j2.jlgstatus,
sub.jlgjobid
from pgagent.pga_joblog j2 join
(select jl.jlgjobid,
max(jl.jlgid) as max_jlgid
from pgagent.pga_joblog jl
group by jl.jlgjobid) sub
on sub.jlgjobid = j2.jlgjobid and
sub.max_jlgid = j2.jlgid) sub3
on sub3.jlgjobid = j.jobid
-- + restriction +
order by jobname

"Sort (cost=81.07..81.07 rows=1 width=226)"
" Sort Key: j.jobname"
" -> Nested Loop (cost=66.00..81.06 rows=1 width=226)"
" -> Nested Loop Left Join (cost=66.00..80.67 rows=1 width=190)"
" -> Nested Loop (cost=66.00..80.29 rows=1 width=146)"
" -> Hash Join (cost=66.00..72.01 rows=1 width=13)"
" Hash Cond: ((sub.jlgjobid = j2.jlgjobid) AND
(sub.max_jlgid = j2.jlgid))"
" -> HashAggregate (cost=27.25..29.75 rows=200
width=8)"
" -> Seq Scan on pga_joblog jl
(cost=0.00..21.50 rows=1150 width=8)"
" -> Hash (cost=21.50..21.50 rows=1150 width=13)"
" -> Seq Scan on pga_joblog j2
(cost=0.00..21.50 rows=1150 width=13)"
" -> Index Scan using pga_job_pkey on pga_job j
(cost=0.00..8.27 rows=1 width=141)"
" Index Cond: (sub.jlgjobid = j.jobid)"
" -> Index Scan using pga_jobagent_pkey on pga_jobagent ag
(cost=0.00..0.37 rows=1 width=44)"
" Index Cond: (ag.jagpid = j.jobagentid)"
" -> Index Scan using pga_jobclass_pkey on pga_jobclass cl
(cost=0.00..0.37 rows=1 width=36)"
" Index Cond: (cl.jclid = j.jobjclid)"

Surely the cost dropping from 5359 to 81 is worth the trouble.

Jon

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, October 10, 2007 2:57 PM
To: Jon Roberts
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #3667: Job scheduling with Greenplum fails

"Jon Roberts" <jon(dot)roberts(at)asurion(dot)com> writes:
> Greenplum doesn't support "correlated subqueries" which PGAdmin III uses
> when PgAgent is installed.

Surely this complaint should be directed to Greenplum. Correlated
subqueries are a required entry-level feature in the SQL92 standard,
and have been supported by PG for nigh ten years. It is by no means
unreasonable for PGAdmin to expect them to work.

regards, tom lane