explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 62t2

Settings
# exclusive inclusive rows x rows loops node
1. 17,449.707 47,608.727 ↓ 44,762.7 134,288 1

HashAggregate (cost=1,206,814.55..1,210,227.77 rows=3 width=111) (actual time=47,547.394..47,608.727 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.pxcre
  • Rows Removed by Filter: 5621
2. 9,974.711 30,159.020 ↓ 133.3 42,956,401 1

Hash Left Join (cost=31,922.24..1,195,538.32 rows=322,178 width=658) (actual time=285.216..30,159.020 rows=42,956,401 loops=1)

  • Hash Cond: (((asm.pzcacheconfigid)::text = (ra_entry.pzcacheconfigid)::text) AND ((asm.pzpackage)::text = (ra_entry.pzpackage)::text) AND ((asm.pzclass)::text = (ra_entry.pzclass)::text))
3. 11,849.253 20,184.303 ↓ 133.3 42,956,401 1

Hash Right Join (cost=31,920.00..1,191,911.57 rows=322,178 width=134) (actual time=285.198..20,184.303 rows=42,956,401 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))
4. 8,050.084 8,050.084 ↑ 1.0 4,566,387 1

Seq Scan on pr_sys_appcache_shortcut ac_shortcut (cost=0.00..1,103,403.68 rows=4,743,654 width=130) (actual time=0.020..8,050.084 rows=4,566,387 loops=1)

  • Filter: ((pzcacheconfigid)::text = '-560251499'::text)
5. 46.290 284.966 ↓ 1.0 155,527 1

Hash (cost=29,265.27..29,265.27 rows=151,699 width=119) (actual time=284.966..284.966 rows=155,527 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 24432kB
6. 51.030 238.676 ↓ 1.0 155,527 1

Hash Right Join (cost=23,000.64..29,265.27 rows=151,699 width=119) (actual time=183.225..238.676 rows=155,527 loops=1)

  • Hash Cond: (((usage.pzpackage)::text = (asm.pzpackage)::text) AND ((usage.pzclass)::text = (asm.pzclass)::text))
7. 4.647 4.647 ↓ 1.0 20,184 1

Seq Scan on pr4_log_rule_usage usage (cost=0.00..6,098.82 rows=20,182 width=113) (actual time=0.007..4.647 rows=20,184 loops=1)

8. 47.091 182.999 ↑ 1.1 139,909 1

Hash (cost=20,725.15..20,725.15 rows=151,699 width=111) (actual time=182.998..182.999 rows=139,909 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 21937kB
9. 135.908 135.908 ↑ 1.1 139,909 1

Seq Scan on pr_assembledclasses asm (cost=0.00..20,725.15 rows=151,699 width=111) (actual time=0.014..135.908 rows=139,909 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 ((pzcacheconfi
  • Rows Removed by Filter: 197963
10. 0.000 0.006 ↓ 0.0 0 1

Hash (cost=2.22..2.22 rows=1 width=1,702) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Index Cond: ((pzcacheconfigid)::text = '-560251499'::text)