explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 88tP : Optimization for: plan #w5ff

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 100.948 43,381.028 ↓ 44,760.0 134,280 1

Finalize GroupAggregate (cost=30,203.00..751,609.28 rows=3 width=111) (actual time=1,073.359..43,381.028 rows=134,280 loops=1)

  • Group Key: asm.pzpackage, asm.pzclass, asm.pzcacheconfigid
  • Filter: ((((max(usage.pxlastuse) IS NOT NULL) AND (max(usage.pxlastuse) < '2019-10-27 06:00:15.462'::timestamp without time zone)) OR (max(usage.pxlastuse) IS NULL)) AND ((max(ra_entry.pxcreatedatetime) IS NULL) OR (max(ra_entry.pxcre
  • Rows Removed by Filter: 5627
2. 0.000 43,280.080 ↑ 1.9 139,907 1

Gather Merge (cost=30,203.00..740,131.11 rows=264,938 width=151) (actual time=1,073.342..43,280.080 rows=139,907 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 10,747.917 88,396.335 ↑ 2.8 46,636 3

Partial GroupAggregate (cost=29,202.97..708,550.67 rows=132,469 width=151) (actual time=1,024.295..29,465.445 rows=46,636 loops=3)

  • Group Key: asm.pzpackage, asm.pzclass, asm.pzcacheconfigid
4. 32,057.399 77,648.418 ↓ 108.1 14,319,356 3

Nested Loop Left Join (cost=29,202.97..703,914.25 rows=132,469 width=658) (actual time=1,024.252..25,882.806 rows=14,319,356 loops=3)

5. 128.754 3,288.219 ↑ 1.3 51,842 3

Merge Left Join (cost=29,202.42..30,177.07 rows=65,371 width=643) (actual time=1,024.192..1,096.073 rows=51,842 loops=3)

  • Merge Cond: (((asm.pzpackage)::text = (usage.pzpackage)::text) AND ((asm.pzclass)::text = (usage.pzclass)::text))
6. 41.073 2,593.917 ↑ 1.4 46,636 3

Merge Left Join (cost=21,660.33..22,150.63 rows=65,371 width=635) (actual time=840.362..864.639 rows=46,636 loops=3)

  • Merge Cond: (((asm.pzpackage)::text = (ra_entry.pzpackage)::text) AND ((asm.pzclass)::text = (ra_entry.pzclass)::text))
  • Join Filter: ((ra_entry.pzcacheconfigid)::text = (asm.pzcacheconfigid)::text)
7. 2,308.266 2,552.799 ↑ 1.4 46,636 3

Sort (cost=21,658.10..21,821.53 rows=65,371 width=111) (actual time=840.340..850.933 rows=46,636 loops=3)

  • Sort Key: asm.pzpackage, asm.pzclass
  • Sort Method: quicksort Memory: 11513kB
  • Worker 0: Sort Method: quicksort Memory: 10451kB
  • Worker 1: Sort Method: quicksort Memory: 11694kB
8. 244.533 244.533 ↑ 1.4 46,636 3

Parallel Seq Scan on pr_assembledclasses asm (cost=0.00..16,429.61 rows=65,371 width=111) (actual time=0.022..81.511 rows=46,636 loops=3)

  • Filter: ((pzlastmodified < '2019-11-25 05:00:15.462'::timestamp without time zone) AND ((pzpackage)::text <> 'com/pegarules/generated'::text) AND ((pzclass)::text !~~ '%$%'::text) AND ((pzcach
  • Rows Removed by Filter: 65992
9. 0.024 0.045 ↓ 0.0 0 3

Sort (cost=2.23..2.23 rows=1 width=1,702) (actual time=0.015..0.015 rows=0 loops=3)

  • Sort Key: ra_entry.pzpackage, ra_entry.pzclass
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
10. 0.021 0.021 ↓ 0.0 0 3

Index Scan using cache_entry_rule on pr_sys_cache_entry ra_entry (cost=0.14..2.22 rows=1 width=1,702) (actual time=0.007..0.007 rows=0 loops=3)

  • Index Cond: ((pzcacheconfigid)::text = '-560251499'::text)
11. 500.388 565.548 ↑ 1.0 20,184 3

Sort (cost=7,542.09..7,592.55 rows=20,184 width=113) (actual time=183.821..188.516 rows=20,184 loops=3)

  • Sort Key: usage.pzpackage, usage.pzclass
  • Sort Method: quicksort Memory: 4955kB
  • Worker 0: Sort Method: quicksort Memory: 4955kB
  • Worker 1: Sort Method: quicksort Memory: 4955kB
12. 65.160 65.160 ↑ 1.0 20,184 3

Seq Scan on pr4_log_rule_usage usage (cost=0.00..6,098.84 rows=20,184 width=113) (actual time=0.026..21.720 rows=20,184 loops=3)

13. 42,302.800 42,302.800 ↓ 3.8 275 155,525

Index Scan using pr_sys_appcache_shortcut_idx5 on pr_sys_appcache_shortcut ac_shortcut (cost=0.56..9.58 rows=73 width=130) (actual time=0.029..0.272 rows=275 loops=155,525)

  • Index Cond: (((pzcacheconfigid)::text = (asm.pzcacheconfigid)::text) AND ((pzcacheconfigid)::text = '-560251499'::text) AND ((pzpackage)::text = (asm.pzpackage)::text) AND ((pzclass)::text = (asm.pzclass)::text