explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xdmG

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 3,851.385 ↑ 1.0 10 1

Limit (cost=172,700.64..610,410.58 rows=10 width=1,632) (actual time=1,368.222..3,851.385 rows=10 loops=1)

  • Buffers: shared hit=24,490, temp read=476 written=4,927
2. 0.217 3,851.373 ↑ 9.3 10 1

Nested Loop Left Join (cost=172,700.64..4,243,403.04 rows=93 width=1,632) (actual time=1,368.220..3,851.373 rows=10 loops=1)

  • Buffers: shared hit=24,490, temp read=476 written=4,927
3. 0.071 1,066.986 ↑ 9.3 10 1

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

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

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

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

Sort (cost=139,818.28..142,211.08 rows=957,120 width=32) (actual time=1,066.579..1,066.613 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. 210.802 210.802 ↑ 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.012..210.802 rows=957,120 loops=1)

  • Buffers: shared hit=21,752
7. 0.280 0.280 ↑ 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.028..0.028 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,769.460 2,784.170 ↑ 1.0 1 10

Aggregate (cost=32,881.80..32,881.81 rows=1 width=1,600) (actual time=278.417..278.417 rows=1 loops=10)

  • Buffers: shared hit=2,687
9. 14.710 14.710 ↑ 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..862.60 rows=761 width=21) (actual time=0.095..1.471 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,687
Planning time : 29.295 ms
Execution time : 3,862.794 ms