explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rhbC

Settings
# exclusive inclusive rows x rows loops node
1. 0.101 3,769.277 ↓ 67.0 67 1

Nested Loop (cost=25,174.22..26,946.67 rows=1 width=134) (actual time=3,428.998..3,769.277 rows=67 loops=1)

2. 0.053 3,768.841 ↓ 67.0 67 1

Nested Loop Left Join (cost=25,173.94..26,946.34 rows=1 width=245) (actual time=3,428.974..3,768.841 rows=67 loops=1)

  • Join Filter: ((protection_job_runs.job_id = protection_job_runs_1.job_id) AND ((protection_job_runs.cluster_identifier)::text = (protection_job_runs_1.cluster_identifier)::text))
3. 0.546 3,768.721 ↓ 67.0 67 1

Nested Loop (cost=25,088.40..26,860.75 rows=1 width=221) (actual time=3,428.935..3,768.721 rows=67 loops=1)

4. 0.249 3,767.664 ↓ 73.0 73 1

Merge Join (cost=25,088.11..26,858.60 rows=1 width=166) (actual time=3,428.902..3,767.664 rows=73 loops=1)

  • Merge Cond: ((protection_job_runs.cluster_identifier)::text = (registered_sources.cluster_identifier)::text)
  • Join Filter: (registered_sources.source_id = protection_job_runs.parent_source_id)
  • Rows Removed by Join Filter: 1060
5. 204.167 3,767.057 ↑ 538.5 73 1

GroupAggregate (cost=24,980.65..26,258.33 rows=39,313 width=102) (actual time=3,428.623..3,767.057 rows=73 loops=1)

  • Group Key: protection_job_runs.cluster_identifier, protection_job_runs.job_id, protection_job_runs.parent_source_id, protection_job_runs.view_box_id
6. 1,191.228 3,562.890 ↓ 20.9 822,941 1

Sort (cost=24,980.65..25,078.94 rows=39,313 width=66) (actual time=3,428.566..3,562.890 rows=822,941 loops=1)

  • Sort Key: protection_job_runs.cluster_identifier, protection_job_runs.job_id, protection_job_runs.parent_source_id, protection_job_runs.view_box_id
  • Sort Method: external merge Disk: 56288kB
7. 260.638 2,371.662 ↓ 20.9 822,941 1

Hash Join (cost=175.59..21,980.54 rows=39,313 width=66) (actual time=0.278..2,371.662 rows=822,941 loops=1)

  • Hash Cond: (((protection_job_runs.cluster_identifier)::text = (view_boxes_1.cluster_identifier)::text) AND (protection_job_runs.view_box_id = view_boxes_1.view_box_id))
8. 263.631 2,110.907 ↓ 3.0 823,262 1

Nested Loop (cost=64.52..19,842.18 rows=270,204 width=96) (actual time=0.151..2,110.907 rows=823,262 loops=1)

9. 0.131 0.236 ↑ 1.0 39 1

HashAggregate (cost=63.97..64.36 rows=39 width=38) (actual time=0.120..0.236 rows=39 loops=1)

  • Group Key: (registered_sources_1.cluster_identifier)::text, registered_sources_1.source_id
10. 0.056 0.105 ↑ 1.0 39 1

Bitmap Heap Scan on registered_sources registered_sources_1 (cost=26.27..63.77 rows=39 width=38) (actual time=0.059..0.105 rows=39 loops=1)

  • Recheck Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
  • Heap Blocks: exact=21
11. 0.049 0.049 ↑ 1.0 39 1

Bitmap Index Scan on registered_sources_cid_sid (cost=0.00..26.26 rows=39 width=0) (actual time=0.049..0.049 rows=39 loops=1)

  • Index Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
12. 1,847.040 1,847.040 ↓ 1,407.3 21,109 39

Index Scan using protection_job_runs_cid_jid_pid_vid on protection_job_runs (cost=0.56..506.97 rows=15 width=66) (actual time=19.131..47.360 rows=21,109 loops=39)

  • Index Cond: (((cluster_identifier)::text = (registered_sources_1.cluster_identifier)::text) AND (parent_source_id = registered_sources_1.source_id))
13. 0.011 0.117 ↑ 1.1 37 1

Hash (cost=110.45..110.45 rows=41 width=38) (actual time=0.117..0.117 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.055 0.106 ↑ 1.1 37 1

Bitmap Heap Scan on view_boxes view_boxes_1 (cost=30.30..110.45 rows=41 width=38) (actual time=0.061..0.106 rows=37 loops=1)

  • Recheck Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
  • Heap Blocks: exact=21
15. 0.051 0.051 ↑ 1.1 37 1

Bitmap Index Scan on view_boxes_cid_vuid (cost=0.00..30.29 rows=41 width=0) (actual time=0.051..0.051 rows=37 loops=1)

  • Index Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
16. 0.089 0.358 ↓ 1,123.0 1,123 1

Materialize (cost=107.46..107.47 rows=1 width=80) (actual time=0.265..0.358 rows=1,123 loops=1)

17. 0.041 0.269 ↓ 39.0 39 1

Sort (cost=107.46..107.47 rows=1 width=80) (actual time=0.262..0.269 rows=39 loops=1)

  • Sort Key: registered_sources.cluster_identifier
  • Sort Method: quicksort Memory: 30kB
18. 0.032 0.228 ↓ 39.0 39 1

Hash Join (cost=69.79..107.45 rows=1 width=80) (actual time=0.164..0.228 rows=39 loops=1)

  • Hash Cond: ((registered_sources.cluster_identifier)::text = (cluster.cluster_identifier)::text)
19. 0.064 0.137 ↑ 1.0 39 1

Bitmap Heap Scan on registered_sources (cost=26.27..63.77 rows=39 width=38) (actual time=0.084..0.137 rows=39 loops=1)

  • Recheck Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:15532938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
  • Heap Blocks: exact=21
20. 0.073 0.073 ↑ 1.0 39 1

Bitmap Index Scan on registered_sources_cid_sid (cost=0.00..26.26 rows=39 width=0) (actual time=0.072..0.073 rows=39 loops=1)

  • Index Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370::1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
21. 0.005 0.059 ↑ 1.2 6 1

Hash (cost=43.44..43.44 rows=7 width=42) (actual time=0.059..0.059 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.014 0.054 ↑ 1.2 6 1

Bitmap Heap Scan on cluster (cost=26.00..43.44 rows=7 width=42) (actual time=0.046..0.054 rows=6 loops=1)

  • Recheck Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,45902408748333781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
  • Heap Blocks: exact=4
23. 0.040 0.040 ↑ 1.2 6 1

Bitmap Index Scan on cluster_pkey (cost=0.00..25.99 rows=7 width=0) (actual time=0.040..0.040 rows=6 loops=1)

  • Index Cond: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,45902408748992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
24. 0.511 0.511 ↑ 1.0 1 73

Index Scan using protection_jobs_pkey on protection_jobs (cost=0.29..2.13 rows=1 width=63) (actual time=0.007..0.007 rows=1 loops=73)

  • Index Cond: (((cluster_identifier)::text = (protection_job_runs.cluster_identifier)::text) AND ((cluster_identifier)::text = ANY ('{2690990343931928:15489765760317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[])) AND (job_id = protection_job_runs.job_id))
25. 0.000 0.067 ↓ 0.0 0 67

GroupAggregate (cost=85.54..85.57 rows=1 width=86) (actual time=0.001..0.001 rows=0 loops=67)

  • Group Key: protection_job_runs_1.cluster_identifier, protection_job_runs_1.job_id, protection_job_runs_1.parent_source_id, protection_job_runs_1.view_box_id
26. 0.036 0.067 ↓ 0.0 0 67

Sort (cost=85.54..85.54 rows=1 width=62) (actual time=0.001..0.001 rows=0 loops=67)

  • Sort Key: protection_job_runs_1.cluster_identifier, protection_job_runs_1.job_id, protection_job_runs_1.parent_source_id, protection_job_runs_1.view_box_id
  • Sort Method: quicksort Memory: 25kB
27. 0.000 0.031 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1.00..85.53 rows=1 width=62) (actual time=0.030..0.031 rows=0 loops=1)

  • Join Filter: (((protection_job_runs_1.cluster_identifier)::text = (view_boxes_2.cluster_identifier)::text) AND (protection_job_runs_1.view_box_id = view_boxes_2.view_box_id))
28. 0.003 0.031 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.72..67.73 rows=1 width=92) (actual time=0.030..0.031 rows=0 loops=1)

29. 0.028 0.028 ↓ 0.0 0 1

Index Scan using protection_job_runs_n1 on protection_job_runs protection_job_runs_1 (cost=0.45..7.93 rows=1 width=62) (actual time=0.028..0.028 rows=0 loops=1)

  • Index Cond: (start_time_usecs >= ((((date_part('epoch'::text, now()))::bigint - 604800) * 1000) * 1000))
30. 0.000 0.000 ↓ 0.0 0

Index Only Scan using registered_sources_cid_sid on registered_sources registered_sources_2 (cost=0.28..30.04 rows=1 width=38) (never executed)

  • Index Cond: ((cluster_identifier = (protection_job_runs_1.cluster_identifier)::text) AND (cluster_identifier = ANY ('{2690990343931928:15489378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[])) AND (source_id = protection_job_runs_1.parent_source_id))
  • Heap Fetches: 0
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using view_boxes_pkey on view_boxes view_boxes_2 (cost=0.28..9.03 rows=1 width=38) (never executed)

  • Index Cond: ((cluster_identifier = (registered_sources_2.cluster_identifier)::text) AND (cluster_identifier = ANY ('{2690990343931928:15489765796517,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[])))
  • Heap Fetches: 0
32. 0.335 0.335 ↑ 1.0 1 67

Index Scan using view_boxes_pkey on view_boxes (cost=0.28..0.32 rows=1 width=55) (actual time=0.004..0.005 rows=1 loops=67)

  • Index Cond: (((cluster_identifier)::text = (protection_job_runs.cluster_identifier)::text) AND (view_box_id = protection_job_runs.view_box_id))