Re: BUG #3667: Job scheduling with Greenplum fails

Like I said in the email, I think the best solution is to put the code in a
function.  However, after I inserted a total of 25 jobs, the difference is
more noticeable and my sql is better in terms of cost and total time.

vacuum analyze pgagent.pga_job;
vacuum analyze pgagent.pga_jobclass;
vacuum analyze pgagent.pga_jobagent;

Original SQL:
explain analyze
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=291.31..291.37 rows=25 width=127) (actual time=2.125..2.181
rows=25 loops=1)"
"  Sort Key: j.jobname"
"  ->  Hash Join  (cost=2.13..290.73 rows=25 width=127) (actual
time=0.204..1.823 rows=25 loops=1)"
"        Hash Cond: (j.jobjclid = cl.jclid)"
"        ->  Hash Left Join  (cost=1.02..4.38 rows=25 width=105) (actual
time=0.061..0.264 rows=25 loops=1)"
"              Hash Cond: (j.jobagentid = ag.jagpid)"
"              ->  Seq Scan on pga_job j  (cost=0.00..3.25 rows=25 width=63)
(actual time=0.009..0.074 rows=25 loops=1)"
"              ->  Hash  (cost=1.01..1.01 rows=1 width=42) (actual
time=0.021..0.021 rows=1 loops=1)"
"                    ->  Seq Scan on pga_jobagent ag  (cost=0.00..1.01
rows=1 width=42) (actual time=0.005..0.008 rows=1 loops=1)"
"        ->  Hash  (cost=1.05..1.05 rows=5 width=22) (actual
time=0.050..0.050 rows=5 loops=1)"
"              ->  Seq Scan on pga_jobclass cl  (cost=0.00..1.05 rows=5
width=22) (actual time=0.005..0.026 rows=5 loops=1)"
"        SubPlan"
"          ->  Limit  (cost=0.00..11.40 rows=1 width=9) (actual
time=0.045..0.045 rows=0 loops=25)"
"                ->  Index Scan Backward using pga_joblog_pkey on pga_joblog
jl  (cost=0.00..68.38 rows=6 width=9) (actual time=0.036..0.036 rows=0
loops=25)"
"                      Filter: (jlgjobid = $0)"
"Total runtime: 2.436 ms"


My SQL:
explain analyze
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=68.35..68.36 rows=1 width=132) (actual time=1.026..1.033 rows=2
loops=1)"
"  Sort Key: j.jobname"
"  ->  Nested Loop  (cost=36.69..68.34 rows=1 width=132) (actual
time=0.877..0.961 rows=2 loops=1)"
"        ->  Nested Loop Left Join  (cost=36.69..67.58 rows=1 width=110)
(actual time=0.838..0.882 rows=2 loops=1)"
"              ->  Hash Join  (cost=36.69..66.82 rows=1 width=68) (actual
time=0.810..0.830 rows=2 loops=1)"
"                    Hash Cond: ((j2.jlgjobid = j.jobid) AND (j2.jlgid =
sub.max_jlgid))"
"                    ->  Seq Scan on pga_joblog j2  (cost=0.00..21.50
rows=1150 width=13) (actual time=0.024..0.130 rows=44 loops=1)"
"                    ->  Hash  (cost=36.31..36.31 rows=25 width=71) (actual
time=0.542..0.542 rows=2 loops=1)"
"                          ->  Hash Join  (cost=30.81..36.31 rows=25
width=71) (actual time=0.506..0.523 rows=2 loops=1)"
"                                Hash Cond: (sub.jlgjobid = j.jobid)"
"                                ->  HashAggregate  (cost=27.25..29.75
rows=200 width=8) (actual time=0.270..0.275 rows=2 loops=1)"
"                                      ->  Seq Scan on pga_joblog jl
(cost=0.00..21.50 rows=1150 width=8) (actual time=0.013..0.116 rows=44
loops=1)"
"                                ->  Hash  (cost=3.25..3.25 rows=25
width=63) (actual time=0.206..0.206 rows=25 loops=1)"
"                                      ->  Seq Scan on pga_job j
(cost=0.00..3.25 rows=25 width=63) (actual time=0.011..0.082 rows=25
loops=1)"
"              ->  Index Scan using pga_jobagent_pkey on pga_jobagent ag
(cost=0.00..0.75 rows=1 width=42) (actual time=0.006..0.006 rows=0 loops=2)"
"                    Index Cond: (ag.jagpid = j.jobagentid)"
"        ->  Index Scan using pga_jobclass_pkey on pga_jobclass cl
(cost=0.00..0.75 rows=1 width=22) (actual time=0.016..0.020 rows=1 loops=2)"
"              Index Cond: (cl.jclid = j.jobjclid)"
"Total runtime: 1.406 ms"


I think this trend will continue as more and more jobs are inserted.


BOOYA!

Jon
-----Original Message-----
From: Andrew Sullivan [mailto:ajs@crankycanuck.ca]
Sent: Thursday, October 11, 2007 10:14 AM
To: Roberts, Jon
Cc: 'Tom Lane'; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3667: Job scheduling with Greenplum fails

On Thu, Oct 11, 2007 at 07:31:44AM -0500, Roberts, Jon wrote:

> The cost is significantly lower but the total runtime is higher.

Um, so you want developers to change the thing so that it performs
more slowly, but has a prettier estimate of how much work it's going
to do?  That seems like a poor optimisation to me.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
        --George Orwell