explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sZuE9

Settings
# exclusive inclusive rows x rows loops node
1. 0.156 3,204.324 ↓ 67.0 67 1

Nested Loop (cost=50,211.93..54,477.12 rows=1 width=134) (actual time=2,167.001..3,204.324 rows=67 loops=1)

2. 0.623 3,203.833 ↓ 67.0 67 1

Nested Loop (cost=50,211.65..54,476.79 rows=1 width=245) (actual time=2,166.977..3,203.833 rows=67 loops=1)

3. 0.296 3,202.553 ↓ 73.0 73 1

Merge Join (cost=50,211.37..54,474.67 rows=1 width=198) (actual time=2,166.944..3,202.553 rows=73 loops=1)

  • Merge Cond: ((protection_job_runs_master.cluster_identifier)::text = (registered_sources.cluster_identifier)::text)
  • Join Filter: (registered_sources.source_id = protection_job_runs_master.parent_source_id)
  • Rows Removed by Join Filter: 1060
4. 455.061 3,201.402 ↑ 864.7 73 1

GroupAggregate (cost=50,102.12..53,573.89 rows=63,123 width=134) (actual time=2,166.197..3,201.402 rows=73 loops=1)

  • Group Key: protection_job_runs_master.cluster_identifier, protection_job_runs_master.parent_source_id, protection_job_runs_master.view_box_id, protection_job_runs_master.job_id
5. 1,720.604 2,746.341 ↓ 13.0 822,941 1

Sort (cost=50,102.12..50,259.93 rows=63,123 width=74) (actual time=2,166.012..2,746.341 rows=822,941 loops=1)

  • Sort Key: protection_job_runs_master.cluster_identifier, protection_job_runs_master.parent_source_id, protection_job_runs_master.view_box_id, protection_job_runs_master.job_id
  • Sort Method: external merge Disk: 62704kB
6. 273.803 1,025.737 ↓ 13.0 822,941 1

Hash Join (cost=176.92..42,262.36 rows=63,123 width=74) (actual time=0.758..1,025.737 rows=822,941 loops=1)

  • Hash Cond: (((protection_job_runs_master.cluster_identifier)::text = (registered_sources_1.cluster_identifier)::text) AND (protection_job_runs_master.parent_source_id = registered_sources_1.source_id))
7. 323.431 751.334 ↓ 4.0 1,000,602 1

Hash Join (cost=111.06..40,302.58 rows=252,492 width=104) (actual time=0.151..751.334 rows=1,000,602 loops=1)

  • Hash Cond: (((protection_job_runs_master.cluster_identifier)::text = (view_boxes_1.cluster_identifier)::text) AND (protection_job_runs_master.view_box_id = view_boxes_1.view_box_id))
8. 89.536 427.792 ↑ 1.0 1,009,966 1

Append (cost=0.00..32,616.58 rows=1,009,967 width=74) (actual time=0.013..427.792 rows=1,009,966 loops=1)

9. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on protection_job_runs_master (cost=0.00..0.00 rows=1 width=190) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((account_id)::text = '0015000002I3CkVAAV'::text)
10. 338.253 338.253 ↑ 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.010..338.253 rows=1,009,966 loops=1)

  • Filter: ((account_id)::text = '0015000002I3CkVAAV'::text)
11. 0.007 0.111 ↑ 1.1 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.048 0.104 ↑ 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.104 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
13. 0.056 0.056 ↑ 1.1 37 1

Bitmap Index Scan on view_boxes_cid_vuid (cost=0.00..30.29 rows=41 width=0) (actual time=0.055..0.056 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[]))
14. 0.009 0.600 ↑ 1.0 39 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
15. 0.024 0.591 ↑ 1.0 39 1

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

  • Group Key: (registered_sources_1.cluster_identifier)::text, registered_sources_1.source_id
16. 0.567 0.567 ↑ 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.567 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
17. 0.089 0.855 ↓ 1,123.0 1,123 1

Materialize (cost=109.25..109.49 rows=1 width=80) (actual time=0.729..0.855 rows=1,123 loops=1)

18. 0.026 0.766 ↓ 39.0 39 1

Merge Join (cost=109.25..109.49 rows=1 width=80) (actual time=0.725..0.766 rows=39 loops=1)

  • Merge Cond: ((registered_sources.cluster_identifier)::text = (cluster.cluster_identifier)::text)
19. 0.044 0.654 ↑ 1.0 39 1

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

  • Sort Key: registered_sources.cluster_identifier
  • Sort Method: quicksort Memory: 28kB
20. 0.610 0.610 ↑ 1.0 39 1

Seq Scan on registered_sources (cost=0.00..64.68 rows=39 width=38) (actual time=0.031..0.610 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
21. 0.017 0.086 ↓ 5.3 37 1

Sort (cost=43.53..43.55 rows=7 width=42) (actual time=0.077..0.086 rows=37 loops=1)

  • Sort Key: cluster.cluster_identifier
  • Sort Method: quicksort Memory: 25kB
22. 0.015 0.069 ↑ 1.2 6 1

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

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

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

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

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

  • Index Cond: (((cluster_identifier)::text = (protection_job_runs_master.cluster_identifier)::text) AND ((cluster_identifier)::text = ANY ('{2690990343931928:15489765360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[])) AND (job_id = protection_job_runs_master.job_id))
25. 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_master.cluster_identifier)::text) AND (view_box_id = protection_job_runs_master.view_box_id))