explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AZiq

Settings
# exclusive inclusive rows x rows loops node
1. 0.146 64,981.283 ↑ 1.0 100 1

Limit (cost=632.00..4,011.96 rows=100 width=1,632) (actual time=34.761..64,981.283 rows=100 loops=1)

  • Buffers: shared hit=130,106 read=2,149 dirtied=4
2. 2.332 64,981.137 ↑ 1,865.3 100 1

Nested Loop Left Join (cost=632.00..6,305,367.37 rows=186,533 width=1,632) (actual time=34.760..64,981.137 rows=100 loops=1)

  • Buffers: shared hit=130,106 read=2,149 dirtied=4
3. 1.709 525.705 ↑ 1,865.3 100 1

Merge Semi Join (cost=621.00..4,246,509.39 rows=186,533 width=50) (actual time=6.829..525.705 rows=100 loops=1)

  • Merge Cond: ((p.organization_uuid = mdl_engage_actions_table_p1_yaddr_v4.organization_uuid) AND (p.person_uuid = mdl_engage_actions_table_p1_yaddr_v4.person_uuid))
  • Buffers: shared hit=20,455 read=2,149 dirtied=4
4. 523.689 523.689 ↑ 1,661.8 10,143 1

Index Scan using "t_person_dim$idx1" on t_person_dim_backend p (cost=0.56..4,136,582.62 rows=16,856,022 width=50) (actual time=0.019..523.689 rows=10,143 loops=1)

  • Filter: ((longitude IS NOT NULL) AND (latitude IS NOT NULL))
  • Rows Removed by Filter: 41,933
  • Buffers: shared hit=20,451 read=2,149 dirtied=4
5. 0.307 0.307 ↑ 6,817.7 142 1

Index Only Scan using "mdl_engage_actions_table_p1_yaddr_v4$idx3" on mdl_engage_actions_table_p1_yaddr_v4 (cost=0.42..20,991.30 rows=968,118 width=32) (actual time=0.011..0.307 rows=142 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=4
6. 62,181.400 64,453.100 ↑ 1.0 1 100

Aggregate (cost=11.01..11.02 rows=1 width=1,600) (actual time=644.531..644.531 rows=1 loops=100)

  • Buffers: shared hit=109,651
7. 2,271.700 2,271.700 ↓ 689.0 689 100

Index Scan using "mdl_engage_actions_table_p1_yaddr_v4$idx2" on mdl_engage_actions_table_p1_yaddr_v4 s_1 (cost=0.44..2.75 rows=1 width=45) (actual time=0.285..22.717 rows=689 loops=100)

  • Index Cond: ((geog && _st_expand(((((('SRID=4,326;POINT('::text || (p.longitude)::text) || ' '::text) || (p.latitude)::text) || ')'::text))::geography, '4000'::double precision)) AND (activity_date > (now() - '2 years'::interval)) AND (p.person_uuid <> person_uuid) AND (city = p.city))
  • Filter: ((((((('SRID=4,326;POINT('::text || (p.longitude)::text) || ' '::text) || (p.latitude)::text) || ')'::text))::geography && _st_expand(geog, '4000'::double precision)) AND _st_dwithin(geog, ((((('SRID=4,326;POINT('::text || (p.longitude)::text) || ' '::text) || (p.latitude)::text) || ')'::text))::geography, '4000'::double precision, true))
  • Rows Removed by Filter: 252
  • Buffers: shared hit=109,255
Planning time : 29.742 ms
Execution time : 64,983.661 ms