explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6q3y

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 3,857.597 ↓ 1.1 100 1

Limit (cost=141,386.77..1,306,539.81 rows=94 width=40) (actual time=1,114.462..3,857.597 rows=100 loops=1)

  • Buffers: shared hit=154,392, temp read=480 written=4,937
2. 0.106 3,857.576 ↓ 1.1 100 1

Nested Loop Left Join (cost=141,386.77..1,306,539.81 rows=94 width=40) (actual time=1,114.460..3,857.576 rows=100 loops=1)

  • Buffers: shared hit=154,392, temp read=480 written=4,937
3. 0.205 1,110.270 ↓ 1.1 100 1

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

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

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

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

Sort (cost=140,120.79..142,518.50 rows=959,083 width=32) (actual time=1,108.248..1,108.413 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. 296.077 296.077 ↑ 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.021..296.077 rows=959,083 loops=1)

  • Buffers: shared hit=21,797
7. 1.590 1.590 ↑ 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.015..0.015 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,382.400 2,747.200 ↑ 1.0 1 100

Aggregate (cost=1,265.41..1,265.42 rows=1 width=8) (actual time=27.471..27.472 rows=1 loops=100)

  • Buffers: shared hit=132,064
9. 364.800 364.800 ↓ 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.648 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 : 3.718 ms
Execution time : 3,866.618 ms