explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fdcm : Optimization for: Optimization for: Optimization for: ; plan #szA; plan #w1Qn; plan #K8z

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.295 137.234 ↑ 1.2 58 1

Sort (cost=22,394.81..22,394.98 rows=69 width=227) (actual time=137.220..137.234 rows=58 loops=1)

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

Nested Loop Left Join (cost=10,124.93..22,392.70 rows=69 width=227) (actual time=101.945..136.939 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. 26.486 135.729 ↑ 1.2 58 1

Hash Left Join (cost=10,124.52..22,334.57 rows=69 width=226) (actual time=101.897..135.729 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.404 28.763 ↑ 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.058..28.763 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=4894
5. 0.980 20.359 ↓ 0.0 0 1

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

6. 2.691 2.691 ↓ 1.0 23,615 1

Bitmap Index Scan on pc_sg_admissions_work_pxdate (cost=0.00..364.09 rows=23,367 width=0) (actual time=2.691..2.691 rows=23,615 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. 16.688 16.688 ↑ 1.0 140,944 1

Bitmap Index Scan on pc_sg_admissions_work_pxobj (cost=0.00..2,640.43 rows=141,041 width=0) (actual time=16.688..16.688 rows=140,944 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. 31.865 80.480 ↓ 1.0 66,651 1

Hash (cost=6,285.84..6,285.84 rows=66,160 width=78) (actual time=80.480..80.480 rows=66,651 loops=1)

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

Seq Scan on pr_index_sg_data_contactpartie "Student" (cost=0.00..6,285.84 rows=66,160 width=78) (actual time=0.013..48.615 rows=66,651 loops=1)

  • Filter: (((partyrole)::text = ANY ('{Student,Agent}'::text[])) AND ((pxobjclass)::text = 'Index-SG-Data-ContactParties'::text))
  • Rows Removed by Filter: 53634
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