explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uwHR

Settings
# exclusive inclusive rows x rows loops node
1. 0.121 128,449.518 ↑ 1.0 1 1

Aggregate (cost=5,919,320.94..5,919,320.95 rows=1 width=136) (actual time=128,449.518..128,449.518 rows=1 loops=1)

2. 3,015.690 128,449.397 ↑ 6.8 4 1

Hash Join (cost=367,785.41..5,919,320.06 rows=27 width=56) (actual time=16,280.087..128,449.397 rows=4 loops=1)

  • Hash Cond: (a.stats_container_id = b.stats_container_id)
  • Join Filter: (((a.data_written_size_bytes)::numeric <= b.unmorphed_usage_bytes_15m) AND (b.unmorphed_usage_bytes_15m <= ((a.source_delta_size_bytes)::numeric * 1.05)))
3. 120,682.708 125,395.677 ↑ 1.1 12,802,831 1

Bitmap Heap Scan on protection_job_run_entities a (cost=365,003.39..5,864,027.08 rows=14,001,446 width=24) (actual time=5,304.534..125,395.677 rows=12,802,831 loops=1)

  • Recheck Cond: ((start_time_usecs >= '1578502213755482'::bigint) AND (start_time_usecs <= '1581094213755482'::bigint))
  • Heap Blocks: exact=966586
4. 4,712.969 4,712.969 ↑ 1.1 12,927,992 1

Bitmap Index Scan on protection_job_run_entities_n1 (cost=0.00..361,503.03 rows=14,001,446 width=0) (actual time=4,712.969..4,712.969 rows=12,927,992 loops=1)

  • Index Cond: ((start_time_usecs >= '1578502213755482'::bigint) AND (start_time_usecs <= '1581094213755482'::bigint))
5. 0.046 38.030 ↓ 6.0 6 1

Hash (cost=2,782.00..2,782.00 rows=1 width=40) (actual time=38.030..38.030 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.002 37.984 ↓ 6.0 6 1

Subquery Scan on b (cost=2,781.97..2,782.00 rows=1 width=40) (actual time=37.976..37.984 rows=6 loops=1)

7. 0.039 37.982 ↓ 6.0 6 1

GroupAggregate (cost=2,781.97..2,781.99 rows=1 width=70) (actual time=37.975..37.982 rows=6 loops=1)

  • Group Key: u.cluster_identifier, u.stats_container_id
8. 0.061 37.943 ↓ 22.0 22 1

Sort (cost=2,781.97..2,781.97 rows=1 width=46) (actual time=37.939..37.943 rows=22 loops=1)

  • Sort Key: u.stats_container_id
  • Sort Method: quicksort Memory: 26kB
9. 0.155 37.882 ↓ 22.0 22 1

Nested Loop (cost=2,717.65..2,781.96 rows=1 width=46) (actual time=30.199..37.882 rows=22 loops=1)

10. 0.099 28.331 ↓ 12.0 12 1

Bitmap Heap Scan on protection_job_run_entities u (cost=2,716.93..2,773.21 rows=1 width=46) (actual time=28.253..28.331 rows=12 loops=1)

  • Recheck Cond: ((job_id = 2340) AND (entity_id = 102))
  • Filter: ((is_full_backup IS NULL) AND (source_delta_size_bytes > 0) AND (data_written_size_bytes > 0) AND ((cluster_identifier)::text = '998663406507174:1556895109547'::text) AND (status = 4))
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=15
11. 0.065 28.232 ↓ 0.0 0 1

BitmapAnd (cost=2,716.93..2,716.93 rows=14 width=0) (actual time=28.232..28.232 rows=0 loops=1)

12. 0.027 0.027 ↑ 1,154.2 19 1

Bitmap Index Scan on protection_job_run_entities_n3 (cost=0.00..413.04 rows=21,929 width=0) (actual time=0.027..0.027 rows=19 loops=1)

  • Index Cond: (job_id = 2340)
13. 28.140 28.140 ↑ 1.1 113,657 1

Bitmap Index Scan on protection_job_run_entities_n5 (cost=0.00..2,303.64 rows=122,543 width=0) (actual time=28.140..28.140 rows=113,657 loops=1)

  • Index Cond: (entity_id = 102)
14. 9.396 9.396 ↓ 2.0 2 12

Index Scan using protection_job_run_containerstats_n1 on protection_job_run_containerstats v (cost=0.71..8.74 rows=1 width=54) (actual time=0.704..0.783 rows=2 loops=12)

  • Index Cond: ((cluster_identifier = '998663406507174:1556895109547'::text) AND (entity_id = u.stats_container_id) AND (ts < (to_timestamp(((((u.start_time_usecs)::numeric / 1000000.0) + 900.00))::double precision))::timestamp without time zone))
Planning time : 1.990 ms
Execution time : 128,454.164 ms