explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6ogz

Settings
# exclusive inclusive rows x rows loops node
1. 0.144 20,405.589 ↓ 2.0 2 1

Nested Loop (cost=298.91..2,840.61 rows=1 width=28) (actual time=18,860.170..20,405.589 rows=2 loops=1)

  • Join Filter: (runtime_app.model_id = app_model.id)
2. 1,453.501 20,404.983 ↓ 7.0 7 1

Nested Loop (cost=298.64..2,840.29 rows=1 width=48) (actual time=18,860.086..20,404.983 rows=7 loops=1)

  • Join Filter: ((app_relation.metadata)::jsonb @? ((('$.processDefinitions[*].id ? (@ == '::text || (proc_def.id_)::text) || ')'::text))::jsonpath)
  • Rows Removed by Join Filter: 110222
3. 42.619 18,769.601 ↓ 7.0 7 1

Hash Join (cost=298.64..1,764.95 rows=1 width=112) (actual time=18,736.929..18,769.601 rows=7 loops=1)

  • Hash Cond: (app_relation.model_id = model_history.id)
4. 46.127 46.127 ↓ 1.0 43,597 1

Seq Scan on app_relation (cost=0.00..1,302.86 rows=43,586 width=100) (actual time=0.035..46.127 rows=43,597 loops=1)

5. 0.092 18,680.855 ↓ 7.0 7 1

Hash (cost=298.62..298.62 rows=1 width=28) (actual time=18,680.852..18,680.855 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.173 18,680.763 ↓ 7.0 7 1

Nested Loop (cost=64.74..298.62 rows=1 width=28) (actual time=2,403.230..18,680.763 rows=7 loops=1)

7. 371.783 18,680.275 ↓ 7.0 7 1

Nested Loop (cost=64.45..268.76 rows=1 width=32) (actual time=2,403.144..18,680.275 rows=7 loops=1)

  • Join Filter: ((runtime_app.app_definition)::jsonb @? ((('$.models[*].id ? (@ == '::text || (model.id)::text) || ')'::text))::jsonpath)
  • Rows Removed by Join Filter: 9380
8. 0.097 2.183 ↓ 7.0 7 1

Nested Loop (cost=64.45..88.86 rows=1 width=451) (actual time=1.331..2.183 rows=7 loops=1)

9. 0.175 1.969 ↓ 6.5 13 1

Nested Loop (cost=64.30..88.53 rows=2 width=12) (actual time=1.311..1.969 rows=13 loops=1)

10. 0.384 1.612 ↓ 6.5 13 1

Bitmap Heap Scan on model_history processDefinition_deployedApplication (cost=64.01..71.92 rows=2 width=8) (actual time=1.268..1.612 rows=13 loops=1)

  • Recheck Cond: ((name)::text ~~* '%test rela%'::text)
  • Rows Removed by Index Recheck: 8
  • Heap Blocks: exact=14
11. 1.228 1.228 ↓ 10.5 21 1

Bitmap Index Scan on idx_model_history_name (cost=0.00..64.01 rows=2 width=0) (actual time=1.226..1.228 rows=21 loops=1)

  • Index Cond: ((name)::text ~~* '%test rela%'::text)
12. 0.182 0.182 ↑ 1.0 1 13

Index Scan using pk_model_history on model_history app_model_history (cost=0.29..8.31 rows=1 width=20) (actual time=0.014..0.014 rows=1 loops=13)

  • Index Cond: (id = processDefinition_deployedApplication.id)
13. 0.117 0.117 ↑ 1.0 1 13

Index Scan using uniq_runtime_app_def_model on runtime_app_def runtime_app (cost=0.14..0.16 rows=1 width=439) (actual time=0.008..0.009 rows=1 loops=13)

  • Index Cond: (model_id = app_model_history.model_id)
14. 18,306.309 18,306.309 ↓ 2.0 1,341 7

Seq Scan on model (cost=0.00..157.67 rows=684 width=12) (actual time=0.465..2,615.187 rows=1,341 loops=7)

  • Filter: (NOT (COALESCE(((model_editor_json)::jsonb #>> '{hidden}'::text[]), 'false'::text))::boolean)
  • Rows Removed by Filter: 5
15. 0.315 0.315 ↑ 1.0 1 7

Index Scan using idx_proc_mod_history_proc on model_history (cost=0.29..29.85 rows=1 width=20) (actual time=0.042..0.045 rows=1 loops=7)

  • Index Cond: (model_id = model.id)
  • Filter: ((model.version - 1) = version)
  • Rows Removed by Filter: 3
16. 181.881 181.881 ↑ 1.0 15,747 7

Seq Scan on act_re_procdef proc_def (cost=0.00..641.69 rows=15,769 width=28) (actual time=0.014..25.983 rows=15,747 loops=7)

17. 0.462 0.462 ↓ 0.0 0 7

Index Scan using pk_model on model app_model (cost=0.28..0.31 rows=1 width=12) (actual time=0.066..0.066 rows=0 loops=7)

  • Index Cond: (id = app_model_history.model_id)
  • Filter: (app_model_history.version = (version - 1))
  • Rows Removed by Filter: 1
Planning time : 8.230 ms
Execution time : 20,405.941 ms