explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QXKy

Settings
# exclusive inclusive rows x rows loops node
1. 124.646 45,755.888 ↓ 44,762.7 134,288 1

Finalize GroupAggregate (cost=29,881.27..765,477.09 rows=3 width=111) (actual time=885.740..45,755.888 rows=134,288 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.pxcreatedatetime) < '2019-11-25 05:00:15.462'::timestamp without time zone)) AND ((max(ac_shortcut.pxcreatedatetime) IS NULL) OR (max(ac_shortcut.pxcreatedatetime) < '2019-11-25 05:00:15.462'::timestamp without time zone)) AND (sum(CASE ra_entry.pzentrytype WHEN 'SharedAssembly'::text THEN 1 ELSE 0 END) = 0) AND (sum(CASE ac_shortcut.pxcreateoperator WHEN 'StaticAssembler@pega.com'::text THEN 1 ELSE 0 END) = 0))
  • Rows Removed by Filter: 5,621
2. 15,898.690 45,631.242 ↑ 1.8 139,909 1

Gather Merge (cost=29,881.27..754,310.24 rows=258,454 width=151) (actual time=885.727..45,631.242 rows=139,909 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 3,487.936 29,732.552 ↑ 2.8 46,636 3 / 3

Partial GroupAggregate (cost=28,881.25..723,478.21 rows=129,227 width=151) (actual time=852.041..29,732.552 rows=46,636 loops=3)

  • Group Key: asm.pzpackage, asm.pzclass, asm.pzcacheconfigid
4. 10,326.787 26,244.616 ↓ 110.8 14,319,298 3 / 3

Nested Loop Left Join (cost=28,881.25..718,955.27 rows=129,227 width=658) (actual time=852.001..26,244.616 rows=14,319,298 loops=3)

5. 49.219 935.395 ↑ 1.2 51,842 3 / 3

Merge Left Join (cost=28,880.69..29,828.20 rows=63,208 width=643) (actual time=851.930..935.395 rows=51,842 loops=3)

  • Merge Cond: (((asm.pzpackage)::text = (usage.pzpackage)::text) AND ((asm.pzclass)::text = (usage.pzclass)::text))
6. 17.672 729.772 ↑ 1.4 46,636 3 / 3

Merge Left Join (cost=21,338.78..21,812.86 rows=63,208 width=635) (actual time=700.077..729.772 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. 657.364 712.082 ↑ 1.4 46,636 3 / 3

Sort (cost=21,336.55..21,494.57 rows=63,208 width=111) (actual time=700.053..712.082 rows=46,636 loops=3)

  • Sort Key: asm.pzpackage, asm.pzclass
  • Sort Method: quicksort Memory: 10,298kB
  • Worker 0: Sort Method: quicksort Memory: 9,227kB
  • Worker 1: Sort Method: quicksort Memory: 14,134kB
8. 54.718 54.718 ↑ 1.4 46,636 3 / 3

Parallel Seq Scan on pr_assembledclasses asm (cost=0.00..16,296.40 rows=63,208 width=111) (actual time=0.012..54.718 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 ((pzcacheconfigid)::text = '-560251499'::text) AND ((pzjar)::text = 'prgenclasses.jar'::text))
  • Rows Removed by Filter: 65,989
9. 0.008 0.018 ↓ 0.0 0 3 / 3

Sort (cost=2.23..2.23 rows=1 width=1,702) (actual time=0.018..0.018 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.010 0.010 ↓ 0.0 0 3 / 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.010..0.010 rows=0 loops=3)

  • Index Cond: ((pzcacheconfigid)::text = '-560251499'::text)
11. 143.832 156.404 ↓ 1.0 20,184 3 / 3

Sort (cost=7,541.91..7,592.37 rows=20,182 width=113) (actual time=151.845..156.404 rows=20,184 loops=3)

  • Sort Key: usage.pzpackage, usage.pzclass
  • Sort Method: quicksort Memory: 4,955kB
  • Worker 0: Sort Method: quicksort Memory: 4,955kB
  • Worker 1: Sort Method: quicksort Memory: 4,955kB
12. 12.572 12.572 ↓ 1.0 20,184 3 / 3

Seq Scan on pr4_log_rule_usage usage (cost=0.00..6,098.82 rows=20,182 width=113) (actual time=0.022..12.572 rows=20,184 loops=3)

13. 14,982.434 14,982.434 ↓ 3.3 275 155,527 / 3

Index Scan using pr_sys_appcache_shortcut_idx5 on pr_sys_appcache_shortcut ac_shortcut (cost=0.56..10.07 rows=83 width=130) (actual time=0.034..0.289 rows=275 loops=155,527)

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