explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Ugh

Settings
# exclusive inclusive rows x rows loops node
1. 0.139 3,450.680 ↓ 67.0 67 1

Nested Loop (cost=58,998.56..63,251.32 rows=1 width=134) (actual time=2,427.166..3,450.680 rows=67 loops=1)

2. 0.535 3,450.206 ↓ 67.0 67 1

Nested Loop (cost=58,998.27..63,251.00 rows=1 width=245) (actual time=2,427.144..3,450.206 rows=67 loops=1)

3. 0.277 3,449.014 ↓ 73.0 73 1

Merge Join (cost=58,997.99..63,248.87 rows=1 width=198) (actual time=2,427.114..3,449.014 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. 451.300 3,447.887 ↑ 862.2 73 1

GroupAggregate (cost=58,888.74..62,350.39 rows=62,939 width=134) (actual time=2,426.372..3,447.887 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,717.221 2,996.587 ↓ 13.1 822,941 1

Sort (cost=58,888.74..59,046.09 rows=62,939 width=74) (actual time=2,426.190..2,996.587 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. 274.548 1,279.366 ↓ 13.1 822,941 1

Hash Join (cost=176.92..51,071.98 rows=62,939 width=74) (actual time=0.819..1,279.366 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. 333.215 1,004.166 ↓ 4.0 1,000,602 1

Hash Join (cost=111.06..49,117.72 rows=251,756 width=104) (actual time=0.157..1,004.166 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.204 670.824 ↑ 1.0 1,003,900 1

Append (cost=0.00..41,453.78 rows=1,007,025 width=74) (actual time=0.019..670.824 rows=1,003,900 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) AND ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[])))
10. 581.617 581.617 ↑ 1.0 1,003,900 1

Seq Scan on protection_job_runs_0015000002i3ckvaav (cost=0.00..41,453.78 rows=1,007,024 width=74) (actual time=0.015..581.617 rows=1,003,900 loops=1)

  • Filter: (((account_id)::text = '0015000002I3CkVAAV'::text) AND ((cluster_identifier)::text = ANY ('{2690990343931928:1548976579658,4753914011867584:1537769063609,4590240874833370:1553378360317,3358568177796484:1552939875724,3773445208992781:1552938645523,8374834709413972:1535586101500,1616226053966050:1530308452261}'::text[])))
  • Rows Removed by Filter: 6066
11. 0.007 0.127 ↑ 1.1 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.061 0.120 ↑ 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.065..0.120 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.059 0.059 ↑ 1.1 37 1

Bitmap Index Scan on view_boxes_cid_vuid (cost=0.00..30.29 rows=41 width=0) (actual time=0.058..0.059 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.007 0.652 ↑ 1.0 39 1

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

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

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

  • Group Key: (registered_sources_1.cluster_identifier)::text, registered_sources_1.source_id
16. 0.621 0.621 ↑ 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.621 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.095 0.850 ↓ 1,123.0 1,123 1

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

18. 0.022 0.755 ↓ 39.0 39 1

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

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

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

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

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

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

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

Bitmap Heap Scan on cluster (cost=26.00..43.44 rows=7 width=42) (actual time=0.064..0.072 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.057 0.057 ↑ 1.2 6 1

Bitmap Index Scan on cluster_pkey (cost=0.00..25.99 rows=7 width=0) (actual time=0.057..0.057 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.008..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))