explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7CbM

Settings
# exclusive inclusive rows x rows loops node
1. 0.177 1,752.075 ↓ 67.0 67 1

Nested Loop (cost=76.00..178,136.91 rows=1 width=134) (actual time=9.909..1,752.075 rows=67 loops=1)

2. 0.673 1,751.563 ↓ 67.0 67 1

Nested Loop (cost=75.72..178,136.59 rows=1 width=245) (actual time=9.895..1,751.563 rows=67 loops=1)

3. 0.279 1,750.306 ↓ 73.0 73 1

Merge Join (cost=75.43..178,134.47 rows=1 width=198) (actual time=9.862..1,750.306 rows=73 loops=1)

  • Merge Cond: ((protection_job_runs_0015000002i3ckvaav.cluster_identifier)::text = (registered_sources.cluster_identifier)::text)
  • Join Filter: (protection_job_runs_0015000002i3ckvaav.parent_source_id = registered_sources.source_id)
  • Rows Removed by Join Filter: 1060
4. 0.104 1,749.806 ↑ 7.5 73 1

Merge Join (cost=75.15..177,997.86 rows=548 width=176) (actual time=9.829..1,749.806 rows=73 loops=1)

  • Merge Cond: ((protection_job_runs_0015000002i3ckvaav.cluster_identifier)::text = (cluster.cluster_identifier)::text)
5. 452.482 1,749.623 ↑ 1,383.5 73 1

GroupAggregate (cost=74.88..176,673.13 rows=100,997 width=134) (actual time=9.813..1,749.623 rows=73 loops=1)

  • Group Key: protection_job_runs_0015000002i3ckvaav.cluster_identifier, protection_job_runs_0015000002i3ckvaav.parent_source_id, protection_job_runs_0015000002i3ckvaav.view_box_id, protection_job_runs_0015000002i3ckvaav.job_id
6. 335.929 1,297.141 ↑ 1.0 822,941 1

Merge Semi Join (cost=74.88..144,012.10 rows=830,562 width=74) (actual time=9.514..1,297.141 rows=822,941 loops=1)

  • Merge Cond: ((protection_job_runs_0015000002i3ckvaav.cluster_identifier)::text = (view_boxes_1.cluster_identifier)::text)
  • Join Filter: (protection_job_runs_0015000002i3ckvaav.view_box_id = view_boxes_1.view_box_id)
  • Rows Removed by Join Filter: 1065099
7. 292.446 904.342 ↑ 1.0 823,262 1

Merge Semi Join (cost=66.26..140,589.96 rows=830,562 width=104) (actual time=9.471..904.342 rows=823,262 loops=1)

  • Merge 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. 611.215 611.215 ↑ 1.0 1,009,856 1

Index Scan using protection_job_runs_0015000002i3ckvaav_cid_pid_vid_jid on protection_job_runs_0015000002i3ckvaav (cost=0.55..135,449.05 rows=1,009,966 width=74) (actual time=0.019..611.215 rows=1,009,856 loops=1)

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

Sort (cost=65.71..65.81 rows=39 width=38) (actual time=0.648..0.681 rows=39 loops=1)

  • Sort Key: registered_sources_1.cluster_identifier, registered_sources_1.source_id
  • Sort Method: quicksort Memory: 28kB
10. 0.615 0.615 ↑ 1.0 39 1

Seq Scan on registered_sources registered_sources_1 (cost=0.00..64.68 rows=39 width=38) (actual time=0.020..0.615 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
11. 56.751 56.870 ↓ 25,978.2 1,065,108 1

Materialize (cost=0.28..170.55 rows=41 width=38) (actual time=0.028..56.870 rows=1,065,108 loops=1)

12. 0.119 0.119 ↑ 1.1 37 1

Index Only Scan using view_boxes_pkey on view_boxes view_boxes_1 (cost=0.28..170.44 rows=41 width=38) (actual time=0.022..0.119 rows=37 loops=1)

  • Index Cond: (cluster_identifier = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[]))
  • Heap Fetches: 37
13. 0.018 0.079 ↓ 9.0 63 1

Materialize (cost=0.28..54.07 rows=7 width=42) (actual time=0.014..0.079 rows=63 loops=1)

14. 0.061 0.061 ↑ 1.2 6 1

Index Scan using cluster_pkey on cluster (cost=0.28..54.05 rows=7 width=42) (actual time=0.013..0.061 rows=6 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.117 0.221 ↓ 28.8 1,123 1

Materialize (cost=0.28..134.57 rows=39 width=38) (actual time=0.015..0.221 rows=1,123 loops=1)

16. 0.104 0.104 ↑ 1.0 39 1

Index Scan using registered_sources_pkey on registered_sources (cost=0.28..134.47 rows=39 width=38) (actual time=0.014..0.104 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[]))
17. 0.584 0.584 ↑ 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.007..0.008 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))
18. 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))