explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E40jG

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 3,879.456 ↑ 1.0 10 1

Limit (cost=173,089.55..610,697.11 rows=10 width=1,632) (actual time=1,401.976..3,879.456 rows=10 loops=1)

  • Buffers: shared hit=24,574, temp read=480 written=4,937
2. 0.265 3,879.450 ↑ 9.4 10 1

Nested Loop Left Join (cost=173,089.55..4,286,600.66 rows=94 width=1,632) (actual time=1,401.974..3,879.450 rows=10 loops=1)

  • Buffers: shared hit=24,574, temp read=480 written=4,937
3. 0.100 1,109.545 ↑ 9.4 10 1

Nested Loop (cost=140,121.36..1,187,587.98 rows=94 width=40) (actual time=1,109.162..1,109.545 rows=10 loops=1)

  • Buffers: shared hit=21,848, temp read=480 written=4,937
4. 0.027 1,109.155 ↑ 42,708.6 10 1

Unique (cost=140,120.79..147,313.91 rows=427,086 width=32) (actual time=1,109.088..1,109.155 rows=10 loops=1)

  • Buffers: shared hit=21,797, temp read=480 written=4,937
5. 909.394 1,109.128 ↑ 95,908.3 10 1

Sort (cost=140,120.79..142,518.50 rows=959,083 width=32) (actual time=1,109.084..1,109.128 rows=10 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. 199.734 199.734 ↑ 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.011..199.734 rows=959,083 loops=1)

  • Buffers: shared hit=21,797
7. 0.290 0.290 ↑ 1.0 1 10

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.029..0.029 rows=1 loops=10)

  • 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))
  • Buffers: shared hit=51
8. 2,756.320 2,769.640 ↑ 1.0 1 10

Aggregate (cost=32,968.19..32,968.20 rows=1 width=1,600) (actual time=276.964..276.964 rows=1 loops=10)

  • Buffers: shared hit=2,726
9. 13.320 13.320 ↑ 4.3 178 10

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.062..1.332 rows=178 loops=10)

  • 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=2,715
Planning time : 21.232 ms
Execution time : 3,891.298 ms