Re: BUG #3667: Job scheduling with Greenplum fails
От | Roberts, Jon |
---|---|
Тема | Re: BUG #3667: Job scheduling with Greenplum fails |
Дата | |
Msg-id | 15362F202C62EA4590F5F3E5FA15021E05286688@nasappexc04.asurion.loc обсуждение исходный текст |
Ответ на | BUG #3667: Job scheduling with Greenplum fails ("Jon Roberts" <jon.roberts@asurion.com>) |
Список | pgsql-bugs |
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@sss.pgh.pa.us] Sent: Wednesday, October 10, 2007 2:57 PM To: Jon Roberts Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #3667: Job scheduling with Greenplum fails "Jon Roberts" <jon.roberts@asurion.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
В списке pgsql-bugs по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: BUG #3662: Seems that more than one run of a functions causes an error