explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bw7w

Settings
# exclusive inclusive rows x rows loops node
1. 0.161 3,053.214 ↓ 67.0 67 1

Nested Loop (cost=202,201.47..238,206.86 rows=1 width=134) (actual time=2,017.719..3,053.214 rows=67 loops=1)

2. 0.673 3,052.718 ↓ 67.0 67 1

Nested Loop (cost=202,201.18..238,206.54 rows=1 width=245) (actual time=2,017.695..3,052.718 rows=67 loops=1)

3. 0.304 3,051.388 ↓ 73.0 73 1

Merge Join (cost=202,200.90..238,204.42 rows=1 width=198) (actual time=2,017.648..3,051.388 rows=73 loops=1)

  • Merge Cond: ((protection_job_runs_0015000002i3ckvaav.cluster_identifier)::text = (registered_sources.cluster_identifier)::text)
  • Join Filter: (registered_sources.source_id = protection_job_runs_0015000002i3ckvaav.parent_source_id)
  • Rows Removed by Join Filter: 1060
4. 446.749 3,050.216 ↑ 1,383.5 73 1

GroupAggregate (cost=202,092.53..236,829.96 rows=100,997 width=134) (actual time=2,016.856..3,050.216 rows=73 loops=1)

  • Group Key: protection_job_runs_0015000002i3ckvaav.cluster_identifier, protection_job_runs_0015000002i3ckvaav.job_id, protection_job_runs_0015000002i3ckvaav.parent_source_id, protection_job_runs_0015000002i3ckvaav.view_box_id
5. 1,716.580 2,603.467 ↑ 1.0 822,941 1

Sort (cost=202,092.53..204,168.93 rows=830,562 width=74) (actual time=2,016.573..2,603.467 rows=822,941 loops=1)

  • Sort Key: protection_job_runs_0015000002i3ckvaav.cluster_identifier, protection_job_runs_0015000002i3ckvaav.job_id, protection_job_runs_0015000002i3ckvaav.parent_source_id, protection_job_runs_0015000002i3ckvaav.view_box_id
  • Sort Method: external merge Disk: 62704kB
6. 252.911 886.887 ↑ 1.0 822,941 1

Hash Join (cost=176.92..46,617.80 rows=830,562 width=74) (actual time=0.874..886.887 rows=822,941 loops=1)

  • Hash Cond: (((protection_job_runs_0015000002i3ckvaav.cluster_identifier)::text = (view_boxes_1.cluster_identifier)::text) AND (protection_job_runs_0015000002i3ckvaav.view_box_id = view_boxes_1.view_box_id))
7. 383.293 633.834 ↑ 1.0 823,262 1

Hash Join (cost=65.85..40,272.16 rows=830,562 width=104) (actual time=0.712..633.834 rows=823,262 loops=1)

  • Hash Cond: (((protection_job_runs_0015000002i3ckvaav.cluster_identifier)::text = (registered_sources_1.cluster_identifier)::text) AND (protection_job_runs_0015000002i3ckvaav.parent_source_id = registered_sources_1.source_id))
8. 249.859 249.859 ↑ 1.0 1,009,966 1

Seq Scan on protection_job_runs_0015000002i3ckvaav (cost=0.00..32,616.58 rows=1,009,966 width=74) (actual time=0.014..249.859 rows=1,009,966 loops=1)

  • Filter: ((account_id)::text = '0015000002I3CkVAAV'::text)
9. 0.013 0.682 ↑ 1.0 39 1

Hash (cost=65.27..65.27 rows=39 width=38) (actual time=0.682..0.682 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
10. 0.018 0.669 ↑ 1.0 39 1

HashAggregate (cost=64.88..65.27 rows=39 width=38) (actual time=0.666..0.669 rows=39 loops=1)

  • Group Key: (registered_sources_1.cluster_identifier)::text, registered_sources_1.source_id
11. 0.651 0.651 ↑ 1.0 39 1

Seq Scan on registered_sources registered_sources_1 (cost=0.00..64.68 rows=39 width=38) (actual time=0.025..0.651 rows=39 loops=1)

  • Filter: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
  • Rows Removed by Filter: 1544
12. 0.010 0.142 ↑ 1.1 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.062 0.132 ↑ 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.077..0.132 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
14. 0.070 0.070 ↑ 1.1 37 1

Bitmap Index Scan on view_boxes_cid_vuid (cost=0.00..30.29 rows=41 width=0) (actual time=0.070..0.070 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[]))
15. 0.089 0.868 ↓ 1,123.0 1,123 1

Materialize (cost=108.37..108.38 rows=1 width=80) (actual time=0.765..0.868 rows=1,123 loops=1)

16. 0.048 0.779 ↓ 39.0 39 1

Sort (cost=108.37..108.38 rows=1 width=80) (actual time=0.761..0.779 rows=39 loops=1)

  • Sort Key: registered_sources.cluster_identifier
  • Sort Method: quicksort Memory: 30kB
17. 0.031 0.731 ↓ 39.0 39 1

Hash Join (cost=43.52..108.36 rows=1 width=80) (actual time=0.148..0.731 rows=39 loops=1)

  • Hash Cond: ((registered_sources.cluster_identifier)::text = (cluster.cluster_identifier)::text)
18. 0.597 0.597 ↑ 1.0 39 1

Seq Scan on registered_sources (cost=0.00..64.68 rows=39 width=38) (actual time=0.030..0.597 rows=39 loops=1)

  • Filter: ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,33374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
  • Rows Removed by Filter: 1544
19. 0.006 0.103 ↑ 1.2 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.026 0.097 ↑ 1.2 6 1

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

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

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

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

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

  • Index Cond: (((cluster_identifier)::text = (protection_job_runs_0015000002i3ckvaav.cluster_identifier)::text) AND ((cluster_identifier)::text = ANY ('{26909903439313370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[])) AND (job_id = protection_job_runs_0015000002i3ckvaav.job_id))
23. 0.335 0.335 ↑ 1.0 1 67

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

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