explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8NJJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 96,885.013 ↑ 6,721,884.0 1 1

Unique (cost=4,568,094.33..4,667,588.16 rows=6,721,884 width=65) (actual time=96,885.008..96,885.013 rows=1 loops=1)

2. 0.060 96,885.007 ↑ 736,991.3 18 1

Sort (cost=4,568,094.33..4,601,258.94 rows=13,265,844 width=65) (actual time=96,885.006..96,885.007 rows=18 loops=1)

  • Sort Key: "PC0".pxinsindexedclass, "CL".pylabel
  • Sort Method: quicksort Memory: 26kB
3. 0.195 96,884.947 ↑ 736,991.3 18 1

Hash Join (cost=2,751.63..2,687,744.52 rows=13,265,844 width=65) (actual time=2,883.362..96,884.947 rows=18 loops=1)

  • Hash Cond: (("PC0".pxinsindexedclass)::text = ("CL".pyclassname)::text)
4. 3,399.910 96,871.984 ↑ 748,084.7 18 1

Hash Left Join (cost=63.96..2,434,575.08 rows=13,465,524 width=35) (actual time=2,870.437..96,871.984 rows=18 loops=1)

  • Hash Cond: (("PC0".pxinsindexedclass)::text = ("MRP".srcol1)::text)
  • Join Filter: (("PC0".pxobjclass)::text = 'Index-WorkPartyUri'::text)
  • Filter: (((("PC0".pxpartyrole)::text = ANY ('{Initiator,NotifiedAfterRejection,NotifiedAfterApproval}'::text[])) AND (("PC0".pypartyidentifier)::text = 'Z000CJZM'::text) AND (("PC0".pxinsindexedclass)::text ~~ '%GWSS%'::text) AND (("PC0".pxinsindexedclass)::text !~~ '%Stage%'::text)) OR ("MRP".srcol2 IS NOT NULL))
  • Rows Removed by Filter: 13885650
5. 93,462.487 93,462.487 ↓ 1.0 13,885,668 1

Seq Scan on pc_index_workparty "PC0" (cost=0.00..2,383,074.88 rows=13,533,190 width=80) (actual time=2.530..93,462.487 rows=13,885,668 loops=1)

  • Filter: ((pxobjclass)::text = 'Index-WorkPartyUri'::text)
6. 0.000 9.587 ↓ 0.0 0 1

Hash (cost=63.95..63.95 rows=1 width=55) (actual time=9.587..9.587 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
7. 0.001 9.587 ↓ 0.0 0 1

Subquery Scan on MRP (cost=63.92..63.95 rows=1 width=55) (actual time=9.586..9.587 rows=0 loops=1)

8. 0.001 9.586 ↓ 0.0 0 1

Unique (cost=63.92..63.94 rows=1 width=87) (actual time=9.586..9.586 rows=0 loops=1)

9. 0.013 9.585 ↓ 0.0 0 1

Sort (cost=63.92..63.93 rows=1 width=87) (actual time=9.585..9.585 rows=0 loops=1)

  • Sort Key: "MRP_1".casetype, "MRP_1".processtype, (concat("MRP_1".casetype, '#', "MRP_1".processtype))
  • Sort Method: quicksort Memory: 25kB
10. 0.001 9.572 ↓ 0.0 0 1

Nested Loop (cost=5.21..63.91 rows=1 width=87) (actual time=9.572..9.572 rows=0 loops=1)

11. 0.002 9.571 ↓ 0.0 0 1

Bitmap Heap Scan on pc_index_workparty "PartyURI" (cost=4.93..23.33 rows=18 width=33) (actual time=9.571..9.571 rows=0 loops=1)

  • Recheck Cond: ((((pypartyidentifier)::text = 'Z000CJZM'::text) AND ((pxpartyrole)::text = 'Administrators'::text) AND ((pxindexpurpose)::text = 'PartyURI'::text)) OR (((pypartyidentifier)::text = 'Z000CJZM'::text) AND ((pxpartyrole)::text = ANY ('{Administrators,Search}'::text[])) AND ((pxindexpurpose)::text = 'PartyURI'::text)))
  • Filter: ((pxobjclass)::text = 'Index-WorkPartyUri'::text)
12. 0.001 9.569 ↓ 0.0 0 1

BitmapOr (cost=4.93..4.93 rows=18 width=0) (actual time=9.569..9.569 rows=0 loops=1)

13. 9.538 9.538 ↓ 0.0 0 1

Bitmap Index Scan on pc_index_workparty_idx1 (cost=0.00..1.65 rows=7 width=0) (actual time=9.538..9.538 rows=0 loops=1)

  • Index Cond: (((pypartyidentifier)::text = 'Z000CJZM'::text) AND ((pxpartyrole)::text = 'Administrators'::text) AND ((pxindexpurpose)::text = 'PartyURI'::text))
14. 0.030 0.030 ↓ 0.0 0 1

Bitmap Index Scan on pc_index_workparty_idx1 (cost=0.00..3.27 rows=11 width=0) (actual time=0.030..0.030 rows=0 loops=1)

  • Index Cond: (((pypartyidentifier)::text = 'Z000CJZM'::text) AND ((pxpartyrole)::text = ANY ('{Administrators,Search}'::text[])) AND ((pxindexpurpose)::text = 'PartyURI'::text))
15. 0.000 0.000 ↓ 0.0 0

Index Scan using pc_sie_coe_gwss_work_pk on pc_sie_coe_gwss_work "MRP_1" (cost=0.29..2.25 rows=1 width=78) (never executed)

  • Index Cond: ((pzinskey)::text = ("PartyURI".pxinsindexedkey)::text)
  • Filter: (((pxobjclass)::text = 'SIE-GWSS-Work-Config'::text) AND ((configname)::text = 'Default'::text))
16. 4.111 12.768 ↑ 1.1 15,211 1

Hash (cost=2,480.70..2,480.70 rows=16,558 width=64) (actual time=12.768..12.768 rows=15,211 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1697kB
17. 8.657 8.657 ↑ 1.1 15,211 1

Seq Scan on pr4_base "CL" (cost=0.00..2,480.70 rows=16,558 width=64) (actual time=0.006..8.657 rows=15,211 loops=1)

  • Filter: ((pxobjclass)::text = 'Rule-Obj-Class'::text)
  • Rows Removed by Filter: 3459