explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yyTS

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 3,796.485 ↑ 1.0 10 1

Limit (cost=172,690.74..610,302.74 rows=10 width=1,632) (actual time=1,296.113..3,796.485 rows=10 loops=1)

  • Buffers: shared hit=24,161, temp read=476 written=4,927
2. 0.243 3,796.471 ↑ 9.3 10 1

Nested Loop Left Join (cost=172,690.74..4,242,482.34 rows=93 width=1,632) (actual time=1,296.108..3,796.471 rows=10 loops=1)

  • Buffers: shared hit=24,161, temp read=476 written=4,927
3. 0.064 994.588 ↑ 9.3 10 1

Nested Loop (cost=139,818.84..1,185,393.32 rows=93 width=40) (actual time=994.291..994.588 rows=10 loops=1)

  • Buffers: shared hit=21,803, temp read=476 written=4,927
4. 0.019 994.284 ↑ 42,621.2 10 1

Unique (cost=139,818.28..146,996.68 rows=426,212 width=32) (actual time=994.234..994.284 rows=10 loops=1)

  • Buffers: shared hit=21,752, temp read=476 written=4,927
5. 783.154 994.265 ↑ 95,712.0 10 1

Sort (cost=139,818.28..142,211.08 rows=957,120 width=32) (actual time=994.229..994.265 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,352kB
  • Buffers: shared hit=21,752, temp read=476 written=4,927
6. 211.111 211.111 ↑ 1.0 957,120 1

Seq Scan on mdl_engage_actions_table_p1_yaddr_v4 (cost=0.00..31,323.20 rows=957,120 width=32) (actual time=0.008..211.111 rows=957,120 loops=1)

  • Buffers: shared hit=21,752
7. 0.240 0.240 ↑ 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.024..0.024 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,789.710 2,801.640 ↑ 1.0 1 10

Aggregate (cost=32,871.90..32,871.91 rows=1 width=1,600) (actual time=280.164..280.164 rows=1 loops=10)

  • Buffers: shared hit=2,358
9. 11.930 11.930 ↑ 4.3 178 10

Index Scan using "mdl_engage_actions_table_p1_yaddr_v4$idx2" on mdl_engage_actions_table_p1_yaddr_v4 s (cost=0.67..852.70 rows=761 width=21) (actual time=0.071..1.193 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)))
  • Filter: (p.person_uuid <> person_uuid)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,355
Planning time : 19.502 ms
Execution time : 3,806.869 ms