Skip to content

Instantly share code, notes, and snippets.

@natefoo

natefoo/00_before_analyze_jobs.sql

Last active June 15, 2018 17:28
Show Gist options
  • Save natefoo/77b500d374473ee9963d192b04a0e27e to your computer and use it in GitHub Desktop.
Save natefoo/77b500d374473ee9963d192b04a0e27e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
00_before_analyze_jobs.sql
galaxy_main=> EXPLAIN ANALYZE SELECT job.id AS job_id,
job.create_time AS job_create_time,
job.update_time AS job_update_time,
job.history_id AS job_history_id,
job.library_folder_id AS job_library_folder_id,
job.tool_id AS job_tool_id,
job.tool_version AS job_tool_version,
job.state AS job_state,
job.info AS job_info,
job.command_line AS job_command_line,
job.param_filename AS job_param_filename,
job.runner_name AS job_runner_name_1,
job.stdout AS job_stdout,
job.stderr AS job_stderr,
job.exit_code AS job_exit_code,
job.traceback AS job_traceback,
job.session_id AS job_session_id,
job.user_id AS job_user_id,
job.job_runner_name AS job_job_runner_name,
job.job_runner_external_id AS job_job_runner_external_id,
job.destination_id AS job_destination_id,
job.destination_params AS job_destination_params,
job.object_store_id AS job_object_store_id,
job.imported AS job_imported,
job.params AS job_params,
job.handler AS job_handler
FROM job
LEFT OUTER JOIN galaxy_user ON galaxy_user.id = job.user_id
WHERE job.state = 'new'
AND (job.user_id IS NULL
OR galaxy_user.active = TRUE)
AND job.handler = 'main_w3_handler0'
AND job.id NOT IN
(SELECT job.id
FROM job
JOIN job_to_input_dataset ON job.id = job_to_input_dataset.job_id
JOIN history_dataset_association ON history_dataset_association.id = job_to_input_dataset.dataset_id
JOIN dataset ON dataset.id = history_dataset_association.dataset_id
WHERE job.state = 'new'
AND (history_dataset_association.state = 'failed_metadata'
OR history_dataset_association.deleted = TRUE
OR dataset.state != 'ok'
OR dataset.deleted = TRUE))
AND job.id NOT IN
(SELECT job.id
FROM job
JOIN job_to_input_library_dataset ON job.id = job_to_input_library_dataset.job_id
JOIN library_dataset_dataset_association ON library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id
JOIN dataset ON dataset.id = library_dataset_dataset_association.dataset_id
WHERE job.state = 'new'
AND (library_dataset_dataset_association.state IS NOT NULL
OR library_dataset_dataset_association.deleted = TRUE
OR dataset.state != 'ok'
OR dataset.deleted = TRUE))
ORDER BY job.id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=3301746.63..3301746.98 rows=139 width=2514) (actual time=28882.367..28882.369 rows=17 loops=1)
Sort Key: public.job.id
Sort Method: quicksort Memory: 29kB
-> Nested Loop Left Join (cost=3298271.96..3301741.69 rows=139 width=2514) (actual time=28881.877..28882.304 rows=17 loops=1)
Filter: ((public.job.user_id IS NULL) OR galaxy_user.active)
Rows Removed by Filter: 33
-> Bitmap Heap Scan on job (cost=3298271.96..3300550.22 rows=144 width=2514) (actual time=28876.806..28878.005 rows=50 loops=1)
Recheck Cond: (((state)::text = 'new'::text) AND ((handler)::text = 'main_w3_handler0'::text))
Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
Rows Removed by Filter: 315
-> BitmapAnd (cost=6458.84..6458.84 rows=576 width=0) (actual time=117.406..117.406 rows=0 loops=1)
-> Bitmap Index Scan on ix_job_state (cost=0.00..2163.93 rows=116016 width=0) (actual time=1.047..1.047 rows=3618 loops=1)
Index Cond: ((state)::text = 'new'::text)
-> Bitmap Index Scan on ix_job_handler (cost=0.00..4294.59 rows=69703 width=0) (actual time=115.394..115.394 rows=316741 loops=1)
Index Cond: ((handler)::text = 'main_w3_handler0'::text)
SubPlan 1
-> Hash Join (cost=959370.87..3291431.57 rows=107680 width=4) (actual time=20036.978..28756.197 rows=2134 loops=1)
Hash Cond: (history_dataset_association.dataset_id = public.dataset.id)
Join Filter: (((history_dataset_association.state)::text = 'failed_metadata'::text) OR history_dataset_association.deleted OR ((public.dataset.state)::text <> 'ok'::text) OR public.dataset.deleted)
Rows Removed by Join Filter: 1181
-> Nested Loop (cost=165427.34..2424672.11 rows=127224 width=25) (actual time=2153.220..6845.124 rows=3315 loops=1)
-> Hash Join (cost=165427.34..636646.94 rows=133335 width=8) (actual time=2153.185..6766.550 rows=3924 loops=1)
Hash Cond: (job_to_input_dataset.job_id = public.job.id)
-> Seq Scan on job_to_input_dataset (cost=0.00..288432.88 rows=16129188 width=8) (actual time=0.013..4427.591 rows=16397248 loops=1)
-> Hash (cost=163977.14..163977.14 rows=116016 width=4) (actual time=7.897..7.897 rows=2593 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 92kB
-> Index Scan using ix_job_state on job (cost=0.00..163977.14 rows=116016 width=4) (actual time=0.048..7.183 rows=2593 loops=1)
Index Cond: ((state)::text = 'new'::text)
-> Index Scan using history_dataset_association_pkey on history_dataset_association (cost=0.00..13.40 rows=1 width=25) (actual time=0.018..0.019 rows=1 loops=3924)
Index Cond: (id = job_to_input_dataset.dataset_id)
-> Hash (cost=516131.68..516131.68 rows=16933268 width=8) (actual time=17879.076..17879.076 rows=17450538 loops=1)
Buckets: 131072 Batches: 32 Memory Usage: 21764kB
-> Seq Scan on dataset (cost=0.00..516131.68 rows=16933268 width=8) (actual time=0.028..10168.414 rows=17450538 loops=1)
SubPlan 2
-> Nested Loop (cost=0.00..112.35 rows=1 width=4) (actual time=1.180..1.180 rows=0 loops=1)
-> Nested Loop (cost=0.00..97.87 rows=1 width=25) (actual time=1.180..1.180 rows=0 loops=1)
-> Nested Loop (cost=0.00..89.59 rows=1 width=8) (actual time=1.179..1.179 rows=0 loops=1)
-> Seq Scan on job_to_input_library_dataset (cost=0.00..1.09 rows=9 width=8) (actual time=0.007..0.009 rows=9 loops=1)
-> Index Scan using job_pkey on job (cost=0.00..9.82 rows=1 width=4) (actual time=0.128..0.128 rows=0 loops=9)
Index Cond: (id = job_to_input_library_dataset.job_id)
Filter: ((state)::text = 'new'::text)
Rows Removed by Filter: 1
-> Index Scan using library_dataset_dataset_association_pkey on library_dataset_dataset_association (cost=0.00..8.27 rows=1 width=25) (never executed)
Index Cond: (id = job_to_input_library_dataset.ldda_id)
-> Index Scan using dataset_pkey on dataset (cost=0.00..14.46 rows=1 width=8) (never executed)
Index Cond: (id = library_dataset_dataset_association.dataset_id)
Filter: ((library_dataset_dataset_association.state IS NOT NULL) OR library_dataset_dataset_association.deleted OR ((state)::text <> 'ok'::text) OR deleted)
-> Index Scan using galaxy_user_pkey on galaxy_user (cost=0.00..8.26 rows=1 width=5) (actual time=0.079..0.081 rows=1 loops=50)
Index Cond: (id = public.job.user_id)
Total runtime: 28882.684 ms
(50 rows)
Raw
after_analyze_first_query.sql
galaxy_main=> explain analyze SELECT job.id AS job_id, job.create_time AS job_create_time, job.update_time AS job_update_time, job.history_id AS job_history_id, job.library_folder_id AS job_library_folder_id, job.tool_id AS job_tool_id, job.tool_version AS job_tool_version, job.state AS job_state, job.info AS job_info, job.command_line AS job_command_line, job.param_filename AS job_param_filename, job.runner_name AS job_runner_name_1, job.stdout AS job_stdout, job.stderr AS job_stderr, job.exit_code AS job_exit_code, job.traceback AS job_traceback, job.session_id AS job_session_id, job.user_id AS job_user_id, job.job_runner_name AS job_job_runner_name, job.job_runner_external_id AS job_job_runner_external_id, job.destination_id AS job_destination_id, job.destination_params AS job_destination_params, job.object_store_id AS job_object_store_id, job.imported AS job_imported, job.params AS job_params, job.handler AS job_handler
galaxy_main-> FROM job LEFT OUTER JOIN galaxy_user ON galaxy_user.id = job.user_id
galaxy_main-> WHERE job.state = 'new' AND (job.user_id IS NULL OR galaxy_user.active = true) AND job.handler = 'main_w3_handler0' AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_dataset ON job.id = job_to_input_dataset.job_id JOIN history_dataset_association ON history_dataset_association.id = job_to_input_dataset.dataset_id JOIN dataset ON dataset.id = history_dataset_association.dataset_id WHERE job.state = 'new' AND (history_dataset_association.state = 'failed_metadata' OR history_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true)) AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_library_dataset ON job.id = job_to_input_library_dataset.job_id JOIN library_dataset_dataset_association ON library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id JOIN dataset ON dataset.id = library_dataset_dataset_association.dataset_id WHERE job.state = 'new' AND (library_dataset_dataset_association.state IS NOT NULL OR library_dataset_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true))
galaxy_main-> ORDER BY job.id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1459336.74..1459336.81 rows=27 width=2100) (actual time=29538.583..29538.588 rows=46 loops=1)
Sort Key: public.job.id
Sort Method: quicksort Memory: 33kB
-> Nested Loop Left Join (cost=1458663.78..1459336.10 rows=27 width=2100) (actual time=29421.348..29538.451 rows=46 loops=1)
Filter: ((public.job.user_id IS NULL) OR galaxy_user.active)
Rows Removed by Filter: 32
-> Bitmap Heap Scan on job (cost=1458663.78..1459102.87 rows=28 width=2100) (actual time=29420.825..29424.292 rows=78 loops=1)
Recheck Cond: (((state)::text = 'new'::text) AND ((handler)::text = 'main_w3_handler0'::text))
Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
Rows Removed by Filter: 308
-> BitmapAnd (cost=3787.17..3787.17 rows=110 width=0) (actual time=23893.261..23893.261 rows=0 loops=1)
-> Bitmap Index Scan on ix_job_state (cost=0.00..563.60 rows=29572 width=0) (actual time=1.087..1.087 rows=2284 loops=1)
Index Cond: ((state)::text = 'new'::text)
-> Bitmap Index Scan on ix_job_handler (cost=0.00..3223.31 rows=51131 width=0) (actual time=23891.361..23891.361 rows=83737 loops=1)
Index Cond: ((handler)::text = 'main_w3_handler0'::text)
SubPlan 1
-> Nested Loop (cost=0.00..1454693.53 rows=28287 width=4) (actual time=13.887..5523.746 rows=2154 loops=1)
-> Nested Loop (cost=0.00..969331.52 rows=33442 width=25) (actual time=0.094..4989.780 rows=3408 loops=1)
-> Nested Loop (cost=0.00..497619.93 rows=35171 width=8) (actual time=0.074..3438.750 rows=4020 loops=1)
-> Index Scan using ix_job_state on job (cost=0.00..40876.99 rows=29572 width=4) (actual time=0.054..93.571 rows=2283 loops=1)
Index Cond: ((state)::text = 'new'::text)
-> Index Scan using ix_job_to_input_dataset_job_id on job_to_input_dataset (cost=0.00..15.26 rows=19 width=8) (actual time=1.450..1.463 rows=2 loops=2283)
Index Cond: (job_id = public.job.id)
-> Index Scan using history_dataset_association_pkey on history_dataset_association (cost=0.00..13.40 rows=1 width=25) (actual time=0.378..0.384 rows=1 loops=4020)
Index Cond: (id = job_to_input_dataset.dataset_id)
-> Index Scan using dataset_pkey on dataset (cost=0.00..14.50 rows=1 width=8) (actual time=0.155..0.155 rows=1 loops=3408)
Index Cond: (id = history_dataset_association.dataset_id)
Filter: (((history_dataset_association.state)::text = 'failed_metadata'::text) OR history_dataset_association.deleted OR ((state)::text <> 'ok'::text) OR deleted)
Rows Removed by Filter: 0
SubPlan 2
-> Nested Loop (cost=0.00..112.36 rows=1 width=4) (actual time=0.388..0.388 rows=0 loops=1)
-> Nested Loop (cost=0.00..97.88 rows=1 width=25) (actual time=0.388..0.388 rows=0 loops=1)
-> Nested Loop (cost=0.00..89.60 rows=1 width=8) (actual time=0.387..0.387 rows=0 loops=1)
-> Seq Scan on job_to_input_library_dataset (cost=0.00..1.09 rows=9 width=8) (actual time=0.005..0.010 rows=9 loops=1)
-> Index Scan using job_pkey on job (cost=0.00..9.82 rows=1 width=4) (actual time=0.041..0.041 rows=0 loops=9)
Index Cond: (id = job_to_input_library_dataset.job_id)
Filter: ((state)::text = 'new'::text)
Rows Removed by Filter: 1
-> Index Scan using library_dataset_dataset_association_pkey on library_dataset_dataset_association (cost=0.00..8.27 rows=1 width=25) (never executed)
Index Cond: (id = job_to_input_library_dataset.ldda_id)
-> Index Scan using dataset_pkey on dataset (cost=0.00..14.47 rows=1 width=8) (never executed)
Index Cond: (id = library_dataset_dataset_association.dataset_id)
Filter: ((library_dataset_dataset_association.state IS NOT NULL) OR library_dataset_dataset_association.deleted OR ((state)::text <> 'ok'::text) OR deleted)
-> Index Scan using galaxy_user_pkey on galaxy_user (cost=0.00..8.32 rows=1 width=5) (actual time=1.150..1.460 rows=1 loops=78)
Index Cond: (id = public.job.user_id)
Total runtime: 29538.964 ms
(46 rows)
Raw
after_analyze_jobs.sql
galaxy_main=> explain analyze SELECT job.id AS job_id, job.create_time AS job_create_time, job.update_time AS job_update_time, job.history_id AS job_history_id, job.library_folder_id AS job_library_folder_id, job.tool_id AS job_tool_id, job.tool_version AS job_tool_version, job.state AS job_state, job.info AS job_info, job.command_line AS job_command_line, job.param_filename AS job_param_filename, job.runner_name AS job_runner_name_1, job.stdout AS job_stdout, job.stderr AS job_stderr, job.exit_code AS job_exit_code, job.traceback AS job_traceback, job.session_id AS job_session_id, job.user_id AS job_user_id, job.job_runner_name AS job_job_runner_name, job.job_runner_external_id AS job_job_runner_external_id, job.destination_id AS job_destination_id, job.destination_params AS job_destination_params, job.object_store_id AS job_object_store_id, job.imported AS job_imported, job.params AS job_params, job.handler AS job_handler
FROM job LEFT OUTER JOIN galaxy_user ON galaxy_user.id = job.user_id
WHERE job.state = 'new' AND (job.user_id IS NULL OR galaxy_user.active = true) AND job.handler = 'main_w3_handler0' AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_dataset ON job.id = job_to_input_dataset.job_id JOIN history_dataset_association ON history_dataset_association.id = job_to_input_dataset.dataset_id JOIN dataset ON dataset.id = history_dataset_association.dataset_id WHERE job.state = 'new' AND (history_dataset_association.state = 'failed_metadata' OR history_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true)) AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_library_dataset ON job.id = job_to_input_library_dataset.job_id JOIN library_dataset_dataset_association ON library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id JOIN dataset ON dataset.id = library_dataset_dataset_association.dataset_id WHERE job.state = 'new' AND (library_dataset_dataset_association.state IS NOT NULL OR library_dataset_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true))
ORDER BY job.id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1459336.74..1459336.81 rows=27 width=2100) (actual time=136.537..136.545 rows=48 loops=1)
Sort Key: public.job.id
Sort Method: quicksort Memory: 34kB
-> Nested Loop Left Join (cost=1458663.78..1459336.10 rows=27 width=2100) (actual time=134.657..136.451 rows=48 loops=1)
Filter: ((public.job.user_id IS NULL) OR galaxy_user.active)
Rows Removed by Filter: 32
-> Bitmap Heap Scan on job (cost=1458663.78..1459102.87 rows=28 width=2100) (actual time=134.637..135.596 rows=80 loops=1)
Recheck Cond: (((state)::text = 'new'::text) AND ((handler)::text = 'main_w3_handler0'::text))
Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
Rows Removed by Filter: 308
-> BitmapAnd (cost=3787.17..3787.17 rows=110 width=0) (actual time=43.750..43.750 rows=0 loops=1)
-> Bitmap Index Scan on ix_job_state (cost=0.00..563.60 rows=29572 width=0) (actual time=1.077..1.077 rows=2287 loops=1)
Index Cond: ((state)::text = 'new'::text)
-> Bitmap Index Scan on ix_job_handler (cost=0.00..3223.31 rows=51131 width=0) (actual time=42.179..42.179 rows=83741 loops=1)
Index Cond: ((handler)::text = 'main_w3_handler0'::text)
SubPlan 1
-> Nested Loop (cost=0.00..1454693.53 rows=28287 width=4) (actual time=1.390..88.392 rows=2154 loops=1)
-> Nested Loop (cost=0.00..969331.52 rows=33442 width=25) (actual time=0.246..55.644 rows=3407 loops=1)
-> Nested Loop (cost=0.00..497619.93 rows=35171 width=8) (actual time=0.227..24.821 rows=4019 loops=1)
-> Index Scan using ix_job_state on job (cost=0.00..40876.99 rows=29572 width=4) (actual time=0.048..4.761 rows=2285 loops=1)
Index Cond: ((state)::text = 'new'::text)
-> Index Scan using ix_job_to_input_dataset_job_id on job_to_input_dataset (cost=0.00..15.26 rows=19 width=8) (actual time=0.006..0.007 rows=2 loops=2285)
Index Cond: (job_id = public.job.id)
-> Index Scan using history_dataset_association_pkey on history_dataset_association (cost=0.00..13.40 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=4019)
Index Cond: (id = job_to_input_dataset.dataset_id)
-> Index Scan using dataset_pkey on dataset (cost=0.00..14.50 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=3407)
Index Cond: (id = history_dataset_association.dataset_id)
Filter: (((history_dataset_association.state)::text = 'failed_metadata'::text) OR history_dataset_association.deleted OR ((state)::text <> 'ok'::text) OR deleted)
Rows Removed by Filter: 0
SubPlan 2
-> Nested Loop (cost=0.00..112.36 rows=1 width=4) (actual time=0.094..0.094 rows=0 loops=1)
-> Nested Loop (cost=0.00..97.88 rows=1 width=25) (actual time=0.094..0.094 rows=0 loops=1)
-> Nested Loop (cost=0.00..89.60 rows=1 width=8) (actual time=0.093..0.093 rows=0 loops=1)
-> Seq Scan on job_to_input_library_dataset (cost=0.00..1.09 rows=9 width=8) (actual time=0.005..0.007 rows=9 loops=1)
-> Index Scan using job_pkey on job (cost=0.00..9.82 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=9)
Index Cond: (id = job_to_input_library_dataset.job_id)
Filter: ((state)::text = 'new'::text)
Rows Removed by Filter: 1
-> Index Scan using library_dataset_dataset_association_pkey on library_dataset_dataset_association (cost=0.00..8.27 rows=1 width=25) (never executed)
Index Cond: (id = job_to_input_library_dataset.ldda_id)
-> Index Scan using dataset_pkey on dataset (cost=0.00..14.47 rows=1 width=8) (never executed)
Index Cond: (id = library_dataset_dataset_association.dataset_id)
Filter: ((library_dataset_dataset_association.state IS NOT NULL) OR library_dataset_dataset_association.deleted OR ((state)::text <> 'ok'::text) OR deleted)
-> Index Scan using galaxy_user_pkey on galaxy_user (cost=0.00..8.32 rows=1 width=5) (actual time=0.007..0.008 rows=1 loops=80)
Index Cond: (id = public.job.user_id)
Total runtime: 136.862 ms
Raw
explain_analyze_verbose_buffers_jobs.sql
galaxy_main=> explain (analyze, verbose, buffers) SELECT job.id AS job_id, job.create_time AS job_create_time, job.update_time AS job_update_time, job.history_id AS job_history_id, job.library_folder_id AS job_library_folder_id, job.tool_id AS job_tool_id, job.tool_version AS job_tool_version, job.state AS job_state, job.info AS job_info, job.command_line AS job_command_line, job.param_filename AS job_param_filename, job.runner_name AS job_runner_name_1, job.stdout AS job_stdout, job.stderr AS job_stderr, job.exit_code AS job_exit_code, job.traceback AS job_traceback, job.session_id AS job_session_id, job.user_id AS job_user_id, job.job_runner_name AS job_job_runner_name, job.job_runner_external_id AS job_job_runner_external_id, job.destination_id AS job_destination_id, job.destination_params AS job_destination_params, job.object_store_id AS job_object_store_id, job.imported AS job_imported, job.params AS job_params, job.handler AS job_handler
FROM job LEFT OUTER JOIN galaxy_user ON galaxy_user.id = job.user_id
WHERE job.state = 'new' AND (job.user_id IS NULL OR galaxy_user.active = true) AND job.handler = 'main_w3_handler0' AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_dataset ON job.id = job_to_input_dataset.job_id JOIN history_dataset_association ON history_dataset_association.id = job_to_input_dataset.dataset_id JOIN dataset ON dataset.id = history_dataset_association.dataset_id WHERE job.state = 'new' AND (history_dataset_association.state = 'failed_metadata' OR history_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true)) AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_library_dataset ON job.id = job_to_input_library_dataset.job_id JOIN library_dataset_dataset_association ON library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id JOIN dataset ON dataset.id = library_dataset_dataset_association.dataset_id WHERE job.state = 'new' AND (library_dataset_dataset_association.state IS NOT NULL OR library_dataset_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true))
ORDER BY job.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1459336.73..1459336.80 rows=27 width=2100) (actual time=44194.755..44194.766 rows=51 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.library_folder_id, public.job.tool_id, public.job.tool_version, public.job.state, public.job.info, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.exit_code, public.job.traceback, public.job.session_id, public.job.user_id, public.job.job_runner_name, public.job.job_runner_external_id, public.job.destination_id, public.job.destination_params, public.job.object_store_id, public.job.imported, public.job.params, public.job.handler
Sort Key: public.job.id
Sort Method: quicksort Memory: 34kB
Buffers: shared hit=35698 read=8599 dirtied=13
-> Nested Loop Left Join (cost=1458663.77..1459336.09 rows=27 width=2100) (actual time=44022.966..44194.633 rows=51 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.library_folder_id, public.job.tool_id, public.job.tool_version, public.job.state, public.job.info, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.exit_code, public.job.traceback, public.job.session_id, public.job.user_id, public.job.job_runner_name, public.job.job_runner_external_id, public.job.destination_id, public.job.destination_params, public.job.object_store_id, public.job.imported, public.job.params, public.job.handler
Filter: ((public.job.user_id IS NULL) OR galaxy_user.active)
Rows Removed by Filter: 32
Buffers: shared hit=35698 read=8599 dirtied=13
-> Bitmap Heap Scan on public.job (cost=1458663.77..1459102.86 rows=28 width=2100) (actual time=44022.943..44024.219 rows=83 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Recheck Cond: (((public.job.state)::text = 'new'::text) AND ((public.job.handler)::text = 'main_w3_handler0'::text))
Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
Rows Removed by Filter: 307
Buffers: shared hit=35387 read=8567 dirtied=12
-> BitmapAnd (cost=3787.17..3787.17 rows=110 width=0) (actual time=11319.799..11319.799 rows=0 loops=1)
Buffers: shared hit=8 read=1973
-> Bitmap Index Scan on ix_job_state (cost=0.00..563.60 rows=29572 width=0) (actual time=611.976..611.976 rows=2311 loops=1)
Index Cond: ((public.job.state)::text = 'new'::text)
Buffers: shared hit=3 read=45
-> Bitmap Index Scan on ix_job_handler (cost=0.00..3223.31 rows=51131 width=0) (actual time=10707.090..10707.090 rows=83746 loops=1)
Index Cond: ((public.job.handler)::text = 'main_w3_handler0'::text)
Buffers: shared hit=5 read=1928
SubPlan 1
-> Nested Loop (cost=0.00..1454693.52 rows=28287 width=4) (actual time=270.045..32488.624 rows=2153 loops=1)
Output: public.job.id
Buffers: shared hit=35028 read=6579 dirtied=12
-> Nested Loop (cost=0.00..969331.52 rows=33442 width=25) (actual time=0.117..28924.246 rows=3420 loops=1)
Output: public.job.id, history_dataset_association.dataset_id, history_dataset_association.state, history_dataset_association.deleted
Buffers: shared hit=19540 read=4938 dirtied=12
-> Nested Loop (cost=0.00..497619.93 rows=35171 width=8) (actual time=0.084..19221.023 rows=4031 loops=1)
Output: public.job.id, job_to_input_dataset.dataset_id
Buffers: shared hit=7911 read=2854 dirtied=12
-> Index Scan using ix_job_state on public.job (cost=0.00..40876.99 rows=29572 width=4) (actual time=0.063..10530.505 rows=2302 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Index Cond: ((public.job.state)::text = 'new'::text)
Buffers: shared hit=318 read=1432 dirtied=5
-> Index Scan using ix_job_to_input_dataset_job_id on public.job_to_input_dataset (cost=0.00..15.26 rows=19 width=8) (actual time=3.678..3.770 rows=2 loops=2302)
Output: job_to_input_dataset.id, job_to_input_dataset.job_id, job_to_input_dataset.dataset_id, job_to_input_dataset.name
Index Cond: (job_to_input_dataset.job_id = public.job.id)
Buffers: shared hit=7593 read=1422 dirtied=7
-> Index Scan using history_dataset_association_pkey on public.history_dataset_association (cost=0.00..13.40 rows=1 width=25) (actual time=2.391..2.404 rows=1 loops=4031)
Output: history_dataset_association.id, history_dataset_association.history_id, history_dataset_association.dataset_id, history_dataset_association.create_time, history_dataset_association.update_time, history_dataset_association.copied_from_history_dataset_association_id, history_dataset_association.hid, history_dataset_association.name, history_dataset_association.info, history_dataset_association.blurb, history_dataset_association.peek, history_dataset_association.extension, history_dataset_association.metadata, history_dataset_association.parent_id, history_dataset_association.designation, history_dataset_association.deleted, history_dataset_association.visible, history_dataset_association.copied_from_library_dataset_dataset_association_id, history_dataset_association.state, history_dataset_association.purged, history_dataset_association.tool_version, history_dataset_association.extended_metadata_id, history_dataset_association.hidden_beneath_collection_instance_id
Index Cond: (history_dataset_association.id = job_to_input_dataset.dataset_id)
Buffers: shared hit=11629 read=2084
-> Index Scan using dataset_pkey on public.dataset (cost=0.00..14.50 rows=1 width=8) (actual time=1.032..1.040 rows=1 loops=3420)
Output: public.dataset.id, public.dataset.create_time, public.dataset.update_time, public.dataset.state, public.dataset.deleted, public.dataset.purged, public.dataset.file_size, public.dataset.purgable, public.dataset.external_filename, public.dataset._extra_files_path, public.dataset.total_size, public.dataset.object_store_id, public.dataset.uuid
Index Cond: (public.dataset.id = history_dataset_association.dataset_id)
Filter: (((history_dataset_association.state)::text = 'failed_metadata'::text) OR history_dataset_association.deleted OR ((public.dataset.state)::text <> 'ok'::text) OR public.dataset.deleted)
Rows Removed by Filter: 0
Buffers: shared hit=15488 read=1641
SubPlan 2
-> Nested Loop (cost=0.00..112.36 rows=1 width=4) (actual time=193.331..193.331 rows=0 loops=1)
Output: public.job.id
Buffers: shared hit=23 read=14
-> Nested Loop (cost=0.00..97.88 rows=1 width=25) (actual time=193.331..193.331 rows=0 loops=1)
Output: public.job.id, library_dataset_dataset_association.dataset_id, library_dataset_dataset_association.state, library_dataset_dataset_association.deleted
Buffers: shared hit=23 read=14
-> Nested Loop (cost=0.00..89.60 rows=1 width=8) (actual time=193.330..193.330 rows=0 loops=1)
Output: public.job.id, job_to_input_library_dataset.ldda_id
Buffers: shared hit=23 read=14
-> Seq Scan on public.job_to_input_library_dataset (cost=0.00..1.09 rows=9 width=8) (actual time=0.004..0.021 rows=9 loops=1)
Output: job_to_input_library_dataset.id, job_to_input_library_dataset.job_id, job_to_input_library_dataset.ldda_id, job_to_input_library_dataset.name
Buffers: shared hit=1
-> Index Scan using job_pkey on public.job (cost=0.00..9.82 rows=1 width=4) (actual time=21.474..21.474 rows=0 loops=9)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Index Cond: (public.job.id = job_to_input_library_dataset.job_id)
Filter: ((public.job.state)::text = 'new'::text)
Rows Removed by Filter: 1
Buffers: shared hit=22 read=14
-> Index Scan using library_dataset_dataset_association_pkey on public.library_dataset_dataset_association (cost=0.00..8.27 rows=1 width=25) (never executed)
Output: library_dataset_dataset_association.id, library_dataset_dataset_association.library_dataset_id, library_dataset_dataset_association.dataset_id, library_dataset_dataset_association.create_time, library_dataset_dataset_association.update_time, library_dataset_dataset_association.copied_from_history_dataset_association_id, library_dataset_dataset_association.copied_from_library_dataset_dataset_association_id, library_dataset_dataset_association.name, library_dataset_dataset_association.info, library_dataset_dataset_association.blurb, library_dataset_dataset_association.peek, library_dataset_dataset_association.extension, library_dataset_dataset_association.metadata, library_dataset_dataset_association.parent_id, library_dataset_dataset_association.designation, library_dataset_dataset_association.deleted, library_dataset_dataset_association.visible, library_dataset_dataset_association.user_id, library_dataset_dataset_association.message, library_dataset_dataset_association.state, library_dataset_dataset_association.tool_version, library_dataset_dataset_association.extended_metadata_id
Index Cond: (library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id)
-> Index Scan using dataset_pkey on public.dataset (cost=0.00..14.47 rows=1 width=8) (never executed)
Output: public.dataset.id, public.dataset.create_time, public.dataset.update_time, public.dataset.state, public.dataset.deleted, public.dataset.purged, public.dataset.file_size, public.dataset.purgable, public.dataset.external_filename, public.dataset._extra_files_path, public.dataset.total_size, public.dataset.object_store_id, public.dataset.uuid
Index Cond: (public.dataset.id = library_dataset_dataset_association.dataset_id)
Filter: ((library_dataset_dataset_association.state IS NOT NULL) OR library_dataset_dataset_association.deleted OR ((public.dataset.state)::text <> 'ok'::text) OR public.dataset.deleted)
-> Index Scan using galaxy_user_pkey on public.galaxy_user (cost=0.00..8.32 rows=1 width=5) (actual time=1.853..2.050 rows=1 loops=83)
Output: galaxy_user.id, galaxy_user.create_time, galaxy_user.update_time, galaxy_user.email, galaxy_user.password, galaxy_user.external, galaxy_user.deleted, galaxy_user.purged, galaxy_user.username, galaxy_user.form_values_id, galaxy_user.disk_usage, galaxy_user.active, galaxy_user.activation_token, galaxy_user.last_password_change
Index Cond: (galaxy_user.id = public.job.user_id)
Buffers: shared hit=311 read=32 dirtied=1
Total runtime: 44195.405 ms
(83 rows)
Raw
explain_analyze_verbose_buffers_jobs_after_partial.sql
galaxy_main=> create index ix_partial_job_state_new on job (state) where state = 'new';
CREATE INDEX
galaxy_main=> explain (analyze, verbose, buffers) SELECT job.id AS job_id, job.create_time AS job_create_time, job.update_time AS job_update_time, job.history_id AS job_history_id, job.library_folder_id AS job_library_folder_id, job.tool_id AS job_tool_id, job.tool_version AS job_tool_version, job.state AS job_state, job.info AS job_info, job.command_line AS job_command_line, job.param_filename AS job_param_filename, job.runner_name AS job_runner_name_1, job.stdout AS job_stdout, job.stderr AS job_stderr, job.exit_code AS job_exit_code, job.traceback AS job_traceback, job.session_id AS job_session_id, job.user_id AS job_user_id, job.job_runner_name AS job_job_runner_name, job.job_runner_external_id AS job_job_runner_external_id, job.destination_id AS job_destination_id, job.destination_params AS job_destination_params, job.object_store_id AS job_object_store_id, job.imported AS job_imported, job.params AS job_params, job.handler AS job_handler
FROM job LEFT OUTER JOIN galaxy_user ON galaxy_user.id = job.user_id
WHERE job.state = 'new' AND (job.user_id IS NULL OR galaxy_user.active = true) AND job.handler = 'main_w3_handler0' AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_dataset ON job.id = job_to_input_dataset.job_id JOIN history_dataset_association ON history_dataset_association.id = job_to_input_dataset.dataset_id JOIN dataset ON dataset.id = history_dataset_association.dataset_id WHERE job.state = 'new' AND (history_dataset_association.state = 'failed_metadata' OR history_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true)) AND job.id NOT IN (SELECT job.id FROM job JOIN job_to_input_library_dataset ON job.id = job_to_input_library_dataset.job_id JOIN library_dataset_dataset_association ON library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id JOIN dataset ON dataset.id = library_dataset_dataset_association.dataset_id WHERE job.state = 'new' AND (library_dataset_dataset_association.state IS NOT NULL OR library_dataset_dataset_association.deleted = true OR dataset.state != 'ok' OR dataset.deleted = true))
ORDER BY job.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1471837.09..1471837.16 rows=27 width=2100) (actual time=29857.544..29857.554 rows=54 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.library_folder_id, public.job.tool_id, public.job.tool_version, public.job.state, public.job.info, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.exit_code, public.job.traceback, public.job.session_id, public.job.user_id, public.job.job_runner_name, public.job.job_runner_external_id, public.job.destination_id, public.job.destination_params, public.job.object_store_id, public.job.imported, public.job.params, public.job.handler
Sort Key: public.job.id
Sort Method: quicksort Memory: 35kB
Buffers: shared hit=38338 read=5936 dirtied=7
-> Nested Loop Left Join (cost=1471148.22..1471836.45 rows=27 width=2100) (actual time=29766.187..29857.388 rows=54 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.library_folder_id, public.job.tool_id, public.job.tool_version, public.job.state, public.job.info, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.exit_code, public.job.traceback, public.job.session_id, public.job.user_id, public.job.job_runner_name, public.job.job_runner_external_id, public.job.destination_id, public.job.destination_params, public.job.object_store_id, public.job.imported, public.job.params, public.job.handler
Filter: ((public.job.user_id IS NULL) OR galaxy_user.active)
Rows Removed by Filter: 32
Buffers: shared hit=38338 read=5936 dirtied=7
-> Bitmap Heap Scan on public.job (cost=1471148.22..1471603.22 rows=28 width=2100) (actual time=29766.164..29767.531 rows=86 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Recheck Cond: (((public.job.state)::text = 'new'::text) AND ((public.job.handler)::text = 'main_w3_handler0'::text))
Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
Rows Removed by Filter: 307
Buffers: shared hit=38004 read=5915 dirtied=7
-> BitmapAnd (cost=3287.66..3287.66 rows=114 width=0) (actual time=9331.056..9331.056 rows=0 loops=1)
Buffers: shared hit=8 read=1934
-> Bitmap Index Scan on ix_partial_job_state_new (cost=0.00..53.64 rows=30378 width=0) (actual time=0.934..0.934 rows=2322 loops=1)
Index Cond: ((public.job.state)::text = 'new'::text)
Buffers: shared hit=3 read=6
-> Bitmap Index Scan on ix_job_handler (cost=0.00..3233.76 rows=52525 width=0) (actual time=9329.297..9329.297 rows=83748 loops=1)
Index Cond: ((public.job.handler)::text = 'main_w3_handler0'::text)
Buffers: shared hit=5 read=1928
SubPlan 1
-> Nested Loop (cost=0.00..1467677.60 rows=28238 width=4) (actual time=0.274..20316.284 rows=2153 loops=1)
Output: public.job.id
Buffers: shared hit=37640 read=3970 dirtied=7
-> Nested Loop (cost=0.00..982315.60 rows=33442 width=25) (actual time=0.153..17565.396 rows=3442 loops=1)
Output: public.job.id, history_dataset_association.dataset_id, history_dataset_association.state, history_dataset_association.deleted
Buffers: shared hit=21275 read=3096 dirtied=7
-> Nested Loop (cost=0.00..510604.01 rows=35171 width=8) (actual time=0.124..9371.820 rows=4053 loops=1)
Output: public.job.id, job_to_input_dataset.dataset_id
Buffers: shared hit=8685 read=1885 dirtied=7
-> Index Scan using ix_partial_job_state_new on public.job (cost=0.00..41415.01 rows=30378 width=4) (actual time=0.083..4339.307 rows=2319 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Index Cond: ((public.job.state)::text = 'new'::text)
Buffers: shared hit=369 read=1126 dirtied=1
-> Index Scan using ix_job_to_input_dataset_job_id on public.job_to_input_dataset (cost=0.00..15.26 rows=19 width=8) (actual time=2.119..2.166 rows=2 loops=2319)
Output: job_to_input_dataset.id, job_to_input_dataset.job_id, job_to_input_dataset.dataset_id, job_to_input_dataset.name
Index Cond: (job_to_input_dataset.job_id = public.job.id)
Buffers: shared hit=8316 read=759 dirtied=6
-> Index Scan using history_dataset_association_pkey on public.history_dataset_association (cost=0.00..13.40 rows=1 width=25) (actual time=2.016..2.019 rows=1 loops=4053)
Output: history_dataset_association.id, history_dataset_association.history_id, history_dataset_association.dataset_id, history_dataset_association.create_time, history_dataset_association.update_time, history_dataset_association.copied_from_history_dataset_association_id, history_dataset_association.hid, history_dataset_association.name, history_dataset_association.info, history_dataset_association.blurb, history_dataset_association.peek, history_dataset_association.extension, history_dataset_association.metadata, history_dataset_association.parent_id, history_dataset_association.designation, history_dataset_association.deleted, history_dataset_association.visible, history_dataset_association.copied_from_library_dataset_dataset_association_id, history_dataset_association.state, history_dataset_association.purged, history_dataset_association.tool_version, history_dataset_association.extended_metadata_id, history_dataset_association.hidden_beneath_collection_instance_id
Index Cond: (history_dataset_association.id = job_to_input_dataset.dataset_id)
Buffers: shared hit=12590 read=1211
-> Index Scan using dataset_pkey on public.dataset (cost=0.00..14.50 rows=1 width=8) (actual time=0.794..0.796 rows=1 loops=3442)
Output: public.dataset.id, public.dataset.create_time, public.dataset.update_time, public.dataset.state, public.dataset.deleted, public.dataset.purged, public.dataset.file_size, public.dataset.purgable, public.dataset.external_filename, public.dataset._extra_files_path, public.dataset.total_size, public.dataset.object_store_id, public.dataset.uuid
Index Cond: (public.dataset.id = history_dataset_association.dataset_id)
Filter: (((history_dataset_association.state)::text = 'failed_metadata'::text) OR history_dataset_association.deleted OR ((public.dataset.state)::text <> 'ok'::text) OR public.dataset.deleted)
Rows Removed by Filter: 0
Buffers: shared hit=16365 read=874
SubPlan 2
-> Nested Loop (cost=0.00..112.36 rows=1 width=4) (actual time=112.738..112.738 rows=0 loops=1)
Output: public.job.id
Buffers: shared hit=27 read=10
-> Nested Loop (cost=0.00..97.88 rows=1 width=25) (actual time=112.737..112.737 rows=0 loops=1)
Output: public.job.id, library_dataset_dataset_association.dataset_id, library_dataset_dataset_association.state, library_dataset_dataset_association.deleted
Buffers: shared hit=27 read=10
-> Nested Loop (cost=0.00..89.60 rows=1 width=8) (actual time=112.736..112.736 rows=0 loops=1)
Output: public.job.id, job_to_input_library_dataset.ldda_id
Buffers: shared hit=27 read=10
-> Seq Scan on public.job_to_input_library_dataset (cost=0.00..1.09 rows=9 width=8) (actual time=0.004..0.016 rows=9 loops=1)
Output: job_to_input_library_dataset.id, job_to_input_library_dataset.job_id, job_to_input_library_dataset.ldda_id, job_to_input_library_dataset.name
Buffers: shared hit=1
-> Index Scan using job_pkey on public.job (cost=0.00..9.82 rows=1 width=4) (actual time=12.520..12.520 rows=0 loops=9)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Index Cond: (public.job.id = job_to_input_library_dataset.job_id)
Filter: ((public.job.state)::text = 'new'::text)
Rows Removed by Filter: 1
Buffers: shared hit=26 read=10
-> Index Scan using library_dataset_dataset_association_pkey on public.library_dataset_dataset_association (cost=0.00..8.27 rows=1 width=25) (never executed)
Output: library_dataset_dataset_association.id, library_dataset_dataset_association.library_dataset_id, library_dataset_dataset_association.dataset_id, library_dataset_dataset_association.create_time, library_dataset_dataset_association.update_time, library_dataset_dataset_association.copied_from_history_dataset_association_id, library_dataset_dataset_association.copied_from_library_dataset_dataset_association_id, library_dataset_dataset_association.name, library_dataset_dataset_association.info, library_dataset_dataset_association.blurb, library_dataset_dataset_association.peek, library_dataset_dataset_association.extension, library_dataset_dataset_association.metadata, library_dataset_dataset_association.parent_id, library_dataset_dataset_association.designation, library_dataset_dataset_association.deleted, library_dataset_dataset_association.visible, library_dataset_dataset_association.user_id, library_dataset_dataset_association.message, library_dataset_dataset_association.state, library_dataset_dataset_association.tool_version, library_dataset_dataset_association.extended_metadata_id
Index Cond: (library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id)
-> Index Scan using dataset_pkey on public.dataset (cost=0.00..14.47 rows=1 width=8) (never executed)
Output: public.dataset.id, public.dataset.create_time, public.dataset.update_time, public.dataset.state, public.dataset.deleted, public.dataset.purged, public.dataset.file_size, public.dataset.purgable, public.dataset.external_filename, public.dataset._extra_files_path, public.dataset.total_size, public.dataset.object_store_id, public.dataset.uuid
Index Cond: (public.dataset.id = library_dataset_dataset_association.dataset_id)
Filter: ((library_dataset_dataset_association.state IS NOT NULL) OR library_dataset_dataset_association.deleted OR ((public.dataset.state)::text <> 'ok'::text) OR public.dataset.deleted)
-> Index Scan using galaxy_user_pkey on public.galaxy_user (cost=0.00..8.32 rows=1 width=5) (actual time=1.039..1.041 rows=1 loops=86)
Output: galaxy_user.id, galaxy_user.create_time, galaxy_user.update_time, galaxy_user.email, galaxy_user.password, galaxy_user.external, galaxy_user.deleted, galaxy_user.purged, galaxy_user.username, galaxy_user.form_values_id, galaxy_user.disk_usage, galaxy_user.active, galaxy_user.activation_token, galaxy_user.last_password_change
Index Cond: (galaxy_user.id = public.job.user_id)
Buffers: shared hit=334 read=21
Total runtime: 29857.904 ms
(83 rows)
Raw
explain_analyze_verbose_buffers_jobs_after_partial_2nd_time.sql
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1471839.78..1471839.84 rows=27 width=2100) (actual time=10479.282..10479.289 rows=59 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.library_folder_id, public.job.tool_id, public.job.tool_version, public.job.state, public.job.info, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.exit_code, public.job.traceback, public.job.session_id, public.job.user_id, public.job.job_runner_name, public.job.job_runner_external_id, public.job.destination_id, public.job.destination_params, public.job.object_store_id, public.job.imported, public.job.params, public.job.handler
Sort Key: public.job.id
Sort Method: quicksort Memory: 36kB
Buffers: shared hit=42501 read=1857 dirtied=12
-> Nested Loop Left Join (cost=1471150.91..1471839.13 rows=27 width=2100) (actual time=10477.276..10479.184 rows=59 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.library_folder_id, public.job.tool_id, public.job.tool_version, public.job.state, public.job.info, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.exit_code, public.job.traceback, public.job.session_id, public.job.user_id, public.job.job_runner_name, public.job.job_runner_external_id, public.job.destination_id, public.job.destination_params, public.job.object_store_id, public.job.imported, public.job.params, public.job.handler
Filter: ((public.job.user_id IS NULL) OR galaxy_user.active)
Rows Removed by Filter: 32
Buffers: shared hit=42501 read=1857 dirtied=12
-> Bitmap Heap Scan on public.job (cost=1471150.91..1471605.90 rows=28 width=2100) (actual time=10477.257..10478.352 rows=91 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Recheck Cond: (((public.job.state)::text = 'new'::text) AND ((public.job.handler)::text = 'main_w3_handler0'::text))
Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
Rows Removed by Filter: 307
Buffers: shared hit=42146 read=1857 dirtied=12
-> BitmapAnd (cost=3287.66..3287.66 rows=114 width=0) (actual time=8571.599..8571.599 rows=0 loops=1)
Buffers: shared hit=226 read=1715
-> Bitmap Index Scan on ix_partial_job_state_new (cost=0.00..53.64 rows=30378 width=0) (actual time=0.911..0.911 rows=2339 loops=1)
Index Cond: ((public.job.state)::text = 'new'::text)
Buffers: shared hit=8
-> Bitmap Index Scan on ix_job_handler (cost=0.00..3233.76 rows=52525 width=0) (actual time=8570.114..8570.114 rows=83753 loops=1)
Index Cond: ((public.job.handler)::text = 'main_w3_handler0'::text)
Buffers: shared hit=218 read=1715
SubPlan 1
-> Nested Loop (cost=0.00..1467680.29 rows=28238 width=4) (actual time=0.884..1902.810 rows=2153 loops=1)
Output: public.job.id
Buffers: shared hit=41549 read=142 dirtied=12
-> Nested Loop (cost=0.00..982315.60 rows=33442 width=25) (actual time=0.136..1764.603 rows=3444 loops=1)
Output: public.job.id, history_dataset_association.dataset_id, history_dataset_association.state, history_dataset_association.deleted
Buffers: shared hit=24310 read=129 dirtied=12
-> Nested Loop (cost=0.00..510604.01 rows=35171 width=8) (actual time=0.119..1387.388 rows=4055 loops=1)
Output: public.job.id, job_to_input_dataset.dataset_id
Buffers: shared hit=10536 read=93 dirtied=12
-> Index Scan using ix_partial_job_state_new on public.job (cost=0.00..41415.01 rows=30378 width=4) (actual time=0.076..1365.879 rows=2332 loops=1)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Index Cond: ((public.job.state)::text = 'new'::text)
Buffers: shared hit=1418 read=93 dirtied=8
-> Index Scan using ix_job_to_input_dataset_job_id on public.job_to_input_dataset (cost=0.00..15.26 rows=19 width=8) (actual time=0.006..0.007 rows=2 loops=2332)
Output: job_to_input_dataset.id, job_to_input_dataset.job_id, job_to_input_dataset.dataset_id, job_to_input_dataset.name
Index Cond: (job_to_input_dataset.job_id = public.job.id)
Buffers: shared hit=9118 dirtied=4
-> Index Scan using history_dataset_association_pkey on public.history_dataset_association (cost=0.00..13.40 rows=1 width=25) (actual time=0.091..0.091 rows=1 loops=4055)
Output: history_dataset_association.id, history_dataset_association.history_id, history_dataset_association.dataset_id, history_dataset_association.create_time, history_dataset_association.update_time, history_dataset_association.copied_from_history_dataset_association_id, history_dataset_association.hid, history_dataset_association.name, history_dataset_association.info, history_dataset_association.blurb, history_dataset_association.peek, history_dataset_association.extension, history_dataset_association.metadata, history_dataset_association.parent_id, history_dataset_association.designation, history_dataset_association.deleted, history_dataset_association.visible, history_dataset_association.copied_from_library_dataset_dataset_association_id, history_dataset_association.state, history_dataset_association.purged, history_dataset_association.tool_version, history_dataset_association.extended_metadata_id, history_dataset_association.hidden_beneath_collection_instance_id
Index Cond: (history_dataset_association.id = job_to_input_dataset.dataset_id)
Buffers: shared hit=13774 read=36
-> Index Scan using dataset_pkey on public.dataset (cost=0.00..14.50 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=3444)
Output: public.dataset.id, public.dataset.create_time, public.dataset.update_time, public.dataset.state, public.dataset.deleted, public.dataset.purged, public.dataset.file_size, public.dataset.purgable, public.dataset.external_filename, public.dataset._extra_files_path, public.dataset.total_size, public.dataset.object_store_id, public.dataset.uuid
Index Cond: (public.dataset.id = history_dataset_association.dataset_id)
Filter: (((history_dataset_association.state)::text = 'failed_metadata'::text) OR history_dataset_association.deleted OR ((public.dataset.state)::text <> 'ok'::text) OR public.dataset.deleted)
Rows Removed by Filter: 0
Buffers: shared hit=17239 read=13
SubPlan 2
-> Nested Loop (cost=0.00..112.36 rows=1 width=4) (actual time=0.092..0.092 rows=0 loops=1)
Output: public.job.id
Buffers: shared hit=37
-> Nested Loop (cost=0.00..97.88 rows=1 width=25) (actual time=0.091..0.091 rows=0 loops=1)
Output: public.job.id, library_dataset_dataset_association.dataset_id, library_dataset_dataset_association.state, library_dataset_dataset_association.deleted
Buffers: shared hit=37
-> Nested Loop (cost=0.00..89.60 rows=1 width=8) (actual time=0.091..0.091 rows=0 loops=1)
Output: public.job.id, job_to_input_library_dataset.ldda_id
Buffers: shared hit=37
-> Seq Scan on public.job_to_input_library_dataset (cost=0.00..1.09 rows=9 width=8) (actual time=0.004..0.007 rows=9 loops=1)
Output: job_to_input_library_dataset.id, job_to_input_library_dataset.job_id, job_to_input_library_dataset.ldda_id, job_to_input_library_dataset.name
Buffers: shared hit=1
-> Index Scan using job_pkey on public.job (cost=0.00..9.82 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=9)
Output: public.job.id, public.job.create_time, public.job.update_time, public.job.history_id, public.job.tool_id, public.job.state, public.job.command_line, public.job.param_filename, public.job.runner_name, public.job.stdout, public.job.stderr, public.job.session_id, public.job.traceback, public.job.tool_version, public.job.job_runner_name, public.job.job_runner_external_id, public.job.info, public.job.library_folder_id, public.job.user_id, public.job.imported, public.job.object_store_id, public.job.params, public.job.handler, public.job.exit_code, public.job.destination_id, public.job.destination_params
Index Cond: (public.job.id = job_to_input_library_dataset.job_id)
Filter: ((public.job.state)::text = 'new'::text)
Rows Removed by Filter: 1
Buffers: shared hit=36
-> Index Scan using library_dataset_dataset_association_pkey on public.library_dataset_dataset_association (cost=0.00..8.27 rows=1 width=25) (never executed)
Output: library_dataset_dataset_association.id, library_dataset_dataset_association.library_dataset_id, library_dataset_dataset_association.dataset_id, library_dataset_dataset_association.create_time, library_dataset_dataset_association.update_time, library_dataset_dataset_association.copied_from_history_dataset_association_id, library_dataset_dataset_association.copied_from_library_dataset_dataset_association_id, library_dataset_dataset_association.name, library_dataset_dataset_association.info, library_dataset_dataset_association.blurb, library_dataset_dataset_association.peek, library_dataset_dataset_association.extension, library_dataset_dataset_association.metadata, library_dataset_dataset_association.parent_id, library_dataset_dataset_association.designation, library_dataset_dataset_association.deleted, library_dataset_dataset_association.visible, library_dataset_dataset_association.user_id, library_dataset_dataset_association.message, library_dataset_dataset_association.state, library_dataset_dataset_association.tool_version, library_dataset_dataset_association.extended_metadata_id
Index Cond: (library_dataset_dataset_association.id = job_to_input_library_dataset.ldda_id)
-> Index Scan using dataset_pkey on public.dataset (cost=0.00..14.47 rows=1 width=8) (never executed)
Output: public.dataset.id, public.dataset.create_time, public.dataset.update_time, public.dataset.state, public.dataset.deleted, public.dataset.purged, public.dataset.file_size, public.dataset.purgable, public.dataset.external_filename, public.dataset._extra_files_path, public.dataset.total_size, public.dataset.object_store_id, public.dataset.uuid
Index Cond: (public.dataset.id = library_dataset_dataset_association.dataset_id)
Filter: ((library_dataset_dataset_association.state IS NOT NULL) OR library_dataset_dataset_association.deleted OR ((public.dataset.state)::text <> 'ok'::text) OR public.dataset.deleted)
-> Index Scan using galaxy_user_pkey on public.galaxy_user (cost=0.00..8.32 rows=1 width=5) (actual time=0.006..0.007 rows=1 loops=91)
Output: galaxy_user.id, galaxy_user.create_time, galaxy_user.update_time, galaxy_user.email, galaxy_user.password, galaxy_user.external, galaxy_user.deleted, galaxy_user.purged, galaxy_user.username, galaxy_user.form_values_id, galaxy_user.disk_usage, galaxy_user.active, galaxy_user.activation_token, galaxy_user.last_password_change
Index Cond: (galaxy_user.id = public.job.user_id)
Buffers: shared hit=355
Total runtime: 10479.613 ms
(83 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment