explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AX4e : Optimization for: plan #MzN1

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.291 134.378 ↑ 1.2 58 1

Sort (cost=21,756.09..21,756.27 rows=69 width=227) (actual time=134.362..134.378 rows=58 loops=1)

  • Sort Key: "PC0".toplevelcasehandle, "PC0".pyid
  • Sort Method: quicksort Memory: 41kB
2. 0.070 134.087 ↑ 1.2 58 1

Nested Loop Left Join (cost=9,485.98..21,753.99 rows=69 width=227) (actual time=100.458..134.087 rows=58 loops=1)

  • Join Filter: (("PC0".pxobjclass)::text = ANY ('{SG-Admissions-Work,SG-Admissions-Work-AcademicAssessment,SG-Admissions-Work-Admission,SG-Admissions-Work-Assessment,SG-Admissions-Work-Assessment-AU,SG-Admissions-Work-Assessment-AU-CSU,SG-Admissions- (...)
3. 25.282 132.915 ↑ 1.2 58 1

Hash Left Join (cost=9,485.56..21,695.85 rows=69 width=226) (actual time=100.417..132.915 rows=58 loops=1)

  • Hash Cond: (("PC0".toplevelcasehandle)::text = ("Student".pxinsindexedkey)::text)
  • Join Filter: (("PC0".pxobjclass)::text = ANY ('{SG-Admissions-Work,SG-Admissions-Work-AcademicAssessment,SG-Admissions-Work-Admission,SG-Admissions-Work-Assessment,SG-Admissions-Work-Assessment-AU,SG-Admissions-Work-Assessment-AU-CSU,SG-Admis (...)
  • Filter: ((upper(("Student".firstname)::text) ~~ 'SR%'::text) OR (upper(("PC0".firstname)::text) ~~ 'SR%'::text))
  • Rows Removed by Filter: 20340
4. 8.078 29.260 ↑ 1.2 11,678 1

Bitmap Heap Scan on pc_sg_admissions_work "PC0" (cost=3,011.68..14,746.81 rows=13,813 width=185) (actual time=21.875..29.260 rows=11,678 loops=1)

  • Recheck Cond: ((pxcreatedatetime >= '2017-04-07 14:00:00'::timestamp without time zone) AND (pxcreatedatetime <= '2019-04-09 13:59:59.999'::timestamp without time zone) AND ((pxobjclass)::text = ANY ('{SG-Admissions-Work,SG-Admissions-W (...)
  • Heap Blocks: exact=4895
5. 0.854 21.182 ↓ 0.0 0 1

BitmapAnd (cost=3,011.68..3,011.68 rows=13,813 width=0) (actual time=21.182..21.182 rows=0 loops=1)

6. 2.844 2.844 ↓ 1.0 23,617 1

Bitmap Index Scan on pc_sg_admissions_work_pxdate (cost=0.00..364.09 rows=23,367 width=0) (actual time=2.844..2.844 rows=23,617 loops=1)

  • Index Cond: ((pxcreatedatetime >= '2017-04-07 14:00:00'::timestamp without time zone) AND (pxcreatedatetime <= '2019-04-09 13:59:59.999'::timestamp without time zone))
7. 17.484 17.484 ↑ 1.0 140,948 1

Bitmap Index Scan on pc_sg_admissions_work_pxobj (cost=0.00..2,640.43 rows=141,041 width=0) (actual time=17.484..17.484 rows=140,948 loops=1)

  • Index Cond: ((pxobjclass)::text = ANY ('{SG-Admissions-Work,SG-Admissions-Work-AcademicAssessment,SG-Admissions-Work-Admission,SG-Admissions-Work-Assessment,SG-Admissions-Work-Assessment-AU,SG-Admissions-Work-Assessment-AU-C (...)
8. 29.863 78.373 ↓ 1.0 66,651 1

Hash (cost=5,646.22..5,646.22 rows=66,213 width=78) (actual time=78.373..78.373 rows=66,651 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7509kB
9. 48.510 48.510 ↓ 1.0 66,651 1

Index Scan using idx_partyrole on pr_index_sg_data_contactpartie "Student" (cost=0.42..5,646.22 rows=66,213 width=78) (actual time=0.022..48.510 rows=66,651 loops=1)

  • Index Cond: ((partyrole)::text = ANY ('{Student,Agent}'::text[]))
  • Filter: ((pxobjclass)::text = 'Index-SG-Data-ContactParties'::text)
10. 1.102 1.102 ↑ 1.0 1 58

Index Scan using insindexedkey_d06a8 on pr_index_sg_data_contactpartie "Agent" (cost=0.42..0.82 rows=1 width=59) (actual time=0.019..0.019 rows=1 loops=58)

  • Index Cond: ((pxinsindexedkey)::text = ("PC0".toplevelcasehandle)::text)
  • Filter: (((partyrole)::text = 'Agent'::text) AND ((pxobjclass)::text = 'Index-SG-Data-ContactParties'::text))
  • Rows Removed by Filter: 1