Skip to content

Instantly share code, notes, and snippets.

@grahamc

grahamc/keeps-on-giving.sql Secret

Created January 21, 2017 03:35
Show Gist options
  • Save grahamc/cf32b1a4b7bb8ca37c28e77901265caa to your computer and use it in GitHub Desktop.
Save grahamc/cf32b1a4b7bb8ca37c28e77901265caa to your computer and use it in GitHub Desktop.
Download ZIP
Raw
keeps-on-giving.sql
SELECT
me.name, me.project, me.description, me.nixexprinput,
me.nixexprpath, me.errormsg, me.errortime, me.lastcheckedtime,
me.triggertime, me.enabled, me.enableemail, me.hidden,
me.emailoverride, me.keepnr, me.checkinterval, me.schedulingshares,
me.fetcherrormsg, me.forceeval,
(select count(*)
from Builds as a
where a.finished = 0
and me.project = a.project
and me.name = a.jobset
and a.isCurrent = 1
),
(select count(*)
from Builds as a
where a.finished = 1
and me.project = a.project
and me.name = a.jobset
and buildstatus <> 0
and a.isCurrent = 1
),
(select count(*)
from Builds as a
where a.finished = 1
and me.project = a.project
and me.name = a.jobset
and buildstatus = 0
and a.isCurrent = 1
),
(select count(*)
from Builds as a
where me.project = a.project
and me.name = a.jobset
and a.isCurrent = 1
)
FROM Jobsets me
WHERE ( me.project = $1 )
ORDER BY name
Raw
plan
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------
Sort (cost=9673558.21..9673558.43 rows=90 width=394) (actual time=14362.243..14362.247 rows=91 loops=1)
Sort Key: me.name
Sort Method: quicksort Memory: 54kB
-> Seq Scan on jobsets me (cost=0.00..9673555.28 rows=90 width=394) (actual time=357.243..14361.865 rows=91 loops=1)
Filter: (project = 'nixos'::text)
SubPlan 1
-> Aggregate (cost=4696.63..4696.64 rows=1 width=0) (actual time=53.939..53.939 rows=1 loops=91)
-> Bitmap Heap Scan on builds a (cost=2923.34..4695.48 rows=461 width=0) (actual time=53.364..53.670 rows=300
loops=91)
Recheck Cond: ((me.project = project) AND (me.name = jobset) AND (iscurrent = 1) AND (finished = 0))
Heap Blocks: exact=7247
-> BitmapAnd (cost=2923.34..2923.34 rows=461 width=0) (actual time=53.304..53.304 rows=0 loops=91)
-> Bitmap Index Scan on indexbuildsonjobsetiscurrent (cost=0.00..1195.60 rows=25214 width=0) (actu
al time=7.275..7.275 rows=21865 loops=91)
Index Cond: ((me.project = project) AND (me.name = jobset) AND (iscurrent = 1))
-> Bitmap Index Scan on indexbuildsonfinishedstoptime (cost=0.00..1727.26 rows=42510 width=0) (act
ual time=53.466..53.466 rows=241425 loops=77)
Index Cond: (finished = 0)
SubPlan 2
-> Aggregate (cost=42736.94..42736.95 rows=1 width=0) (actual time=41.233..41.234 rows=1 loops=91)
-> Index Scan using indexbuildsonjobsetiscurrent on builds a_1 (cost=0.43..42728.50 rows=3377 width=0) (actual time=0.070..41.092 rows=1217 loops=91)
Index Cond: ((me.project = project) AND (me.name = jobset) AND (iscurrent = 1))
Filter: ((buildstatus <> 0) AND (finished = 1))
Rows Removed by Filter: 20644
SubPlan 3
-> Aggregate (cost=42781.94..42781.95 rows=1 width=0) (actual time=41.885..41.886 rows=1 loops=91)
-> Index Scan using indexbuildsonjobsetiscurrent on builds a_2 (cost=0.43..42728.50 rows=21375 width=0) (actual time=0.053..39.506 rows=20344 loops=91)
Index Cond: ((me.project = project) AND (me.name = jobset) AND (iscurrent = 1))
Filter: ((finished = 1) AND (buildstatus = 0))
Rows Removed by Filter: 1517
SubPlan 4
-> Aggregate (cost=17268.34..17268.35 rows=1 width=0) (actual time=20.745..20.745 rows=1 loops=91)
-> Index Only Scan using indexbuildsonjobsetiscurrent on builds a_3 (cost=0.43..17205.31 rows=25214 width=0) (actual time=0.029..18.092 rows=21861 loops=91)
Index Cond: ((project = me.project) AND (jobset = me.name) AND (iscurrent = 1))
Heap Fetches: 702537
Planning time: 0.638 ms
Execution time: 14362.338 ms
(34 rows)
@grahamc
Copy link
Author

grahamc commented Jan 21, 2017 •
edited
Loading

Changing the query to:

SELECT
  me.name, me.project, me.description, me.nixexprinput,
  me.nixexprpath, me.errormsg, me.errortime, me.lastcheckedtime,
  me.triggertime, me.enabled, me.enableemail, me.hidden,
  me.emailoverride, me.keepnr, me.checkinterval, me.schedulingshares,
  me.fetcherrormsg, me.forceeval,
count(nullif(Builds.finished = 0, false)),
count(nullif(Builds.finished = 1 AND Builds.buildstatus > 0, false)),
count(nullif(Builds.finished = 1 AND Builds.buildstatus = 0, false)),
count(Builds.id)
FROM Jobsets me
LEFT JOIN Builds on (Builds.project = me.project AND Builds.jobset = me.name AND isCurrent = 1)
WHERE ( me.project = 'nixos' )
GROUP BY me.name, me.project
ORDER BY name;
- Sort (cost=9673558.21..9673558.43 rows=90 width=394) (actual time=14362.243..14362.247 rows=91 loops=1)
+ Sort  (cost=323080.44...323080.71 rows=106 width=395) (actual time=3902.657...3902.660 rows=107 loops=1)
   Sort Key: me.name
   Sort Method: quicksort  Memory: 59kB
   ->  HashAggregate  (cost=323075.82..323076.88 rows=106 width=395) (actual time=3902.521..3902.549 rows=107 loops=1)
         Group Key: me.name, me.project
         ->  Hash Right Join  (cost=6.92..239658.99 rows=2383338 width=395) (actual time=0.065..2307.448 rows=2381428 loops=1)
               Hash Cond: ((builds.project = me.project) AND (builds.jobset = me.name))
               ->  Seq Scan on builds  (cost=0.00..197943.65 rows=2383338 width=27) (actual time=0.003..1097.260 rows=2381412 loops=1)
                     Filter: ((project = 'nixos'::text) AND (iscurrent = 1))
                     Rows Removed by Filter: 413965
               ->  Hash  (cost=5.33..5.33 rows=106 width=383) (actual time=0.056..0.056 rows=107 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 32kB
                     ->  Seq Scan on jobsets me  (cost=0.00..5.33 rows=106 width=383) (actual time=0.008..0.037 rows=107 loops=1)
                           Filter: (project = 'nixos'::text)
 Planning time: 0.326 ms
 Execution time: 3902.725 ms

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment