explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H4SIQ

Settings
# exclusive inclusive rows x rows loops node
1. 36.295 62,216.615 ↑ 1.0 1 1

Aggregate (cost=1,189,486.79..1,189,486.80 rows=1 width=8) (actual time=62,216.615..62,216.615 rows=1 loops=1)

2.          

CTE get_plan_revisions

3. 408.768 438.988 ↓ 1.4 191,641 1

Bitmap Heap Scan on plans pl_2 (cost=4,404.89..64,503.21 rows=133,389 width=74) (actual time=36.398..438.988 rows=191,641 loops=1)

  • Recheck Cond: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
  • Filter: (is_visible AND (valid_range && tstzrange('2020-01-17 16:49:36+00'::timestamp with time zone, now(), '[]'::text)))
  • Rows Removed by Filter: 77,768
  • Heap Blocks: exact=32,910
4. 30.220 30.220 ↓ 1.0 269,756 1

Bitmap Index Scan on plans_organization_uuid_idx (cost=0.00..4,371.55 rows=263,816 width=0) (actual time=30.220..30.220 rows=269,756 loops=1)

  • Index Cond: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
5.          

CTE get_plan_info

6. 0.028 638.644 ↑ 1.5 2 1

Subquery Scan on p_info (cost=3,367.68..3,391.02 rows=3 width=112) (actual time=638.417..638.644 rows=2 loops=1)

  • Filter: (p_info.row_num = 1)
  • Rows Removed by Filter: 416
7. 0.200 638.616 ↑ 1.6 418 1

WindowAgg (cost=3,367.68..3,382.69 rows=667 width=192) (actual time=638.415..638.616 rows=418 loops=1)

8. 0.659 638.416 ↑ 1.6 418 1

Sort (cost=3,367.68..3,369.35 rows=667 width=120) (actual time=638.392..638.416 rows=418 loops=1)

  • Sort Key: pl_3.plan_uuid, (lower(pl_3.valid_range)) DESC
  • Sort Method: quicksort Memory: 57kB
9. 637.757 637.757 ↑ 1.6 418 1

CTE Scan on get_plan_revisions pl_3 (cost=0.00..3,336.39 rows=667 width=120) (actual time=47.059..637.757 rows=418 loops=1)

  • Filter: (lower((plan_name)::text) = 'mtech devops'::text)
  • Rows Removed by Filter: 191,223
10. 163.096 62,180.320 ↓ 2,885.1 577,021 1

Unique (cost=1,121,385.97..1,121,590.05 rows=200 width=540) (actual time=61,874.798..62,180.320 rows=577,021 loops=1)

11. 558.644 62,017.224 ↓ 14.1 577,021 1

Sort (cost=1,121,385.97..1,121,488.01 rows=40,817 width=540) (actual time=61,874.794..62,017.224 rows=577,021 loops=1)

  • Sort Key: e.event_uuid
  • Sort Method: external merge Disk: 19,216kB
12. 41,193.479 61,458.580 ↓ 14.1 577,021 1

Merge Left Join (cost=634,844.35..1,111,890.27 rows=40,817 width=540) (actual time=7,767.671..61,458.580 rows=577,021 loops=1)

  • Merge Cond: (e.plan_uuid = pl.plan_uuid)
  • Join Filter: (pl.valid_range @> e.created)
  • Rows Removed by Join Filter: 240,040,736
13. 160.810 7,880.715 ↓ 14.1 577,021 1

Merge Left Join (cost=618,182.39..618,395.66 rows=40,817 width=40) (actual time=7,568.134..7,880.715 rows=577,021 loops=1)

  • Merge Cond: (e.plan_uuid = pl_1.plan_uuid)
14. 344.436 7,686.979 ↓ 14.1 577,021 1

Sort (cost=618,182.31..618,284.35 rows=40,817 width=40) (actual time=7,568.109..7,686.979 rows=577,021 loops=1)

  • Sort Key: e.plan_uuid
  • Sort Method: external merge Disk: 28,280kB
15. 392.114 7,342.543 ↓ 14.1 577,021 1

Hash Semi Join (cost=0.51..615,056.36 rows=40,817 width=40) (actual time=639.424..7,342.543 rows=577,021 loops=1)

  • Hash Cond: (e.plan_uuid = get_plan_info.plan_uuid)
16. 277.385 6,311.773 ↓ 1.0 2,734,747 1

Append (cost=0.41..607,459.23 rows=2,721,121 width=40) (actual time=0.359..6,311.773 rows=2,734,747 loops=1)

  • Subplans Removed: 4
17. 0.083 0.083 ↓ 0.0 0 1

Index Scan using events_org_part_199_l3_f_202007_valid_range_idx on events_org_part_199_l3_f_202007 e (cost=0.41..3.44 rows=1 width=40) (actual time=0.083..0.083 rows=0 loops=1)

  • Index Cond: (valid_range @> now())
  • Filter: (is_visible AND (created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (created <= now()))
18. 0.068 0.068 ↓ 0.0 0 1

Index Scan using events_org_part_199_l3_f_202008_valid_range_idx on events_org_part_199_l3_f_202008 e_1 (cost=0.41..3.44 rows=1 width=40) (actual time=0.068..0.068 rows=0 loops=1)

  • Index Cond: (valid_range @> now())
  • Filter: (is_visible AND (created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (created <= now()))
19. 0.070 0.070 ↓ 0.0 0 1

Index Scan using events_org_part_199_l3_f_202009_valid_range_idx on events_org_part_199_l3_f_202009 e_2 (cost=0.41..3.44 rows=1 width=40) (actual time=0.070..0.070 rows=0 loops=1)

  • Index Cond: (valid_range @> now())
  • Filter: (is_visible AND (created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (created <= now()))
20. 0.052 0.052 ↓ 0.0 0 1

Index Scan using events_org_part_199_l3_f_202010_valid_range_idx on events_org_part_199_l3_f_202010 e_3 (cost=0.29..3.31 rows=1 width=40) (actual time=0.052..0.052 rows=0 loops=1)

  • Index Cond: (valid_range @> now())
  • Filter: (is_visible AND (created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (created <= now()))
21. 1,699.371 1,699.371 ↑ 1.0 431,979 1

Index Scan using events_org_part_199_l3_t_202007_created_idx on events_org_part_199_l3_t_202007 e_4 (cost=0.43..113,925.77 rows=434,811 width=40) (actual time=0.083..1,699.371 rows=431,979 loops=1)

  • Index Cond: ((created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (created <= now()))
  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (valid_range @> now()))
22. 1,040.162 1,040.162 ↓ 1.0 891,210 1

Seq Scan on events_org_part_199_l3_t_202008 e_5 (cost=0.00..179,962.30 rows=890,172 width=40) (actual time=0.036..1,040.162 rows=891,210 loops=1)

  • Filter: (is_visible AND (created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (valid_range @> now()) AND (created <= now()))
23. 1,214.170 1,214.170 ↓ 1.0 961,705 1

Seq Scan on events_org_part_199_l3_t_202009 e_6 (cost=0.00..202,719.33 rows=961,053 width=40) (actual time=0.040..1,214.170 rows=961,705 loops=1)

  • Filter: (is_visible AND (created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (valid_range @> now()) AND (created <= now()))
24. 2,080.412 2,080.412 ↓ 1.0 449,853 1

Seq Scan on events_org_part_199_l3_t_202010 e_7 (cost=0.00..97,219.93 rows=435,077 width=40) (actual time=0.094..2,080.412 rows=449,853 loops=1)

  • Filter: (is_visible AND (created >= '2020-07-17 16:49:36+00'::timestamp with time zone) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (valid_range @> now()) AND (created <= now()))
25. 0.008 638.656 ↑ 1.5 2 1

Hash (cost=0.06..0.06 rows=3 width=16) (actual time=638.656..638.656 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 638.648 638.648 ↑ 1.5 2 1

CTE Scan on get_plan_info (cost=0.00..0.06 rows=3 width=16) (actual time=638.420..638.648 rows=2 loops=1)

27. 32.922 32.926 ↑ 1.5 2 1

Sort (cost=0.08..0.09 rows=3 width=16) (actual time=0.015..32.926 rows=2 loops=1)

  • Sort Key: pl_1.plan_uuid
  • Sort Method: quicksort Memory: 25kB
28. 0.004 0.004 ↑ 1.5 2 1

CTE Scan on get_plan_info pl_1 (cost=0.00..0.06 rows=3 width=16) (actual time=0.003..0.004 rows=2 loops=1)

29. 12,229.901 12,384.386 ↓ 1,805.3 240,806,010 1

Materialize (cost=16,661.96..17,328.90 rows=133,389 width=48) (actual time=130.424..12,384.386 rows=240,806,010 loops=1)

30. 113.192 154.485 ↓ 1.4 188,670 1

Sort (cost=16,661.96..16,995.43 rows=133,389 width=48) (actual time=130.417..154.485 rows=188,670 loops=1)

  • Sort Key: pl.plan_uuid
  • Sort Method: external merge Disk: 9,008kB
31. 41.293 41.293 ↓ 1.4 191,641 1

CTE Scan on get_plan_revisions pl (cost=0.00..2,667.78 rows=133,389 width=48) (actual time=0.033..41.293 rows=191,641 loops=1)

Planning time : 21.598 ms
Execution time : 62,243.748 ms