#	exclusive	inclusive	rows x	rows	loops	node
1.	8,440.610	170,350.214	↓ 44,759.3	134,278	1	→ GroupAggregate (cost=1,217,029.07..1,234,070.93 rows=3 width=111) (actual time=140,442.025..170,350.214 rows=134,278 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,629
2.	5,869.336	161,909.604	↓ 135.1	42,962,516	1	→ Merge Left Join (cost=1,217,029.07..1,219,413.53 rows=317,925 width=658) (actual time=140,442.012..161,909.604 rows=42,962,516 loops=1) 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)
3.	138,645.319	156,040.252	↓ 135.1	42,962,516	1	→ Sort (cost=1,217,026.84..1,217,821.66 rows=317,925 width=134) (actual time=140,441.990..156,040.252 rows=42,962,516 loops=1) Sort Key: asm.pzpackage, asm.pzclass Sort Method: external merge Disk: 6,438,680kB
4.	12,437.459	17,394.933	↓ 135.1	42,962,516	1	→ Hash Right Join (cost=32,228.06..1,187,971.16 rows=317,925 width=134) (actual time=309.920..17,394.933 rows=42,962,516 loops=1) Hash Cond: (((ac_shortcut.pzcacheconfigid)::text = (asm.pzcacheconfigid)::text) AND ((ac_shortcut.pzpackage)::text = (asm.pzpackage)::text) AND ((ac_shortcut.pzclass)::text = (asm.pzclass)::text))
5.	4,647.788	4,647.788	↓ 1.0	4,567,041	1	→ Seq Scan on pr_sys_appcache_shortcut ac_shortcut (cost=0.00..1,101,190.02 rows=4,566,562 width=130) (actual time=0.028..4,647.788 rows=4,567,041 loops=1) Filter: ((pzcacheconfigid)::text = '-560251499'::text)
6.	47.051	309.686	↑ 1.0	155,525	1	→ Hash (cost=29,482.49..29,482.49 rows=156,890 width=119) (actual time=309.686..309.686 rows=155,525 loops=1) Buckets: 262,144 Batches: 1 Memory Usage: 24,432kB
7.	47.726	262.635	↑ 1.0	155,525	1	→ Hash Right Join (cost=23,217.60..29,482.49 rows=156,890 width=119) (actual time=191.199..262.635 rows=155,525 loops=1) Hash Cond: (((usage.pzpackage)::text = (asm.pzpackage)::text) AND ((usage.pzclass)::text = (asm.pzclass)::text))
8.	23.935	23.935	↑ 1.0	20,184	1	→ Seq Scan on pr4_log_rule_usage usage (cost=0.00..6,098.84 rows=20,184 width=113) (actual time=0.008..23.935 rows=20,184 loops=1)
9.	46.917	190.974	↑ 1.1	139,907	1	→ Hash (cost=20,864.26..20,864.26 rows=156,890 width=111) (actual time=190.974..190.974 rows=139,907 loops=1) Buckets: 262,144 Batches: 1 Memory Usage: 21,936kB
10.	144.057	144.057	↑ 1.1	139,907	1	→ Seq Scan on pr_assembledclasses asm (cost=0.00..20,864.26 rows=156,890 width=111) (actual time=0.020..144.057 rows=139,907 loops=1) 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: 197,988
11.	0.007	0.016	↓ 0.0	0	1	→ Sort (cost=2.23..2.23 rows=1 width=1,702) (actual time=0.016..0.016 rows=0 loops=1) Sort Key: ra_entry.pzpackage, ra_entry.pzclass Sort Method: quicksort Memory: 25kB
12.	0.009	0.009	↓ 0.0	0	1	→ 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.009..0.009 rows=0 loops=1) Index Cond: ((pzcacheconfigid)::text = '-560251499'::text)


 GroupAggregate  (cost=1217029.07..1234070.93 rows=3 width=111) (actual time=140442.025..170350.214 rows=134278 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
atedatetime) < '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_en
try.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: 5629
   ->  Merge Left Join  (cost=1217029.07..1219413.53 rows=317925 width=658) (actual time=140442.012..161909.604 rows=42962516 loops=1)
         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)
         ->  Sort  (cost=1217026.84..1217821.66 rows=317925 width=134) (actual time=140441.990..156040.252 rows=42962516 loops=1)
               Sort Key: asm.pzpackage, asm.pzclass
               Sort Method: external merge  Disk: 6438680kB
               ->  Hash Right Join  (cost=32228.06..1187971.16 rows=317925 width=134) (actual time=309.920..17394.933 rows=42962516 loops=1)
                     Hash Cond: (((ac_shortcut.pzcacheconfigid)::text = (asm.pzcacheconfigid)::text) AND ((ac_shortcut.pzpackage)::text = (asm.pzpackage)::text) AND ((ac_shortcut.pzclass)::text = (asm.pzclass)::text))
                     ->  Seq Scan on pr_sys_appcache_shortcut ac_shortcut  (cost=0.00..1101190.02 rows=4566562 width=130) (actual time=0.028..4647.788 rows=4567041 loops=1)
                           Filter: ((pzcacheconfigid)::text = '-560251499'::text)
                     ->  Hash  (cost=29482.49..29482.49 rows=156890 width=119) (actual time=309.686..309.686 rows=155525 loops=1)
                           Buckets: 262144  Batches: 1  Memory Usage: 24432kB
                           ->  Hash Right Join  (cost=23217.60..29482.49 rows=156890 width=119) (actual time=191.199..262.635 rows=155525 loops=1)
                                 Hash Cond: (((usage.pzpackage)::text = (asm.pzpackage)::text) AND ((usage.pzclass)::text = (asm.pzclass)::text))
                                 ->  Seq Scan on pr4_log_rule_usage usage  (cost=0.00..6098.84 rows=20184 width=113) (actual time=0.008..23.935 rows=20184 loops=1)
                                 ->  Hash  (cost=20864.26..20864.26 rows=156890 width=111) (actual time=190.974..190.974 rows=139907 loops=1)
                                       Buckets: 262144  Batches: 1  Memory Usage: 21936kB
                                       ->  Seq Scan on pr_assembledclasses asm  (cost=0.00..20864.26 rows=156890 width=111) (actual time=0.020..144.057 rows=139907 loops=1)
                                             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
econfigid)::text = '-560251499'::text) AND ((pzjar)::text = 'prgenclasses.jar'::text))
                                             Rows Removed by Filter: 197988
         ->  Sort  (cost=2.23..2.23 rows=1 width=1702) (actual time=0.016..0.016 rows=0 loops=1)
               Sort Key: ra_entry.pzpackage, ra_entry.pzclass
               Sort Method: quicksort  Memory: 25kB
               ->  Index Scan using cache_entry_rule on pr_sys_cache_entry ra_entry  (cost=0.14..2.22 rows=1 width=1702) (actual time=0.009..0.009 rows=0 loops=1)
                     Index Cond: ((pzcacheconfigid)::text = '-560251499'::text)

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	8,440.610 ms	5.0 %
Hash	2	93.968 ms	0.1 %
Hash Right Join	2	12,485.185 ms	7.3 %
Index Scan	1	0.009 ms	0.0 %
Merge Left Join	1	5,869.336 ms	3.4 %
Seq Scan	3	4,815.780 ms	2.8 %
Sort	2	138,645.326 ms	81.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
pr4_log_rule_usage	1	23.935 ms	0.0 %
Seq Scan	1	23.935 ms	100.0 %
pr_assembledclasses	1	144.057 ms	0.1 %
Seq Scan	1	144.057 ms	100.0 %
pr_sys_appcache_shortcut	1	4,647.788 ms	2.7 %
Seq Scan	1	4,647.788 ms	100.0 %
pr_sys_cache_entry	1	0.009 ms	0.0 %
Index Scan	1	0.009 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wFb1

Color mode:

Visible columns:

Per node type stats

Per table stats