explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PGNy

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 4,153.742 ↓ 1.1 100 1

Limit (cost=141,386.77..1,306,540.05 rows=94 width=64) (actual time=1,164.679..4,153.742 rows=100 loops=1)

  • Buffers: shared hit=154,411, temp read=480 written=4,937
2. 0.124 4,153.719 ↓ 1.1 100 1

Nested Loop Left Join (cost=141,386.77..1,306,540.05 rows=94 width=64) (actual time=1,164.677..4,153.719 rows=100 loops=1)

  • Buffers: shared hit=154,411, temp read=480 written=4,937
3. 0.235 1,160.295 ↓ 1.1 100 1

Nested Loop (cost=140,121.36..1,187,587.98 rows=94 width=40) (actual time=1,157.604..1,160.295 rows=100 loops=1)

  • Buffers: shared hit=22,328, temp read=480 written=4,937
4. 0.062 1,157.728 ↑ 4,029.1 106 1

Unique (cost=140,120.79..147,313.91 rows=427,086 width=32) (actual time=1,157.531..1,157.728 rows=106 loops=1)

  • Buffers: shared hit=21,797, temp read=480 written=4,937
5. 852.533 1,157.666 ↑ 6,614.4 145 1

Sort (cost=140,120.79..142,518.50 rows=959,083 width=32) (actual time=1,157.528..1,157.666 rows=145 loops=1)

  • Sort Key: mdl_engage_actions_table_p1_yaddr_v4.organization_uuid, mdl_engage_actions_table_p1_yaddr_v4.person_uuid
  • Sort Method: external merge Disk: 39,432kB
  • Buffers: shared hit=21,797, temp read=480 written=4,937
6. 305.133 305.133 ↑ 1.0 959,083 1

Seq Scan on mdl_engage_actions_table_p1_yaddr_v4 (cost=0.00..31,387.83 rows=959,083 width=32) (actual time=0.018..305.133 rows=959,083 loops=1)

  • Buffers: shared hit=21,797
7. 2.332 2.332 ↑ 1.0 1 106

Index Scan using "t_person_dim$idx1" on t_person_dim_backend p (cost=0.56..2.43 rows=1 width=40) (actual time=0.022..0.022 rows=1 loops=106)

  • Index Cond: ((organization_uuid = mdl_engage_actions_table_p1_yaddr_v4.organization_uuid) AND (person_uuid = mdl_engage_actions_table_p1_yaddr_v4.person_uuid))
  • Filter: ((longitude IS NOT NULL) AND (latitude IS NOT NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=531
8. 2,615.600 2,993.300 ↑ 1.0 1 100

Aggregate (cost=1,265.42..1,265.43 rows=1 width=32) (actual time=29.933..29.933 rows=1 loops=100)

  • Buffers: shared hit=132,083
9. 377.700 377.700 ↓ 1.6 1,232 100

Index Scan using "mdl_engage_actions_table_p1_yaddr_v4$idx3" on mdl_engage_actions_table_p1_yaddr_v4 s (cost=0.67..864.84 rows=763 width=21) (actual time=0.053..3.777 rows=1,232 loops=100)

  • Index Cond: ((cube_enlarge((ll_to_earth((p.latitude)::double precision, (p.longitude)::double precision))::cube, '3999.99993444938'::double precision, 3) @> (ll_to_earth((latitude)::double precision, (longitude)::double precision))::cube) AND (activity_date > (now() - '2 years'::interval)) AND (p.person_uuid <> person_uuid))
  • Buffers: shared hit=132,064
Planning time : 4.378 ms
Execution time : 4,162.222 ms